Using Identity Element with Entity Framework + SQL Compact

17 May

The problem of using the identity element with the entity Framework in connection with SQL-Compact is that this feature is not supported.
To circumnavigate this problem I decided to write a method that does the same as the database. It searches the highest value of the given identity column and the corresponding table and returns the at 1 higher number. This number can then be used as the ID of the object that has to be inserted into the database.

The only difference between the identity element and this is following.
Suppose I have (1,4,5,8,9) as ID and I delete 8 and 9. The next inserted ID is not 10, as in the identity specification, but it is 6. In my opinion this is no problem.

To doing that you have to call following method:

public static int GetNextId(IQueryable<int> dataOfIds)
    int countOfTuples = dataOfIds.Count();

    if (countOfTuples == 0) 
        return 0;
    return Int32.Parse(dataOfIds.Max().ToString()) + 1;

The call of this method should be as follows:

int nexId = GetNextId(from i in _conn.TableName select i.Id);

where _conn is the generated entity collection of the Entity Framework, TableName the correspoding table and Id an integer representation of the “fake” identity element.


Posted by on May 17, 2010 in C-Sharp, SQL


Tags: , , ,

2 responses to “Using Identity Element with Entity Framework + SQL Compact

  1. Tudor

    July 26, 2010 at 08:59

    Your code is not thread-safe. SQL CE 4.0 now supports automatic identity incrementation when using ADO.

  2. michaelmairegger

    July 26, 2010 at 21:04

    Yes, I know that this piece of code is not thread-safe.
    I never realized that there is a way to do that in .NET 4.0. Can you please tell me about that?


Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: