In my last post I began the story of how I added concurrency checking capabilities to D3—we conquered a bug in the model first workflow activities in VS 2010 beta 1. Next we need to look into ways to customize the SSDL generated so that the concurrency column will end up with the RowVersion type that SQL Server will automatically update when any part of the row changes.
This lead to the next surprise. At first I thought that I would add a new custom activity to the workflow which would fix-up the SSDL, but this turned out to have its own complexities. As it turns out, the model first process runs the workflow within the visual studio process which means the DLLs containing activities for the workflow are loaded in the same way other VS DLLs are loaded, namely from the VS install directories or from the GAC. This also means if the DLL changes, you have to shutdown VS and restart it before model first will reflect the change. Conclusion: If you want to create a general purpose activity that many folks will reuse, then this is a reasonable approach You can carefully debug an activity and set something up to install it centrally. But, if you need a one-off customization, this is just too hard.
So we’re looking into options to help here, but in the meantime I’ve discovered another approach which seems to work well. The default workflow described in DbGen.xaml has two activities. The first one takes the conceptual model and produces an MSL and SSDL string, while the second one takes the SSDL and produces something which will create the database (normally a SQL file, but after my first round of customizations for D3 it will produce a c-sharp file with a method that will create it).
It turns out, though, that the second activity under the covers is a general-purpose mechanism that can be configured to take in a string, run it through a T4 template and produce an output string. The nice thing about using a template is that it allows you to write arbitrary code as a one-off in your solution and easily iterate on your design, modifying and rerunning without any need to restart VS, etc.
Eureka! The second activity can be used twice. In between the two default activities, it can be inserted again and configured to take the SSDL in, transform it with a different template and send it on to the final activity. In addition model first makes some useful bits of information like a path to the EDMX file available to the template, so we can access the CSDL in order to make better decisions about how to modify the SSDL.
The relevant part of the resulting XAML looks like this:
var query = from property in csdl.Elements(edm + "EntityType").SelectMany(t => t.Elements(edm + "Property"))
where (string)property.Attribute("Type").Value == "Binary" &&
(string)property.Attribute("MaxLength").Value == "8" &&
(string)property.Attribute("ConcurrencyMode").Value == "Fixed" &&
(string)property.Attribute(store + "StoreGeneratedPattern").Value == "Computed"
by entitySetName(property.Parent, csdl, edm);
This query depends on knowledge of the algorithm used by the CsdlToSsdlAndMslActivity to map conceptual model properties to the database—namely that the column names are the same as the property names on the entities and (at least for the base types of entity sets) the name of the table is the same as the name of the entity set that contains the type. So we query the csdl for all the elements that represent properties which have the required facets and extract the name of the property, then we group those property names by the name of the entity set containing the entity type to which the property belongs using these two functions:
string entitySetName(XElement entityType, XElement csdl, XNamespace edm)
return (from set in csdl.Element(edm + "EntityContainer")
.Elements(edm + "EntitySet")
where entityTypeName(set) == entityType.Attribute("Name").Value
string entityTypeName(XElement entitySet)
XElement schema = entitySet.Parent.Parent;
string nsPrefix = schema.Attribute("Namespace").Value + ".";
string aliasPrefix = schema.Attribute("Alias").Value + ".";
string typeName = ((string)entitySet.Attribute("EntityType").Value).Trim();
else if (typeName.StartsWith(aliasPrefix))
return "UNEXPECTED TYPENAME -- Where is the namespace?";
The result of this query is an IGrouping<string, string> where the first string is the name of the entity set and the second string is a collection of property names from the base type within that set. With that information it’s a simple matter to load the SSDL into memory, search for the relevant entity types (in the case of the SSDL, each table is represented by an entity type and each column within the table is a property of that type) and properties within them. Then we swap out the attributes on the XElement for that property and set the name, the type to rowversion and the store generated pattern to computed. Finally, the template serializes the XML for the SSDL to its output.