locked
Fix the Identity and Computed options in EF Code-First, PLEASE RRS feed

  • General discussion

  •  Sometimes, you need to let the DB work out some values. Examples situations are:

    • You want to use the getdate() function to get the value of a field from the DB server because the times in your app servers may not be in sync.
    • You want to generate a custom serial number. The DB is the best place to do this to minimize chances of collision when the DB accessed by multiple services concurrently.
    • You want the account balance in an accounting app to be the sum of all transactions for a given account.
    • The list is virtually endless. 

    You could do this in SQL server using after insert, update triggers, expressions or using User Defined Functions (UDFs) 

    Using EF Database-First or Model-First, you can easily do this by setting any field of any type StoreGeneratedPattern metadata property to Identity or Computed. As expected, Entity framework will not try to insert (in case of Identity) or insert/update (in case of Computed) the value in the DB but will read the value after insert (in case of Identity) and after insert/update (in case of Computed). 

    This is a good thing. 

    This way, it is possible to generate values of any type in multiple fields in the DB using triggers or compute them using UDFs or expressions. Entity Framework will dutifully retrieve such generated values as it inserts or updates a record in the same call to the DB. 

    With EF Code First, it does not seem to be possible to achieve this.  Code-First, at least 4.2 does not have the StoreGeneratedPattern metadata property.  In its place, there is DatabaseGenerated attribute which can be set either through annotations: 

    [DatabaseGenerated(DatabaseGeneratedOption.Identity)] 

    or via the fluent API: 

    modelBuilder.Entity<Invoice>().Property(p => p.InvoiceCreatedDate).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

     As it turns out, DatabaseGenerated attribute in Code First != StoreGeneratedPattern in Model or Database First. DatabaseGenerated attribute seems to be merely a DDL flag; plain and simple. 

    For instance only ‘number’ properties can be marked as Identity. Datetime, strings etc cannot be marked as such. Attempting this will raise the following error:

    "Identity column 'DisplayNumber' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable." 

    Even for the number types that can support Identity, only one property in an entity can be can be marked as Identity and this is usually the Primary Key column. Trying to set more than one property in an entity as Identity will raise the following error: 

    "Multiple identity columns specified for table 'Invoices'. Only one identity column per table is allowed." 

    Secondly, only ‘timestamp’ and ‘rowversion’ can be marked as Computed. Trying to mark a property of any other type as such will give the error: 

    “The store generated pattern 'Computed' is not supported for properties that are not of type ‘timestamp’ or 'rowversion'.” 

    If by now you are thinking, why not insert/update the record, let the DB do it’s magic, then make another call to retrieve the whole entity; tough luck! DB engines, expect you not to *even try* to update any column marked as Computed or Identity. You can't even supply null. Try to update any column marked as Identity or Computed in SQL server and it will complain: 

    The column "TransactionDate" cannot be modified because it is either a computed column or is the result of a UNION operator. 

    The request I make to the EF Code-First team is simple: Let the DatabaseGenerated attribute in Code-First behave the same as StoreGeneratedPattern does in Model-First and Code-First. Please. 

    Regards,

    Ben Gichamba

    Sunday, January 22, 2012 8:46 AM