For the last little while I’ve been banging my head against the desk, trying to figure out an issue when creating records that used a SQL UniqueIdentifier data type. It turns out that the issues I experienced resulted from a bug, or “undocumented feature”, in the way Visual Studio builds entities for a Entity Framework model.

The scenario I have is one where we do some publisher/subscriber database synchronization for our solution. We have a number of clients, including mobile applications, that subscriber to a central SQL database. This resulting configuration on the SQL server database includes uniqueidentifier data type fields appended to the tables used in synchronization processes.

These fields, appropriately named “rowguid”, are configured as UniqueIdentifier data types. When a new row is added to the table, the Default Value or Binding property is configured with a NEWSEQUENTIALID() call.

For example, here is a table named Company configured with a uniqueidentifier type field named rowguid, and the Default Value or Binding property is configured to use a default value of NEWSEQUENTIALID() – which is basically a new GUID value.

Using the Entity Framework designer in Visual Studio, you would expect that when adding the table as an entity to the designer that the uniqueidentifier field (my “rowguid” named field) would be added and configured properly. In some ways it “kinda sorta” does configure it properly. It treats the field as any other field. The problem comes in when you start to use the entity framework to add records.

 

 

Let’s say a call goes out to add a record using the Company entity in the model. Using the entity framework, the call would be something like this…

   MyEntities.AddToCompanies(myNewCompany)

When I did this call, the record was added to the database, however the rowguid field was created with a value of “00000000-0000-0000-000000000000″. Why? Probably because the entity framework defaulted the uniqueidentifier value as such; all zeros. I suppose this would be fine if I knew there would be only one record in the table. But that would defeat the purpose of using a field that is supposed to represent a unique value.

Seems the problem lies in the way the designer handles this property.

The “rowguid” property does not get configured with the necessary StoreGeneratedPattern property. I’ll update this using the designer…

 

 

 

But looking at the actual .edmx model, the StoreGeneratedPattern property that is set does not stick…

 

 

 <EntityType Name="Company">
          <Key>
            <PropertyRef Name="CompanyID" />
          </Key>
          <Property Name="CompanyID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
          <Property Name="CompanyName" Type="nvarchar" MaxLength="50" />
          <Property Name="CreatedBy" Type="int" />
          <Property Name="CreatedDate" Type="datetime" />
          <Property Name="LastUpdatedBy" Type="int" />
          <Property Name="LastUpdatedDate" Type="datetime" />
          <Property Name="CompanyKey" Type="nvarchar" MaxLength="50" />
          <Property Name="rowguid" Type="uniqueidentifier" Nullable="false" />
        </EntityType>

So what’s a person to do? Seems that this issue can be resolved by manually updating the .edmx file that Visual Studio uses when designing the entity model. Unfortunately my model contains a LOT of entities, and I don’t want to spend a bunch of time manually editing this file. Others have found workarounds for this same problem however to me they seemed to be overkill for such a simple problem. I know that Microsoft will come out with a resolution eventually, so until then, I leverage the all mighty power of the Quick Replace tool in Visual Studio.

I opened my entity framework model .edmx file using the Visual Studio XML editor (right-click the .edmx file and select Open With…, then select XMS (Text) Editor from the Open With dialog). Then, using the handy dandy Find and Replace tool, I do a Quick Replace on all the “rowguid” fields, adding the StoreGeneratedPattern property wherever it is needed.

That’s all there is to it. No addin, extension, or fancy abstracted code to fix the issue. Again, Microsoft will come up with a fix eventually. Until then, this simple little process works just fine. Adding a record works as expected, using a unique guid value each time.

 

 

Cheers!