1

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.

Community
  • 1
  • 1
David Veeneman
  • 18,912
  • 32
  • 122
  • 187
  • The approach you show does not look thread safe at all! What's to stop two threads from getting the same ID? – Ian Mercer Mar 26 '10 at 06:13
  • Not an issue for me--I only use SQL CE for single-user applications, and I only generate records on a single thread. Good point though, if someone wants to multi-thread record creation in SQL CE. – David Veeneman Mar 26 '10 at 12:47

4 Answers4

3

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.

David Veeneman
  • 18,912
  • 32
  • 122
  • 187
1

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.

user276695
  • 1,360
  • 1
  • 8
  • 4
1

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.

Ian Mercer
  • 38,490
  • 8
  • 97
  • 133
0

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

ErikEJ
  • 40,951
  • 5
  • 75
  • 115