Entity Framework Migrations, seeding and server farms
Entity Framework Migrations offers a way to evolve your database schema over time. The schema is fine, but what about your actual data? You may have configuration settings in the database and how do you handle it when you need to add a new configuration setting?
With Entity Framework Migrations enabled, your seeder function will run every time you restart your application, so as long as your new configuration setting is in your seeder all will be well. Well, sort of... How do you avoid adding a duplicate row the next time your application is restarted? A simple answer with Code First would be something like;
That works well until you have more than one server as you may then have two or more servers running this code at the same time.
Personally I happen to use Azure so I always have at least two servers.
In Entity Framework 5 you have the AddOrUpdate extension method but you may not always want to update stuff - sometimes you may want to insert it if it doesn't exist and otherwise leave it alone. Or you may be using EF4.
The key to handling this is to obtain a table lock before you check and insert. Now, table locks are normally very bad things because it stops other users from reading from the table – which is of course what you want in this scenario – but you do need to carefully consider any adverse effect this may have on your application. Tom Peplow has a a very good article on how to achieve table locks with Entity Frameworks. I have adapted his approach for use specifically when seeding with Code First.
For the examples below, I am imagining an entity called “Entry” and the underlying database table is called “Entries”. Entry has a “Value” property which is the one I want to be unique in my example, but just replace the lambdas as required for your needs.
The code below will obtain a table lock before checking and inserting;
If you find yourself needing to do this a lot, you could create an extension method like this, which can work for any entity and table;
And call it like this:
With Entity Framework Migrations enabled, your seeder function will run every time you restart your application, so as long as your new configuration setting is in your seeder all will be well. Well, sort of... How do you avoid adding a duplicate row the next time your application is restarted? A simple answer with Code First would be something like;
if (!context.Entries.Any(e => e.Value.Equals(value)))
{
var entry = new Entry() { // initialize };
context.Entries.Add(entry);
context.SaveChanges();
}
That works well until you have more than one server as you may then have two or more servers running this code at the same time.
Personally I happen to use Azure so I always have at least two servers.
In Entity Framework 5 you have the AddOrUpdate extension method but you may not always want to update stuff - sometimes you may want to insert it if it doesn't exist and otherwise leave it alone. Or you may be using EF4.
The key to handling this is to obtain a table lock before you check and insert. Now, table locks are normally very bad things because it stops other users from reading from the table – which is of course what you want in this scenario – but you do need to carefully consider any adverse effect this may have on your application. Tom Peplow has a a very good article on how to achieve table locks with Entity Frameworks. I have adapted his approach for use specifically when seeding with Code First.
For the examples below, I am imagining an entity called “Entry” and the underlying database table is called “Entries”. Entry has a “Value” property which is the one I want to be unique in my example, but just replace the lambdas as required for your needs.
The code below will obtain a table lock before checking and inserting;
using (var scope = new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions()
{ IsolationLevel = IsolationLevel.ReadCommitted }) )
{
context.Database.ExecuteSqlCommand(
"select 1 from Entries with (tablockx, holdlock) where 0 = 1");
if (!context.Entries.Any(e => e.Value.Equals(value)))
{
var entry = new Entry() { // Initialize };
context.Entries.Add(entry);
context.SaveChanges();
}
scope.Complete();
}
If you find yourself needing to do this a lot, you could create an extension method like this, which can work for any entity and table;
public static class DbContextExtensions
{
public static bool UniqueInsert<TContext, TEntity>(
this TContext context,
Func<TContext, IDbSet<TEntity>> dbSetAccessor,
TEntity entity,
Func<TEntity,bool> areSame,
string underlyingTableName)
where TContext : DbContext
where TEntity : class
{
using (var scope = new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions()
{ IsolationLevel = IsolationLevel.ReadCommitted }))
{
try
{
context.Database.ExecuteSqlCommand(
String.Format("select 1 from {0} with (tablockx, holdlock) where 0 = 1",
underlyingTableName));
var set = dbSetAccessor(context);
if (!set.Any(areSame))
{
set.Add(entity);
context.SaveChanges();
return true;
}
return false;
}
finally
{
scope.Complete();
}
}
}
}
And call it like this:
var entry = new Entry() { // initialize };
context.UniqueInsert(
c => c.Entries,
entry ,
dbEntity => dbEntity.Value.Equals(entry.Value),
"Entries");