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:
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.