Entity Framework, Prevent duplicate records, simultaneous connections

Question

If i use entity framework as the backend for a MVC Web API one controller might look something like this (quick mockup):

   public class PersonController : ApiController
   {
        [HttpPost]
        public void AddPerson(Person val)
        {
            DbContext context = new DbContext();
            if(!context.Persons.Any(x=>x.Email == val.Email))
            {
                context.Persons.Add(val)
                context.SaveChanges();
            }
        } 
   }

The problem being that if this operation was called 50, 100 times every few seconds (probably not a good example) there is a high possibility that multiple entries could be added with the same email address.

If the val parameter was a list of Person you could check the changetracker to see if someone had been added with the email address before you SaveChanges() but this doesn't work when you have lots of calls from different sources.

You cannot have a static DBContext either as it will throw an exception saying its busy.

One idea i did think of was the same setup but having a static method which returns a instance (the same instance) of a dbcontext but with a lock() on it creating a kind of like queue but this could impact performance an isn't a great idea.

How do you get around this?

The example is not relevant to what I'm doing but just something simple to explain the scenario. It doesn't have to be specific too i guess.

Thanks

Steve

Answer 1

put a unique constraint index on the email table? or change your SQL Transaction Isolation level to Serializable? or lock a static object so 1 method can go through that method at a time?

http://msdn.microsoft.com/en-us/library/c5kehkcz.aspx

Answer 2

I don't know who or what the consumer is of this service. But why would the same user be added multiple times per second? Multiple Addings of a Person with the same email address should indicate the same person, but other properties may differ. The question is then which do you want to "win", the first or the last?

A simple and effective way is to set a unique constraint on the Email property in the database and handle the exception in a suitable way.