So, trying to use this simple combination proved as a nightmare for me, than of course, when I thought about a reasonable solution, nothing more fair with you reader than blog about it…
So first, what is the problem?
Current Entity Framework version does not support identity keys using SQL Compact, but off course you know all about, as you are searching this…
Summing ideas from blogs here and there, I thought about extension methods…
They are pretty powerful, and after a few interactions, i could come up with a simple one that does the job:
{
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;
}
}
And it’s pretty easy to use also, look below. db is my model context, and i like to call it database.
Categories is a table of Category, ObjectSet<Category> in C#. So with the extension method above, and can easily call NextId passing it the column I’m interested in getting the next Id and that’s it.
Off course I have deleted here all lock controls, etc, that would not help with this post, but you should provide something similar in your code, although SQLce is supposed to be used in a single machine scenario anyway.
{
db.Categories.AddObject(new Category()
{
categoryID = db.Categories.NextId(f => f.categoryID),
description = "High"
});
int changes = db.SaveChanges();
}
Hope this can help someone out there….
Until next time,
.Sergio
Hey, it sure helped me! Thanks.
Hi.This work arround can be used in .Net Framework 3.5 too : Replace "ObjectSet" by "ObjectQuery" to make it work.Thank you
Pingback: SQL Compact, Identity Columns e Entity Framework « João Felipe Portela
I also got this to work by listening to the SaveChanges event, iterating through any added entries and adding GUIDs before they hit the database. This seems tidier to me than adding lots of partial classes. Details in this article:
http://msdn.microsoft.com/en-us/library/cc716714.aspx
Hey, I took your code and made sume adjustments. I was only dealing with ints, and did away with pasing in the selector. Maybe this will help someone.
public static int NextId(this ObjectSet table)
where TSource : class
{
string primaryKey = table.EntitySet.ElementType.KeyMembers.First().ToString();
//i => i.CustomerId
var param = Expression.Parameter(typeof (TSource));
var exp = Expression.MakeMemberAccess(
param,
typeof (TSource).GetProperty(primaryKey));
Expression<Func> selector = Expression.Lambda<Func>(
exp,
new [] { param });
return (table.Any() ? table.Max(selector) : default(int)) + 1;
}
This is a nice solution. Not sure it’s thread-safe for a multi-user environment, but CE4 is generally for a single user.
And if you’re using CE4 to unit test production code that needs to run on SQL 2008, you can leave your [DatabaseGenerated] annotation on your model, but wrap your DbContext in a proxy that replaces it with the above extension method to manually set the ID just for testing.
You can see my example here: http://stackoverflow.com/questions/4199223/in-memory-db-for-entity-framework-4/5495154#5495154