GUID or int entity key with SQL Compact/EF4?

Question

This is a follow-up to an earlier question I posted on EF4 entity keys with SQL Compact. SQL Compact doesn't allow server-generated identity keys, so I am left with creating my own keys as objects are added to the ObjectContext. My first choice would be an integer key, and the previous answer linked to a blog post that shows an extension method that uses the Max operator with a selector expression to find the next available key:

public static TResult NextId<TSource, TResult>(this ObjectSet<TSource> table,  Expression<Func<TSource, TResult>> selector) 
    where TSource : class
{
    TResult lastId = table.Any() ? table.Max(selector) : default(TResult);

    if (lastId is int)
    {
        lastId = (TResult)(object)(((int)(object)lastId) + 1);
    }

    return lastId;
}

Here's my take on the extension method: It will work fine if the ObjectContext that I am working with has an unfiltered entity set. In that case, the ObjectContext will contain all rows from the data table, and I will get an accurate result. But if the entity set is the result of a query filter, the method will return the last entity key in the filtered entity set, which will not necessarily be the last key in the data table. So I think the extension method won't really work.

At this point, the obvious solution seems to be to simply use a GUID as the entity key. That way, I only need to call Guid.NewGuid() method to set the ID property before I add a new entity to my ObjectContext.

Here is my question: Is there a simple way of getting the last primary key in the data store from EF4 (without having to create a second ObjectContext for that purpose)? Any other reason not to take the easy way out and simply use a GUID? Thanks for your help.

Answer 1

One reason to avoid Guids would be size = memory and storage space consumption.

You could also query SQL Compact metadata like so:

SELECT AUTOINC_NEXT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Categories' AND AUTOINC_NEXT IS NOT NULL

Answer 2

I ended up going with a GUID.

• The size/performance issues aren't critical (or even noticeable) with SQL Compact, since it is a local, single-user system. It's not like the app will be managing an airline reservation system.

• And at least at this point, there seems to be no way around the "no server-generated keys" limitation of the SQL Compact/EF4 stack. If someone has a clever hack, I'm still open to it.

That doesn't mean I would take the same approach in SQL Server or SQL Express. I still have a definite preference for integer keys, and SQL Compact's bigger siblings allow them in conjunction with EF4.

Answer 3

Use a Guid. AutoIncrement is not supported on Compact Framework with Entity Framework.

Also, if you ever want to create a application which uses multiple data sources, int PK's are going to fall apart on you very, very quickly.

• With Guid's, you can juse call Guid.NewGuid() to get a new key.
• With int's, you have to hit the database to get a valid key.

If you store data in multiple databases, int PK's will cause conflicts.

Answer 4

What I've done for SQL CE before, and I assume we have a single application accessing the database, is to calculate the MAX value on startup and put it in a static variable. You can now hand out sequential values easily and you can make the code to generate them thread safe very easily.