Code through the pain Ladislav Mrnka's professional blog about software development

17Mar/1115

One of very important features in ORM tools is an ability to get data auto-generated by a database during the entity persistence back to your application. The Entity framework supports this feature by setting StoreGeneratedPattern in the configuration of persisted property. The StoreGeneratedPattern setting is available in both SSDL (Store schema definition language) and CSDL (Conceptual schema definition language) parts of the EDMX file. CSDL configuration allows you defining the reloading behavior in the Model-first approach but SSDL part is responsible for generating correct SQL commands which will persist the entity and reload auto-generated properties. Unfortunately for a long time this was the source of all problems.

The feature was very hard to use because of the annoying bug in the Entity designer. When we set the property in the designer, the value was saved only in CSDL part but not in SSDL part of the EDMX file and the feature didn't work until we opened the EDMX file as XML and manually modified SSDL part. This solved the problem but only until we updated our model from the database. The update always deleted whole SSDL part including our manual change so we had to do it again. Any incremental development of our models become a big pain. The workaround was using mapped stored procedures for inserting and updating entities and mapping result sets  (returning auto-generated data) from these stored procedures back to the entity. Finally this bug is solved in Visual Studio 2010 SP1 and we can use StoreGeneratedPattern without any problems because the value is correctly set in both CSDL and SSDL parts and it is not overwritten during updating from the database.

Update: If you still have problem with this issue install KB2561001. It should be the most recent fix for this bug in VS 2010.

Rest of the blog post describes usage of StoreGeneratedPattern.

The StoreGeneratedPattern property has a value from an enumeration with the same name. The enumeration offers three values:

  • None - the value of the property is set in the application and persisted to the database. This is default setting for most of properties.
  • Identity - the value of the property is set in the database during the first persistence of the entity (during insertion to a database table). This setting is by default used for all primary keys using IDENTITY setting (MS SQL server). When using properties set to StoreGeneratedPattern.Identity, Entity Framework needs to know the real value after each entity insertion. For IDENTITY columns used in MS SQL, Entity framework always bundles INSERT SQL command with a query to get the generated value back to the application by calling SCOPE_IDENTITY().
  • Computed - the value of the property is set in the database during each persistence of the entity (during both insertion and updating). This setting is by default used for properties mapped to columns with TIMESTAMP or  ROWVERSION data types (MS SQL server). When using properties set to StoreGeneratedPattern.Computed, Entity Framework always bundles INSERT and UPDATE SQL commands with a query to get those computed columns back to the application.
    Computed properties must not be part of entity key.

The example of configure StoreGeneratedPattern in Entity designer:

Setting StoreGeneratedPattern in Visual Studio 2010

The example of manually configured StoreGeneratedPattern in SSDL:

<EntityType Name="MyEntity">
  <Key>
    <PropertyRef Name="Id"/>
  </Key>
  <Property Name="Id" Type="int" Nullable="false" StoreGeneratedPattern="Identity"/>
  <Property Name="Name" Type="nvarchar" Nullable="false" MaxLength="50"/>
  <Property Name="CreatedAt" Type="datetime" Nullable="false" StoreGeneratedPattern="Identity"/>
  <Property Name="Timestamp" Type="timestamp" Nullable="false" StoreGeneratedPattern="Computed"/>
</EntityType>

The example of manually configured StoreGeneratedPattern in CSDL:

<EntityType Name="MyEntity">
  <Key>
    <PropertyRef Name="Id"/>
  </Key>
  <Property Name="Id" Type="Int32" Nullable="false" annotation:StoreGeneratedPattern="Identity"/>
  <Property Name="Name" Type="String" Nullable="false" MaxLength="50" Unicode="true" FixedLength="false"/>
  <Property Name="CreatedAt" Type="DateTime" Nullable="false" annotation:StoreGeneratedPattern="Identity"/>
  <Property Name="Timestamp" Type="Binary" Nullable="false" MaxLength="8" FixedLength="true"
    annotation:StoreGeneratedPattern="Computed" ConcurrencyMode="Fixed"/>
</EntityType>

The side effect of configuring StoreGeneratedPattern on a property is different behavior when setting the value in the application. Properties with StoreGeneratedPattern.Identity cannot be set in the application at all. If you try that you will get an exception when you call SaveChanges. Properties with StoreGeneratedPattern.Computed can be set in the application but the value is not used. The only exceptions are properties with ConcurrencyMode.Fixed. These properties are used but only if you set them when the entity is not tracked by a context. I will further discuss this in another blog post because it is the big issue in optimistic concurrency handling in Entity Framework.

Posted on March 17, 2011 by Ladislav Mrnka
Filed under: Entity framework
Leave a comment
Comments (15) Trackbacks (2)
  1. Doesnt seem to be fixed to me. Have even tried creating a new edmx file and still it only sets the csdl section and ignores the ssdl :(

  2. hi checkout this KB2561001
    and there is the possibility that it is only fixed internally without being reflected to the edmx XML file
    i changed Oracle.HR.EMPLOYEE.EMPLOYEEID GeneratedStorePattern to Identity in CSDL and it was reflected to SSDL.
    It seems that I can not refresh the model anymore from database… perhaps i am doing something wrong?

    if you use an Oracle database (ODB.NET) it will not set the the property even if you have Trigger and Sequence defined unfortunately. (Not sure if it is a ODP.NET problem or really the API for the EDM designer does not provide a way to set it without having an Identity but Trigger/Sequence … sigh)

  3. I have the same problem with my VS 2010 Pro. SP1 10.0.40.219.1 SP1Rel and Framework 4.0.30319 SP1Rel.,
    But setting by hand the property as described in the article my solution works fine, even if (@Stefan U7) I’m using Oracle DB with triggers and sequences.
    Anyone has found the solution to have authomatically this property set to the SSDL portion as well??
    Thanks

  4. Just fixed checking out the KB2561001
    Thanks Stefan

  5. The Code First equivalent DatabaseGenerated.Computed will only work with rowvesion columns.

  6. Well, I’m using EF 4.1 with Oracle and this problem maintains. It’s VERY annoying.

    • /me retracts

      Installed the hotfix and it worked!

      I have “identity” columns (with table triger) that are reflected in the object’s property after safe.

      oddly, i’m not able to have the same behaviour with a date column which has a default value of SYSDATE. that is, the correct value is in fact stored in the database, but it’s being reflected back to the object’s date property, after save.

  7. This worked for me. Thanks!

    I had some columns that were already set to “Identity”. I had to change them to “None” then back to “Identity” before it worked. Then in compare versions I could clearly see that the SSDL section had been changed.

    http://stackoverflow.com/questions/9610402/using-entity-framework-with-oracle-and-mapping-inheritance-table-per-type-tri/10098525#10098525

  8. Hi, we have the same problem in a new MVC 4 project using VS2012, EF 5 and ODP.NET for Oracle 11.

    Everytime we “generate database from model”, the ssdl part on edmx file loses the “storedGeneratedPatter=Identity” attribute.

    Does anyone know how to workaround (not manually!) or fix the problem?

  9. I have a requirement where a column value is not passed during insert but automatically set by default constraint in database but value should be passed during update. Neither StoreGeneratedPattern=”Identity” nor StoreGeneratedPattern=”Computed” solve this issue.
    Is there any way of acheiving this at all ? Entity Framework version is 4.x

  10. Thank you !! so much !! you save me

  11. Thank you for detailed explaination being new to EF6 I was not able to resolve it if you wouldn’t have posted this.
    Thank you
    http://stackoverflow.com/questions/25851931/entity-framework-6-primary-key-configuration-issue-on-save-of-entities


Leave a comment