How do I catch a SqlException thrown by RAISERROR called in a trigger set off by Entity Framework 4?

Question

I have Entity Framework set up to update a table. The update is intercepted by an instead-of trigger, which calls RAISERROR:

CREATE TRIGGER mySchema.UpdateBusinessObjects
ON mySchema.BusinessObjects
INSTEAD OF UPDATE
AS
    RAISERROR(''test error from SQL'',16,1)
    RETURN

In my repository class, I'm attempting to catch the SqlException generated by the RAISERROR in SQL:

public void SaveBusinessObject(BusinessObject b) {
    try {
         repo.Entry(b).State = EntityState.Modified;
         repo.SaveChanges();
    } catch (SqlException ex) {
         // handle exception here
    }
}

The problem is that C# isn't catching the SqlException; it gets passed up to the caller as an unhandled exception ("SqlException was unhandled by user: test error from SQL"). What?!

It looks like EF's SaveChanges() somehow is passing the exception up over my try catch block. I have tried switching my catch statement to catch (Exception ex) in case the EF exception is somehow more general, but I still get an unhandled SqlException. Am I missing something simple here? What's the problem with the SaveChanges() method?

Answer 1

I tried almost the exact same trigger you have on a table and tried to save the corresponding (new) object through the entity framework. From what i can see, the exception being thrown is of the type System.Data.UpdateException and not SqlException. The inner exception is SqlException and does contain the custom message that you have raised in the trigger that is 'test error from SQL'. Hope this helps

Answer 2

There are 2 things :-

1. If you want RAISERROR to throw a SqlException, you need to set its severity above 10. Errors with a severity of 10 and below are informational, and thus don't throw exceptions.

2. Catch EntityException or Set a breakpoint in the general Exception's catch block. In the Immediate Window, inspect the type of the exception: ex.GetType();