7

I need a way to distinguish between SQL exceptions using entity framework LINQ, for example how to distinguish foreing key constraint violation, or unique constraint violation when all i get from the DbUpdateException is a ton of nested inner exceptions and useless long error messages? Are there any lower level exceptions where i can do something like "Catch FKException"; catch "uniqueException" or something like that.

1
  • Often the inner exceptions will contain numeric error codes, have you tried looking those up? Post a example of a exception in your question, be sure to highlight the text and click the { } button to make it format nicely. Commented Sep 27, 2013 at 3:16

3 Answers 3

13
+50
            try
            {
                //code
            }
            catch (System.Data.Entity.Validation.DbEntityValidationException e)
            {
                string rs = "";
                foreach (var eve in e.EntityValidationErrors)
                {
                    rs = string.Format("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:", eve.Entry.Entity.GetType().Name, eve.Entry.State);
                    Console.WriteLine(rs);

                    foreach (var ve in eve.ValidationErrors)
                    {
                        rs += "<br />" + string.Format("- Property: \"{0}\", Error: \"{1}\"", ve.PropertyName, ve.ErrorMessage);
                    }
                }
                throw new Exception(rs);
            }
Sign up to request clarification or add additional context in comments.

2 Comments

If you wanted to be really kind the last line should be throw new Exception(rs, e) so the next person up the chain could check the inner exception and see the stacktrace and whatnot. (Also throwing a custom exception instead of a generic Exception would be nice too)
But i actually need to distinguish between them, i need to throw different exceptions depending on what kind of database error it was.
5

Using sql error codes...

catch (DbUpdateException ex)
                    {
                        var sqlex = ex.InnerException.InnerException as SqlException;

                        if (sqlex != null)
                        {
                            switch (sqlex.Number)
                            {
                                case 547: throw new ExNoExisteUsuario("No existe usuario destino."); //FK exception
                                case 2627:
                                case 2601:
                                    throw new ExYaExisteConexion("Ya existe la conexion."); //primary key exception

                                default: throw sqlex; //otra excepcion que no controlo.


                            }
                        }

                        throw ex;
                    }

Comments

0

I wrote couple utility methods for this:

public static class DbUtils
{
    /// <summary>
    ///     Takes a code block that updates database, runs it and catches db exceptions. If the caught
    ///     exception is one of those that are ok to ignore (okToIgnoreChecks) then no
    ///     exception is raised and result is returned. Otherwise an exception is rethrown.
    /// 
    ///     This function is intended to be run within an explicit transaction, i.e.:
    ///     using (var transaction = db.Database.BeginTransaction()), which should be committed/rolledback afterwards.
    ///     Otherwise, if you don't use a transaction discard the db context or in other words make this operation
    ///     the only one that you run within implicit transaction.
    /// 
    ///     This function can wrap a single DB statement, but it's more efficient to wrap multiple statements
    ///     so that locks are held for shorter period of time.
    ///     If an exception occurs within a transaction and is caught by this function, all other changes
    ///     will be still saved to DB on commit if transaction is used.
    /// </summary>
    /// <typeparam name="T">Any result returned by the code block</typeparam>
    /// <param name="context">Database connection</param>
    /// <param name="dbCodeBlock">
    ///     Code block to execute that updates DB. It's expected, but not critical that
    ///     this code does not throw any other exceptions. Do not call SaveChanges() from the code block itself. Let this
    ///     function do it for you.
    /// </param>
    /// <param name="okToIgnoreChecks">
    ///     List of functions that will check if an exception can be ignored.
    /// </param>
    /// <returns>Returns number of rows affected in DB and result produced by the code block</returns>
    public static Tuple<int, T> IgnoreErrors<T>(DbContext context,
        Func<T> dbCodeBlock, params Func<DbUpdateException, bool>[] okToIgnoreChecks)
    {
        var result = dbCodeBlock();
        try
        {
            var rowsAffected = context.SaveChanges();
            return Tuple.Create(rowsAffected, result);
        }
        catch (DbUpdateException e)
        {
            if (okToIgnoreChecks.Any(check => check(e)))
                return Tuple.Create(0, result);
            throw;
        }
    }

    public static bool IsDuplicateInsertError(DbUpdateException e)
    {
        return GetErrorCode(e) == 2601;
    }

    public static bool IsForeignKeyError(DbUpdateException e)
    {
        return GetErrorCode(e) == 547;
    }

    public static T UpdateEntity<T>(DbContext context, T entity, Action<T> entityModifications)
        where T : class
    {
        return EntityCrud(context, entity, (db, e) =>
        {
            db.Attach(e);
            entityModifications(e);
            return e;
        });
    }

    public static T DeleteEntity<T>(DbContext context, T entity)
        where T : class
    {
        return EntityCrud(context, entity, (db, e) => db.Remove(e));
    }

    public static T InsertEntity<T>(DbContext context, T entity)
        where T : class
    {
        return EntityCrud(context, entity, (db, e) => db.Add(e));
    }

    public static T EntityCrud<T>(DbContext context, T entity, Func<DbSet<T>, T, T> crudAction)
        where T : class
    {
        return crudAction(context.Set<T>(), entity);
    }
}

Here is how you can use it. Example of inserting a potentially duplicate row:

DbUtils.IgnoreErrors(_db, () => DbUtils.InsertEntity(_db, someEntity),
  DbUtils.IsDuplicateInsertError);

No exception will be thrown.

Similar to previous example, but handle FK violation exception explicitly:

        try
        {
            var numInserted = DbUtils.IgnoreErrors(_db, () => DbUtils.InsertEntity(_db, someEntity), DbUtils.IsDuplicateInsertError).Item1;
            // no FK exception, but maybe unique index violation, safe
            // to keep going with transaction
        }
        catch (DbUpdateException e)
        {
            if (DbUtils.IsForeignKeyError(e))
            {
              // you know what to do
            }
            throw; // rethrow other db errors
        }

Eventually you can call commit transaction if you have an explicit transaction, otherwise save has been called on context already.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.