use serial field per user in the entity framework

I have an entity with code field. For each user that uses this entity, each entity the user insert must have code. So I wrote code that do the following logic:

1. if the user set the code field - then use his code.

2. if the user doesn't set the code field - read from the db the next serial code (starting from 1, searching the next serial code that doesn't already exists) and use this code.

The probelm is scenario like this:

Assuming the user have the ability to add two entities in single mouse click.

Assuming the next serial code should be "5".

In the first entity the user set code=5 and in the second entity the user doesn't set the code.

Because I am using the entity framework and there is one commit/save changes at the end of the logic, I Insert the first entity (the one with code=5) and for the second entity, searching the db for the next next serial code that doesn't already exists. The next serial code that doesn't already exists in the database is "5". So I set the second entity the code "5".

Eventually I came up with two entities with code=5 which is wrong.

I thought of ways to solve it.

One way is to do SaveChanges right after storing the first entity but this might make many calls to the db, and I am not sure for this solution.

Another way is to search in the DB and in the attached objects but I really don't know how to do it.

Does anyone has any bet开发者_Go百科ter idea?

---

You can use an IDENTITY column to allow SQL Server to auto-populate it when the user doesn't specify one, and when you have one that they do specify you just put it in your INSERT/UPDATE query along with the other fields. It will save your value instead of creating a new one. You do need to check and make sure that the ID hasn't already been used before doing that if you have a unique constraint on that field or if you don't want to allow duplicates when the user specifies a value.

The following example uses a table called MyTable with 3 columns (ID int IDENTITY, FIRST_NAME varchar, LAST_NAME varchar). I would recommend using parameters instead of passing the values in the sql string as I did below since this was just an example and it was faster to put together that way.

String sSQL = "";
String sFields = "";
String sValues = "";
String sCode = "";
// sCode = ... (get user entered code)
String sFirstName = "John";
String sLastName = "Doe";

if (sCode != "") 
{ //Add the user specified value and field for the identity column to the SQL
    sFields = "ID, ";
    sValues = sCode + ", ";
}
sFields += "FIRST_NAME, LAST_NAME";
sValues += "'" + sFirstName.Replace("'","''") + "', '" + sLastName.Replace("'","''") + "'";

sSQL = "INSERT INTO MyTable (" + sFields + ") VALUES (" + sValues + ")"
//execute the sql statement