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?

Solution 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

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

Solution 3

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

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

Solution 5

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.

View more solutions

Comments

[migajek]

the problem is, that while calling SaveChanges (after adding multiple rows) it throws an exception about duplicated primary key...

[migajek]

the problem is, that while calling SaveChanges (after adding multiple rows) it throws an exception about duplicated primary key...

[Justin Niessner]

@vic - Interesting. Are you sure it's failing on the insert for the Auto-Incremented key table?

[Rangoric]

Yeah generally from the VS2010 generated pieces you will have to set the StoreGenerated Value in the top large block of xml. You are getting multiple primary keys because unless you change that, EF is generating the ID for you, as 0.

[arviman]

It's crazy that they haven't fixed this ubiquitous bug yet. It's version 4, come on EF team!!

[arviman]

A lot of times, people forget to mark the column with an Identity and auto increment value. EF does pick this up from the DB.

[Davut Gürbüz]

here is the hotfix for designer of VS2010SP1 may help you connect.microsoft.com/VisualStudio/Downloads/…