2

I'm using Entity Framework 4.1 and have a seemingly simple requirement: I want to either get an entity by a unique key, or if it doesn't already exist, create it:

        var user = db.Users.SingleOrDefault(u => u.Sid == sid);
        if (user != null)
            return user;

        user = new User(sid);
        db.Users.Add(user);

Normally this works fine, but when I run a bunch of tests together (using MSTest) one of them consistently fails with "Sequence contains more than one element". When I run that test by itself it works fine.

The problem seems obvious: multiple threads are calling the above code concurrently and each create a new User row. But what is the solution?

The proper solution is a transaction, of course, but I just cannot get it to work. EF won't use a normal DbTransaction if I start one. If I use a TransactionScope it either has no effect (the same error occurs) or EF tries and fails to start a distributed transaction, even if I follow the advice about opening a connection first.

This is really frustrating, because it is such a trivial thing to do with plain old SQL: begin transaction, SELECT, INSERT, commit transaction. How can I get this to work in EF? It doesn't have to be using transactions - whatever makes it work.

EMP
  • 59,148
  • 53
  • 164
  • 220
  • 1
    Btw. transaction itself will not ensure that record is unique even if you use plain SQL. You will have to use locking table hint in `SELECT` to ensure that. EF doesn't support table hints. – Ladislav Mrnka Aug 09 '11 at 15:02
  • You're right, I didn't think of that. Looks like a UNIQUE constraint and catching the error really is the only way. – EMP Aug 10 '11 at 00:40

1 Answers1

2

The first statement (the only one which could cause the error you describe) will never fail if your DB has a UNIQUE constraint on Sid. Does it? It should. That's the only way to make sure that the sid is truly, globally unique.

Craig Stuntz
  • 125,891
  • 12
  • 252
  • 273
  • Ah, well, that's another whole can of worms. I want a unique constraint, but how do I get entity framework to create one when it creates the DB from the model? Even if I had one, though, it wouldn't solve the underlying problem - it would only be caught earlier. – EMP Aug 09 '11 at 15:02
  • [Here's how to do it.](http://stackoverflow.com/questions/4413084/unique-constraint-in-entity-framework-code-first) Yes, this solves the problem. Indeed, it's the *only* solution for the problem. Even in pure SQL you can get into a conflict with uncommitted data. `UNIQUE` constraints solve this. You just need to handle the error when it occurs. – Craig Stuntz Aug 09 '11 at 15:04
  • One other thing: [Formal support for unique constraints in EF is coming RSN.](http://blogs.msdn.com/b/efdesign/archive/2011/03/09/unique-constraints-in-the-entity-framework.aspx) – Craig Stuntz Aug 09 '11 at 15:06
  • Thanks, I added the UNIQUE constraint as described there - which has a much broder use than just this failing test, so it was very handy. – EMP Aug 11 '11 at 02:05