How can I get the SqlException thrown by RAISERROR caused by a trigger set by Entity Framework 4?

Question

I have an Entity Framework configured to update a table. The update is intercepted instead of the trigger that 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 am trying to catch the SqlException thrown by 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 # does not catch a SqlException; it is passed to the caller as an unhandled exception ("SqlException was unhandled by the user: test error from SQL"). What kind?!

It looks like EF SaveChanges () is somehow throwing an exception on top of my catch try block. I tried switching my catch statement to catch (Exception ex) in case the EF exception is somehow more general, but I still get the unhandled SqlException exception. Am I missing something here? What is the problem with the SaveChanges () method?

Answer 1

I tried almost the same trigger that you have on the table and tried to save the corresponding (new) object through the entity infrastructure. From what I see, the generated exception is of type System.Data.UpdateException, not SqlException. The internal exception is SqlException and contains a special message that you raised in a trigger, which is a "SQL test error". Hope this helps

Answer 2

The only way I know to have an exception while avoiding one try catch has an exception in another thread. Can EF code execute in another thread? Perhaps you can enable the exception by adding a 1º chance in the Exceptions dialog box.

Answer 3

I had this exact problem.

I used the MVC profiler to profile requests. I found that if I remove it from my project, then the exceptions were caught as I expected.

Answer 4

There are 2 things: -

• If you want RAISERROR to throw a SqlException, you need to set it to a level higher than 10. Errors with a severity of 10 or lower are informational and therefore do not throw exceptions.

• Catch EntityException or Set a breakpoint in a common catch Exception block. In the Immediate window, check the type of exception: ex.GetType ();