1

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?

Alexander
  • 3,129
  • 2
  • 19
  • 33
Biscuits
  • 1,767
  • 1
  • 14
  • 22
  • 1
    The more usual way to avoid inserting duplicate records may not be your preferred technique but it does mean that you can customise your message: http://stackoverflow.com/a/18736484/150342 – Colin Mar 24 '14 at 11:37
  • This is interesting too. Thanks Colin. – Biscuits Mar 24 '15 at 23:13

1 Answers1

1

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.

phil soady
  • 11,043
  • 5
  • 50
  • 95
  • 1
    Start the research here! http://thedatafarm.com/data-access/take-care-with-ef-4-3-addorupdate-method/ – Colin Mar 24 '14 at 11:34
  • thanks colin, i recall Julie L. Warning about it. I use it myself but only with key and full update scenarios. – phil soady Mar 25 '14 at 21:52
  • Thanks phil soady. This seems like a nifty method. I can't seem to find, however, any information about it's concurrency. I'm reluctant to use it for my case, I'm afraid. – Biscuits Mar 24 '15 at 23:36