SyndicationThe LINQ to SQL Metamodel
For my DBML Fixup project, I’ve really had to drill into the structure of LINQ to SQL—including how the data is stored in the .dbml file, how various changes in the designer affect the serialized XML (which is the format of the .dbml file), and what the various elements and attributes in the DBML file actually mean. (Honestly, what does the IsForeignKey attribute on the Association element imply as its meaning? I thought all “associations” represented foreign keys!)
At first I tried to dissect LINQ to SQL by keeping track of the various elements and attributes and their representations in the XML and the designer in big Excel tables. They quickly became unwieldy, and I decided to cut my losses and model the LINQ to SQL schema (DBMLSchema.xsd). Needless to say this was a very challenging exercise, but I gleaned a significant amount of knowledge about LINQ to SQL after finishing it.
The purpose of this post is to post the various pages of the LINQ to SQL model; I want this post to clarify the infrastructure of LINQ to SQL to others, so I would appreciate if you can comment on any inconsistencies or missing constraints you can see. I used Object-Role Modeling (ORM) for the model, so if you are not familiar with that, you may want to check out the following resources:
- Object Role Modeling: An Overview, http://msdn2.microsoft.com/en-us/library/aa290383(VS.71).aspx
- Halpin, T. 2001. Information Modeling and Relational Databases: From Conceptual Analysis to Logical Design.San Francisco: Morgan Kaufmann Publishers
The model starts like this…here’s the database page. The derivation rules aren’t on the diagram explicitly, as they are stored as part of the properties of the fact type. Other advantages of having the .orm file are that I’ve put definitions on all of the fact types to clarify their meaning, in the case that the predicate text is still not clear enough. If anyone wants the .orm file, feel free to contact me at david.dewinter AT (@) rev-net DOT (.) com. (You can open .orm files with Visual Studio after installing Project NORMA.) I will also put up a contact form soon…
Figure 1. The Database element.
Figure 2. The Connection element (renamed ConnectionDetails for clarity).
Figure 3. The Table and Type elements.
Figure 4. The Column element.
Figure 5. The Assocation element (renamed AssocationEnd for clarity).
Function 6. The Function element.
Figure 7. The TableFunction element.
February 18th, 2008 at 10:35 pm
Looks good to me.
February 20th, 2008 at 11:25 am
It is always good to see someone else cognizant of ORM diagrams!
I’ve been using ORM for modelling for a few years now, and have a few comments about the models I see:
Figure 3 looks a bit messy. It could be improved by introducing a second subtype of Type, perhaps named “Non-Return Type” or “Regular Type”. Then, attach the facts that do not apply to a return type to it, obviating the need for so many disjunctive exclusion constraints.
Figure two might benefit from a similar subtyping. By introducing an entity to represent the type of location the connection string comes from, (connection string source type) the model becomes more flexible, should there ever be a source introduced that isn’t XML /OR/ application settings.
There were also several entities for which I couldn’t find a primary identifier. I’m thinking you left those off to keep the diagrams from becoming more complex. :]
One of my favorite recent additions to the tool is the ‘alignment’ option - Darren Flynn showed it to me last month. When you select a group of objects on the diagram, select ‘Format’ from the menu bar, ‘Align’, and then how you wish to align them: Lefts, middles, centers, etc.
February 20th, 2008 at 7:57 pm
Orion, good to hear from you again. I believe we worked on NORMA together in Spring 2006. Regarding your comments:
1) Yeah, Figure 3 is a bit messy.
I wanted to keep the images scrunched enough so they could fit on this page but still be clear. Also, for that page, the semantics of the exclusion constraints are exactly what I need. Unfortunately, a ReturnType may either reference some TypeId or play any of those other roles. I don’t think I can reduce the number of external constraints with subtyping, unless you have any other ideas?
2) Another frustration with this domain is that I can’t subtype here either. If you get a connection string from the XML, then you can still specify a SettingsObjectName and SettingsPropertyName, and if you get a connection string from the application settings, then you can still specify a connection string.
3) I chose to leave primary identifiers off the main object types in this model because they didn’t match the XML representation. I didn’t want people who viewed the model to be confused between a Type’s TypeId (an explicit attribute in the XML which is unfortunately optional) and a surrogate identifier like Type_id. There are also instances of this with FunctionId.
4) Yep, I enjoy the Alignment feature as well. I admit that I should go back and make the models a bit cleaner.
Thanks again for your comments!
February 22nd, 2008 at 9:30 am
Now that I look more closely, I can see the information that would be lost transforming figure 3 into the subtype patten I’d mentioned.
The diagram implies that a ‘Return Type’ may indeed play a role those other entities (Access Modifier, ClassModifier, ETC) given that it does not reference a typeID. (Having that optional does complicate things!)
What would you think of objectifying the ‘Type is Return Type for Function’ fact, and naming it “Return Type”? Not that that would make the diagram simpler… Then there’s introducing a third subtype, “return type without typeID reference”… thinking on the fly here. :]
Could you send me that .orm file? I think I’d enjoy mulling it over. It would be a welcome diversion from this medicaid model, and may even give me some fresh ideas for it.
My email is my first name (dot) my last name (at) gmail
July 7th, 2008 at 8:07 pm
The IsForeignKey attribute on associations just indicates what direction a foreign key goes, i.e. is this table referenced, or is it referencing another table. The reason for this is that a foreign key will result in associations on both sides so this attribute is needed to know in what direction it goes…
July 8th, 2008 at 5:57 am
Kris, I agree with your interpretation of its meaning. However, it could’ve been named more appropriately, no?
July 8th, 2008 at 6:36 pm
Yes, the name is a bit confusing.
Overall I think MSFT could have done a better job at:
1) documenting DBML
2) exposing an object model behind the DBML designer for add-ins to hook into instead of having to update the dbml file directly
July 8th, 2008 at 7:44 pm
I actually found this to be an excellent resource on the DBML file structure. It helped significantly when building this model.
I agree that interactions with the DBML designer could have been better from an API level, but frankly developers who are “hacking” the designers are probably last on the LINQ to SQL team’s priority list. I’m not sure if there is any current designer that offers a cleaner API.
August 24th, 2008 at 8:50 pm
[...] http://blogs.rev-net.com/ddewinter/2008/02/16/the-linq-to-sql-model/ [...]