C# integer primary key generation using Entity Framework with local database file (Sdf)

Question

I'm writing a standalone application and I thought using Entity Framework to store my data.

At the moment the application is small so I can use a local database file to get started.

The thing is that the local database file doesn't have the ability to auto generate integer primary keys as SQL Server does.

It's not a problem defining the ID column as "identify" when creating the table, but when trying to call the SaveChanges method it throws the following exception:

{"Server-generated keys and server-generated values are not supported by SQL Server Compact."}

Any suggestions how to manage primary keys for entities in a local database file that will be compatible with SQL Server in the future?

Thanks, Ronny

Answer 1

There are three general techniques I can think of for where the database has no auto-number.

1) Do a MAX(ID_column)+1 first to get the next ID value. However, you need to be aware of multi-user issues here. Also, the numbers are not one-use-only. If you delete a row and add a new row, you will get the same ID. This may or may not be a problem for you.

2) Use a GUID. Pretty much guaranteed to be unique, but does have a large footprint for an ID column.

3) Use a separate key table that holds the last ID that was assigned. This ensures numbers are never reused, but adds an extra table into your database.