CODEWITHSUNDEEP
entity-frameworkconcurrencysqlexception
Biscuits
Biscuits

Reputation: 1807

Avoid inserting duplicate records in Entity Framework

I have a typical scenario where users enter data that is inserted into a SQL database using Entity Framework 6.0. However, some rows that are part of the entity need to be unique (already enforced with unique key constraints in the database).

To avoid possible concurrency or performance issues I favour these checks to be left done by SQL Server.

When attempting to save a new entity that holds a duplicate row, a DbUpdateException is thrown by Entity Framework. The inner exception is a SqlException with its Number equal to 2627, and a message that reads:

"Violation of UNIQUE KEY constraint 'UK_MyTable_MyRule'. Cannot insert duplicate key in object 'dbo.MyTable'".

Considering that there are several tables involved, which may each have their own unique constraints defined, is there no better way to conclude a friendlier message to the user that reads:

"A MyEntity with the name 'MyEntity1' already exists."

...without having to infer this through the Number and Message properties from the SqlException?

For example:

try
{
    ...
    context.SaveChanges();
}
catch (DbUpdateException exception)
{
    var sqlException = exception.InnerException as SqlException;

    bool isDuplicateInMyTable3 =
        sqlException != null &&
        sqlException.Number = 2627/*Unique Constraint Violation*/ &&
        sqlException.Message.Contains("'UK_MyTable3_");

    if (isDuplicateInMyTable3)
    {
        return "A MyTable3 with " + ... + " already exists.";
    }

    throw exception;
}

Is there a "cleaner" way to achieve the same that does not involve looking through the error message string?

Upvotes: 1

Views: 3822

Answers (1)

phil soady
phil soady

Reputation: 11348

You may like to enjoy the AddOrUpdate method. Research it first. I have noted experts warning of over zealous use.

  Context.Set<TPoco>().AddOrUpdate(poco);

can still throw other EF\DB exceptions. But Duplicate primary key should not be one of them. Other constraint issues are as before.

Upvotes: 1

Related Questions