Entity Framework 4 EDMX Modifier - Correct Default Values in SQL Server

• Download source - 3.59 KB

Introduction

If you've used Entity Framework 4 with SQL Server, then you may well have hit the old 'Default Value or Binding' problem where EF4 insists that every property be provided, even if you intend it to be supplied by the SQL server.

In addition to this, it's not uncommon for us to want the values of these fields when we save the entity. Example situations may be:

• 'CreatedDate' fields, automatically recording when a row was created
• 'UniqueGUID identifers' where we want to know the GUID that SQL allocated to the record we just added. This is particularly important if we are dealing with NewSequentialId() functions in SQL where we have a GUID primary key on a clustered index; we can't create these in an application, as only SQL server can calculate the next value during row creation.
• Other 'Identifier' fields where the server assigns the next sequential number. Creating these on the application side is expensive and risky due to concurrency issues.

The attached project provides a workaround; it modifies an EDMX file to copy Microsoft's 'StoreGeneratedPattern' attribute from the conceptual model (the one you edit on-screen) to the server model (the one used to create SQL DML statements).

Using the Code

This code is a simple command-line application that accepts an edmx file on the command line and transforms it into a corrected one, either writing the resulting file to a new file or overwriting the one supplied.

To flag the properties in the edmx designer that are to be treated as server-generated, set their "StoreGeneratedPattern" to "Computed".
We would assume this would flow through nicely, but the initial release has avoided this process, probably due to it being an 'incomplete solution' as I explain below.

The command to modify the edmx file can be placed in the project's Pre-Build command line, but it's important to remember that the EF4 templates are rebuilt before the Pre-Build commands run, so it's necessary to either click the 'Transform All Templates' button above the project explorer, or build your solution twice.

The syntax is:

EDMXModifier infile.edmx [outfile.edmx] [/identity] Modifies an EDMX file, extending table properties marked as 'Computed' to be ignored when EF4 writes entities to the database.
This allows the database to provide values via the 'Default Value or Binding' property.
If the /identity flag is specified then Identity columns are included in the transformation.

Design Issues...

It is important to note that these modifications do not fully resolve the issues in EF4. The intent of the 'Default Value or Binding' property in SQL Server is to provide a default that will be used if no value is supplied. This means that if we supply NULL to an Entity in EF4, it should write NULL into that database field.

Unfortunately Microsoft don't appear to have factored this important state into EF4, and by providing attributes of only 'None', 'Computed' and 'Identity' against fields we are unable to say "I want this field to be optional, and if I don't supply any value it should use the default from the database definition".

So for now, we have to treat every field that we want to use database defaults to be used as though it were a Computed column; read-only, and if we want to update these values we must either create a second entity in the EF4 designer, or modify the value through stored procedures.

Points of Interest

The code used System.Xml.Linq objects to perform the manipulation rather than an XSLT partly due to the convoluted namespace involved, but mainly because it's a darn sight easier to debug!

History

• v1.0 - Hopefully the only version - Microsoft may release a bug fix sometime in the not-too-distant future