CODEWITHSUNDEEP
c#entity-frameworkedmx
raven-ai
raven-ai

Reputation: 253

How to preserve StoreGeneratedPattern setting when refreshing DB First EDMX?

I have a Database First EDMX and one of the Entities has a Property that must be set as StoreGeneratedPattern = Identity in order to use sequential GUIDs as the primary key.

This works correctly, however any time the model is refreshed with "Update model from database" the value is reset to StoreGeneratedPattern = None, and must be manually edited.

It's a hassle and a significant source of potential human error, especially if multiple tables must be changed and multiple developers maybe making changes to the database and updating the EDMX as needed.

I gathered there's no simple way to preserve the StoreGeneratedPattern value, but is there a not simple way? Maybe even an automated script that runs whenever the EDMX is updated and edits the EDMX? Any solution that doesn't involve having to know all the things that need to be edited?

Thank you

Upvotes: 4

Views: 1696

Answers (2)

Michael Sandler
Michael Sandler

Reputation: 1309

All my Primary Keys are Guids with constraint DEFAULT (newsequentialid()). Ignoring @Manish's very good advice, I use the following two find-and-replace regexes in NotePad++ to update the .edmx file after updating from the DB:

Find #1: (<Key>\s*<PropertyRef Name="([^"]+)" />\s*</Key>\s*<Property Name="\2" Type="uniqueidentifier" )(Nullable="false" />)

Replace #1: \1StoreGeneratedPattern="Identity" \3

Find #2: (<Key>\s*<PropertyRef Name="([^"]+)" />\s*</Key>\s*<Property Name="\2" Type="Guid" Nullable="false" )(/>)

Replace #2: \1annotation:StoreGeneratedPattern="Identity" \3

This only works where the PK is the first field in the table.

YMMV. Use at own risk.

Upvotes: 0

Manish Kumar
Manish Kumar

Reputation: 372

That is because its not implicitly possible to set a GUID column as an auto incrementing identity in SQL Server. I know you can put a hack in place eg setting the default value for this "so called" identity column to newsequentialid() to automatically fill this column with a GUID but technically this column is not an identity column for this table per se. Obviously when EF generates the EDMX during "Update model from DB", it treats this column as a regular column and not as an auto incrementing identity hence, overwrites your manually edited StoreGeneratedPattern = Identity back to StoreGeneratedPattern = None.

This is a hack and I would strongly suggest against making any changes to autogenerated code by EF. I've seen many developers getting burnt when they do so for an immediate remedy but when it comes back and bites them later they wish they never manipulated the auto generated code.

Just a word of caution!!!

Upvotes: 2

Related Questions