none
ADO.NET Entity Framework: How to generate next primary key in the Entity Framework?

    Question

  • In Entity Framework, we need to insert New Parents and Children.

    In Julia Lerman "Programming Entity Framework" Charter 5, we see the example (page 130)

    var contact = context.CreateContact(0, "camey", "Combs", DateTime.Now, DateTime.Now);
    var address = new Address();
    address.Street1="One Main Street";
    address.City = "Olympia";
    address.StateProvince="WA";
    address.AddressType = "Business";
    address.ModifiedDate = DateTime.Now;
    //join the new address to the contact
    address.Contact = contact;
    //add new graph to the contact
    context.AddToContacts(contact);
    context.SaveChanges();

    here, assume that Database table "Contact" set its "ContactID" column "Identity" to "true", see the following link
    http://msdn.microsoft.com/en-us/library/ms188665.aspx

    So even though we set the ContactID = '0' in above code, when insert a new row into the Contact table, it will create a correct primary key contactID to the table.

    In the above link mention that, if you delete some rows from the Contact table, there are gaps in the table fro the primary key.
    If we do not want leave some gaps in table, we should not set the "ContactID" column be "Identity" column.

    We use SQL server Compact edition. When we set column ContactID as identity and use SQL generated key, we get runtime error:
    InnerException: System.NotSupportedException
    
               Message="Server-generated keys and server-generated values are not supported by SQL Server Compact."
    We also see the following link:
    http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/1fb7b33e-efa8-4814-9b76-9d50f7c2d94c/

    It talk about using "StoreGeneratedPattern"  <Property Name="Key" Type="uniqueidentifier" Nullable="false" StoreGeneratedPattern="Computed" />
    If you do this, regardless of what you set the key property to in your application, the value will be generated in the database.

    We tried it, and also get a run time error.
    InnerException = {"Modifications to tables where a primary key column has property 'StoreGeneratedPattern' set to 'Computed' are not supported. Use 'Identity' pattern instead. Key column: 'xxxID'. Table: 'xxxModel.Store.yyy'."}

    Our question is how to generate/figureout  a primary key in Entity framework (what is next primary key in the database table)
    and we do not want to use Guid constructor?
    so in above code, we can directly assign next gerneated ContactID to CreateContact() method.
    thx!


    • Moved by Alex Feng (SQL) Friday, October 23, 2009 3:17 AM about ADO.NET Entity Framework (From:SQL Server Compact)
    Tuesday, October 20, 2009 5:18 PM

Answers

  • We have implement the following code to try to find next Primary key for Contact table. If anyone find a better solution, let us know.

    var nextContact = context.Contact.OrderBy("it.ContactID DESC").First();
    int nextContactID = nextContact.ContactID+1;
    var contact = Contact.CreateContact(nextContactID, "camey", "Combs", DateTime.Now, DateTime.Now);
    (Note: somehow we can not use the "Last()" method??? we have to use Orderby descending and then use "First()"method)

     

     

    after creating new Contact entity and call context.SaveChanges().
    Then next time, create new Contact entity, we can call above code to get a next ContactID.
    However, if after create a new Contact and we do not call context.SaveChanges right way.
    We try to create several Contact entities, then the above code always get the same contactID (last row in Contact table).

    Is there a way that we can synchnize the contact entity in the entity framework?
    or we have to add track code ourown to tracking the next ContactID.
    After we called contact.SaveChanges, then we use above code to get the next ContactID.
    thx!
    • Marked as answer by JJChen Friday, October 23, 2009 10:50 PM
    Tuesday, October 20, 2009 5:22 PM