C#, entity framework, auto increment

Question

I'm learning Entity Framework under VC# 2010.

I have created a simple table for learning purposes, one of the fields is "id" type integer, identity set to true. I've generated Entity Data Model from that table and connected it with dataGridView. The problem is that it doesn't auto increment - each inserted row wants to be id=0 (which is impossible of course, since id must be unique)

What am I doing wrong? How should I configure EF or the SQL db itself?

Answer 1

Check in your EDMX model, that the autoincrement field's StoreGeneratedPattern attribute is set to "Identity". In this way, EF knows that the autonumbers are handled by the DB.

Here this is explained better: Autonumber with Entity Framework

Answer 2

The identity isn't set and incremented just by adding to the entity set... The entity isn't actually saved to the db until you call context.SaveChanges()...

db.AddToUserSet(user);//Added to EF entity collection
db.SaveChanges();//INSERT executed in db, Identity set and incremented.

Answer 3

I had similar issues, which occurred in EF6 (did work in EF4 without transactions, EF 4 used implicit transactions with the right scope).

Just creating a new entity and saving it did not help in my case (see the comments of the other answers, they had similar issues with using dc.SaveChanges() only to auto-update).

Consider the following code (CustomerId is the primary key with auto-increment):

public void UpdateCustomer(string strCustomerName, string strDescription)
{
  using (var transaction = CreateTransactionScope())
  {
    MyCustomer tbl=null;
    Func<MyCustomer, bool> selectByName=(i => i.CustomerName.Equals(strCustomerName));
    var doesRecordExistAlready = dc.MyCustomers.Any(selectByName); 
    if (doesRecordExistAlready) 
    {
        // Updating
        tbl=dc.MyCustomers.Where(selectByName).FirstOrDefault();        
        tbl.Description=strDescription;
    }
    else
    {
        // Inserting
        tbl=new MyCustomer(); 
        var maxItem=
           dc.MyCustomers.OrderByDescending(i => i.CustomerId).FirstOrDefault();
        var newID = maxItem==null ? 1 : maxItem.CustomerId+1;
        tbl.CustomerId=newID;
        tbl.CustomerName=strCustomerName; 
        tbl.Description=strDescription;
        dc.MyCustomers.AddObject(tbl);      
    }
    dc.SaveChanges(); // save all changes consistently          
    transaction.Complete(); // commit
  }
}

And the helper function to create the right transaction context is:

// creates the right transaction scope
public static System.Transactions.TransactionScope CreateTransactionScope() 
    // needs to add ref: System.Transactions
 { 
    var transactionOptions = new TransactionOptions 
    { 
        IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted,
        Timeout = new TimeSpan(0,0,10,0,0) //assume 10 min is the timeout time
    }; 
    var scopeOption=TransactionScopeOption.RequiresNew;
    var scope = new System.Transactions.TransactionScope(scopeOption, 
               transactionOptions); 
    return scope;
} 

The trick is here, to allow reading uncommitted - hence you can query the max ID and add 1 to id. What I wasn't able to achieve is to let SQL server generate the ID automatically, because EF doesn't allow to omit the CustomerId upon creation.

To read more about transaction scope, look here.

Answer 4

Be sure you're saving your Entities back to the database before you try to read the auto-incremented Id value.

Your Id won't be set by auto-increment until the first time it is actually saved to the database.

Answer 5

Yes. LINQ to SQL behaves the same way. The id will not be set until it is saved to the database. Until you do, all the ids will be zero (as you've already seen).