Entity Framework thread safe create entity if doesn't exist

Question

I'd like to write a method that inserts an entity into my database if it doesn't already exist. I'd like it to be thread safe across many servers. So really, it has to be concurrency safe at the database level. Is this possible with Entity Framework? Or do I have to do this with raw SQL?

Here's what I have so far:

public void CreateFooIfDoesntExist(int bar)
{
    using (var dbContext = new MyDbContex())
    {
        if (!dbContext.Set<Foo>().Any(f => f.Bar == bar))
        {
            //Not thread safe because two servers could both fail to find any 
            //Foos at the same time.  Then they'd both try to add a Foo.
            dbContext.Set<Foo>().Add(new Foo { Bar = bar });
            dbContext.SaveChanges();
        }
    }
}

I'm using C# 5.0, EF 5.0 and MSSQL server 2012.

Answer 1

You can create a unique index, so the second save will always fail with a well known error code. The catch is, you need a field other than auto-generated ID to create the unique index on.

try
{
    using (var context ...) 
    {
        context.Foos.Add(new Foo() { UniqueId = id });
        context.SaveChanges();
    }
 }
 catch (UpdateException e) 
 {
     // record with "id" already exists
 }

In more complex scenarios you could use database transactions to synchronize multiple processes. When you perform a database update in a transaction with SERIALIZABLE isolation level SQL Server will place a lock on rows that you updated. This lock will prevent any other process not only from writing but even reading data until the first process ends the transaction. So you would essentially have a lock column/row/table in your schema. Whenever you need to perform a global operation you would start a transaction and update the lock, then do any other work you need to perform. No competing process will be able to read/update your lock until the first process ends the transaction.