Tag Archives: SQLCE

Using Identity Element with Entity Framework + SQL Compact

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: , , ,