Linq To SQL: Prevent creation of duplicates

Question

Consider the following simple User table

enter image description here

I want to write Linq To SQL Code that returns a user by some predicate, e.g. the email address, or creates the user if he does not yet exists. The code might look like

User GetUser(String name, String email) {
    using (var context = new DataContext()) {
        User user = context.Users.FirstOrDefault(u => u.email.Equals(email));
        if (user != null)
            return user;
        user = new User() { name = name, email = email };
        context.Users.InsertOnSubmit(user);
        context.SubmitChanges();
        return user;
    }
}

The code might get executed in parallel, but should not create duplicate users.

I would prefer to not create a unique constraint for the table for several reasons. Do you see any alternative that could be realized using Linq To SQL and without touching the database?

ur code is n't it already checking is there any duplicate or not? — NomNomNom
– NomNomNom, Commented Sep 1, 2014 at 10:47

Marcin Wachulski · Accepted Answer · 2014-09-01 20:33:24Z

4

Use TransactionScope (Serializable isolation level is by default):

User GetUser(String name, String email) 
{
    using (var tran = new TransactionScope())
    {
        using (var context = new DataContext()) 
        {
            User user = context.Users.FirstOrDefault(u => u.email.Equals(email));
            if (user != null)
                return user;
            user = new User() { name = name, email = email };
            context.Users.InsertOnSubmit(user);
            context.SubmitChanges();

            tran.Complete();

            return user;
        }
    }
}

More info on Transactions support in LINQ to Sql: http://msdn.microsoft.com/en-us/library/bb386995

edited Sep 1, 2014 at 20:33

answered Sep 1, 2014 at 20:09

Marcin Wachulski

5774 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

usr Over a year ago

Instead of creating a duplicate this will deadlock. But at least it is safe and it is as good as it can get with L2S.

Marcin Wachulski Over a year ago

usr - could you describe an example of deadlock for OP in this case?

usr Over a year ago

Yes, the case of a conflict. Both transactions read and S-lock, then both transactions try to write and X-lock. Classic deadlock. This is exactly the case where without SERIALIZABLE a duplicate row would have been inserted.

usr Over a year ago

I upvoted this. This is a sane solution. It would benefit from a retry loop, though.

Collectives™ on Stack Overflow

Linq To SQL: Prevent creation of duplicates

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related