As you all know, SQL Server Compact Edition now can be used in ASP .NET for small database. I have a small website (phonebook for one city) and decide convert data source from Excel sheet to SQL CE DB. And have got 2 problems.

First is when you are trying first run, you will receive an error: "SQL Server Compact Edition is not intended for ASP.NET development". I found solution with help of "Bite my bytes" and resolve it by putting next code in Global.asax:

void Application_Start(object sender, EventArgs e) 
{
    AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);
}

This setting allows us to use SQL Server CE with ASP .NET.

Next problem is with Entity Framework. Current Entity Framework version does not support identity keys using SQL Compact. If you try to update entity with identity field (autogenerated key field) you will get an error: "Server-generated keys and server-generated values are not supported by SQL Server Compact." So the solution is only to generate new value for key field in the code. I create the class for LINQ extension as it describes and suggested by Sergio.

using System;
    using System.Data.Objects;
    using System.Linq.Expressions;

    public static class ExtensionMethods
    {
        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;
        }
    }

Then you can use it like in an example:

db.AddToPhones(new Phone() { 
                Id = db.Phones.NextId(f => f.Id),
                PhoneNum = Reader["Tel_Numberr"].ToString()
            });
            db.SaveChanges();

As you can see, extension gets maximum value of specified field and returns this value incremented by 1. So be careful to using it…