David DeWinter

As many of you (should) know, you can select result sets from stored procedures to return them to your application, but there is no built-in metadata that tells you the specifications of the columns in each result set. However, I was always intrigued by the way the LINQ to SQL designer managed to determine, without flaw, the names, data types, etc. of the columns returned by stored procedures.

So to satisfy my curiosity I set up a trace using the SQL Server profiler against the database on my box and played with the LINQ to SQL designer to get it to find that metadata. Much to my surprise, it calls the stored procedure directly, passing NULL for each of its parameters.

Wait a minute; my stored procedure does validation, so it will raise an error when xyz parameter is NULL. I then noticed earlier in the trace a strange statement that involved the SET FMTONLY statement. Searching for that statement revealed this gem.

"Returns only metadata to the client. Can be used to test the format of the response without actually running the query."

Running something like this would then yield the metadata of the result set(s), regardless of whether errors are raised.

The nice thing is that you can do this for any query, not just stored procedure executions.

Back in February I blogged about DBML Fixup—a tool whose goal was to sync LINQ to SQL models with their respective database schemas from within the Visual Studio environment, as well as to handle running various fixup tasks on models. Now it’s six months later, and I have a much clearer vision of what the tool is meant to be as well as a firmer command on VSX. The following screencast is a preview of the features of DBML Fixup.

Here are some previous posts which (briefly) describe the domain:

http://blogs.rev-net.com/ddewinter/2008/02/16/the-linq-to-sql-model/

http://blogs.rev-net.com/ddewinter/2008/02/16/linq-to-sql-and-database-schema-sync/

Any ideas about directions for the tool? Or maybe something that wasn’t covered in the screencast? I’d love to hear your ideas.

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:

1. Object Role Modeling: An Overview, http://msdn2.microsoft.com/en-us/library/aa290383(VS.71).aspx
2. 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…

(more…)

Since the new year I’ve been working on a project that attempts to solve the following problem:

How do I keep my generated entity classes in sync with the current database schema using LINQ to SQL?

For some people, this question is moot. It’s easy enough to utilize the current “resync” mechanism in LINQ to SQL by deleting all the entity classes and functions (the LINQ to SQL term for sprocs and UDFs) on the designer surface and then redragging and dropping the tables and functions of interest, thus updating the entity classes by regenerating from scratch. However, for others, this solution is not good enough for a few reasons:

(more…)