Developer's Notes

Recently I've doing some works using MS SQL Compact Edition and for data layer ADO.NET Entity Framework was chosen. Now MS only recently added support for SQLCE in the framework, so its kind of beta inside beta. Some features are not working properly or missing, as for example, support for identity columns. While SQLCE provides support for auto-generated columns, the framework doesn't yet have it. And of course all my IDs in tables are auto-generated integer values, e.g. identity.

Of course the only option was to generate ID at client. But I didn't want to change everything to be GUID, which looks like the only option at first glance, so I was looking for a way to generate integer values. I wouldn't even think about it if I was working with SQL Server, but at the end this is SQL CE, which is not supposed to (and would not in the scope of this project) be used by different processes, so I can synchronize identity generating in-process without problems.

The solution is not something genius and its simple, but its worked for me. The only downside that I saw is that I have to add partial class for every entity in my database. Well, since I had about 15 of them, this was not a big deal. The method is added to the class that extends ObjectContext, e.g. your model class:

private static Dictionary<Type, int> _primaryKeysCache = new Dictionary<Type, int>();
public static int GenerateID<TEntity>(
    Func<Model, ObjectQuery<TEntity>> table, 
    Func<TEntity, int> idSelector) where TEntity : EntityObject
{
    lock (_primaryKeysCache)
    {
        int primaryKey = 0;
        if (!_primaryKeysCache.TryGetValue(typeof(TEntity), out primaryKey))
        {
            using (Model model = Model.CreateContext())
            {
                primaryKey = table(model).Any() ? table(model).Max(idSelector) : 0;
            }
        }
        primaryKey++;
        _primaryKeysCache[typeof(TEntity)] = primaryKey;
        return primaryKey;
    }
}

In this method, "Model" is my ObjectContext and the "Model.CreateContext()" is a simple static method that returns a new instance of objects model.

Usage of this method is simple - just call it when you want to generate a new ID value. Only warning - do not do this in object's constructor, you will get endless recursion. In the example below, I have a class that stores information about single file on disk. I always create a new class using the method "New()":

partial class GenericFile
{
    public static GenericFile New()
    {
        GenericFile ret = new GenericFile();
        if (Model.ShouldGenerateID)
            ret.ID = Model.GenerateID(m => m.Files, f => f.ID);
        return ret;
    }
}

Another thing you might note here - the static property "ShouldGenerateID" on my model. I thought to myself that probably in the future, the issue will be solved and I will not need to generate IDs myself. So I have added this boolean that currently always returns true.

Updated: June 3, 2008: For general info on how to start with SQLCE and Entity Framework, see this post in SQLCE Devs' blog