Reading Time: 15 minutes (it is quite hard going, but you need to know this if you need to work with the DacFx)

DacFx - Getting the data type of a column on a table, a rehash of an old blog post

This post has been years in the making, I did a version of it years ago here: https://sqlserverfunctions.wordpress.com/2014/09/27/querying-the-dacfx-api-getting-column-type-information - well fast forward 5 years to now and I can now understand how it works and whereas before I basically sat in a debugger trying to figure out what method to use. I am now pretty confident I can get to any property I need using excel rather than using a debugger and reflection, this makes me happy :)

In the original post I said to get the data type from a column on a table, do this:

var dataType = table.First()
.GetReferencedRelationshipInstances(Table.Columns)
.First(p => p.ObjectName.ToString() == “[dbo].[AWBuildVersion].[SystemInformationID]”)
.Object.GetReferenced(Column.DataType)
.First()
.Name;

Note, I don’t recommend only using one line of linq to do everything but it was just a suggestion :)

The things to point out here are that table is a TSqlObject which has various methods for getting to properties:

• GetChildren
• GetProperty
• GetReferenced
• GetReferencedRelationshipInstances
• GetReferencing
• GetReferencingRelationshipInstances

These all come in various flavours, all a little sour. In the above snippet it uses GetReferencedRelationshipInstances to get all the relationships that have a ModelRelationshipClass of Table.Columns, that is Table.Columns is an object of type ModelRelationshipClass - this is important so bear with me. If we look at Table.Columns we see this:

This shows that Table.Columns is an object, a class of type ModelRelationshipClass and it shows the type is belongs to, the FromObjectClass which is set to Table, then we have the name of the property, in this case Columns, then we see that th erelationship class has no properties and it is supported on All platforms (Sql versions, not the cool new x-plat support) and finally the relationship type: Composing.

So what does this all mean? Well, to understand how all this works we need to think about what it is we are dealing with here. The TSqlModel is a representation of how the database should look, how tables are defined, which columns they have, what stored procedures are doing. Then we need to understand how the model is used - The model is used to compare to other models and also traverse the hierarchy, two different use cases:

Traversing the hierarchy

This is where we have things like in SSDT where you can use the refactoring support to rename a table, all the references to that table are found and updated - the model is used to find those relationships so the model needs to support finding out an object’s dependent things but also what the object is depended on by.

Comparing models

This is the second use case, if we have two models - what changes are there that need to happen? This isn’t just a “does prox x look the same as proc y”, that would be easy - when you throw in options like ignore whitespace, or for tables, ignore column order then it becomes clearer that the model needs to be able to compared to another model but the deep context information like are two tables the same but with their columns in a different order. The requirements for something that can handle this quickly outgrow a string comparison.

Relationships

The model is made up of TSqlObject's which are generic, an object could be a table or a view or a data type and it will be a TSqlObject - now these objects are very, very specific - a table for example doesn’t have any columns or a column doesn’t have a data type, this is an example of a Column:

The public properties on this example are the Name and the ObjectType that is it, super focused. However, what we do have is a set of relationships and properties - now these have always been a bit of a mystery to me but I have uploaded a csv to github with the current list of properties and relationships that every TSqlObject in the DacFx has today, grab the entire csv from:

If we look at Column then we see a column has a whole load of properties such as Collation which returns a sting or DataType which isn’t a property but a relationship.

To get a property such as Length we can call GetProperty, because we know from the chart that Length returns an Int32 we can use the typed version of GetProperty and ask for the property that has the name Length so:

var length = column.GetProperty<int>(Column.Length);

Length is an int of the actual length. Now it isn’t all roses, if we wanted to know something more exotic such as the expression on a calculated column we could do:

var expression = column.GetProperty<SqlScriptProperty>(Column.Expression);

However we will get an issue compiling because SqlScriptProperty is an internal sealed class in the DacFx. In this case we have to fall back to using the generic version of GetProperty like column.GetProperty(Column.Expression); - through experimentation I can see that this returns a string of the definition, there seem to be a mix of types that are internal and public, I would like it if this was a bit clearer (I have raised a github issue here, I know the ssdt team look at these issues so maybe something might happen: https://github.com/Microsoft/DACExtensions/issues/27

So, if we take a computed column we can get to the definition which I suppose covers the “comparing model” cases, but what about traversing references? For that we can use ExpressionDependencies so if we have a computed column called a which performs a calculation on another column we can use the relationship ExpressionDependencies to find the columns that a refers to.

Insted of using GetProperty, we need to get the details of a relationship so can either just get the TSqlObject of the referenced columns or we can get the TSqlObject and the details of the relationship:

Just the TSqlObject of the referenced columns:

column.GetReferenced(Column.ExpressionDependencies)

Full details of the relationships to the referenced columns:

column.GetReferencedRelationshipInstances(Column.ExpressionDependencies, DacQueryScopes.UserDefined)

I would think that in most case GetReferenced would be enough - I would say that GetReferencedRelationshipInstances is more for when we are enumerating objects and we don’t know the type - in that you want to see what an object references rather than saying “i need the length of this type”.

What are Column.Expression and Column.ExpressionDepenencies

Well these are important and we need to understand about the difference between properties and relationships and how we use GetProperty as opposed to either GetReferenced or GetReferencedRelationshipInstances.

GetProperty takes a ModelPropertyClass and the GetReferenc* methods take a ModelRelationshipClass - you can’t ask GetProperty to get a relationship or visa-versa. I think this is what confused be the most about the DacFx, not understanding how these two similar classes were so different and led to so many hours spent with a debugger trying to figure out whether to use GetProperty or GetReferenc*.

Column.Expression therefore is a ModelPropertyClass and Column.ExpressionDependencies is a ModelRelationshipClass.

A worked example

So lets take a TSqlObject of type Table and see how we can get to the data types using the csv I uploaded to github: https://github.com/GoEddie/DacFx-Info/blob/master/DacFxRelationships/ModelSchema.csv

We first query a model for a table:

var model = new TSqlModel("Database1.dacpac");
var table = model.GetObject(ModelSchema.Table, new ObjectIdentifier("dbo", "table_with_computed"), DacQueryScopes.UserDefined);

The table object is a TSqlObject:

Now we have a TSqlObject we can use our csv to see what properties and relationships exist for a Table object:

In this list we see we have a relationship called Columns so we call:

foreach (var column in table.GetReferenced(Table.Columns, DacQueryScopes.UserDefined))
{

}

Now we need to have a bit of a guess here and hope it pans out - where the relationship name is plural DataCompressionOptions,Columns, etc, the type returned in an enumerable of the non-plural version so an enumerable of DataCompressionOption or Column - all the ones I have tried fit this but it isn’t covered by the compiler so who knows /shrug :).

IF we take this to be true, column is a TSqlObject of type Column so we then look up the properties on Column:

From this list, to get the data type we can see a relationship called DataType (2nd from bottom) so we prod that:

foreach (var column in table.GetReferenced (Table.Columns, DacQueryScopes.UserDefined))
{
   var type = column.GetReferenced(Column.DataType, DacQueryScopes.All);
}

This then gives use a type which is TSqlObject of type DataType:

DataType itself has a property SqlDataType (which incidentally isn’t internal to the DacFx):

foreach (var column in table.GetReferenced (Table.Columns, DacQueryScopes.UserDefined))
{
   var type = column.GetReferenced(Column.DataType, DacQueryScopes.All);
   var sqlDataType = type.FirstOrDefault().GetProperty<SqlDataType>(DataType.SqlDataType);
   Console.WriteLine(sqlDataType);
}

The SqlDataType is even documented at microsoft (not much in the DacFx is!!):

https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.dac.model.sqldatatype?view=sql-dacfx-140.3881.1

Fin

So there you have it, a rehash of a really old blog post but hopefully with less hand waving than last time - I know this post was a bit niche but if you need to work with the DacFx, the more of this sort of thing, the better if you ask me :)