Exception Handling in Entity Framework for SELECTs

Question

One of the nice features of using Entity Framework and IEnumerables is that you don't have to worry about when the data is actually queried from the DB. You just pass around what will be query results and when it's actually needed, EF will get it for you. However, when it comes to putting a try/catch around your SELECT code, then it becomes an issue, because it's not easy to figure out exactly when the DB will be queried.

I'm getting intermittent DB timeouts right now, and I'd like to log the SQL that EF uses when it happens, but I'm running into the problem I just described above. Is there any convenient way for me to handle this? Or am I looking at the problem wrong?

score 4 · Answer 1 · answered Oct 05 '12 at 20:41

You should be using proper try/catch error handling whenever executing a functional block of code that you expect might except (as in this case whenever you execute a lamba expression and/or LINQ query against your EF data context). The real problem is why are you receiving timeouts.

I'd like to respectfully point out one potential fallicy with your above statement that may provide some help in diagnosing and fixing your timeouts. "...you don't have to worry about when the data is actually queried from the database." I would suggest that in the course of constructing a nominally complex LINQ query, you actually do need to be quite aware of when the database is going to get hit. You are going to want to keep your LINQ queries as IQueryable for as long as possible before materalizing them via a call to ToList(), Distinct(), Count(), etc.

So let's say that you're querying a million-row table and you are parsing through potential criterion, you should wait until the very end to materialize the query with ToList() as this is the point at which the SQL statement generated by EF will be executed on the db:

using(var context = CreateEFContextFactory())
{
   var x = (from d in context.MyBigTable select d);
   if(!string.IsNullOrWhitespace(stringParam1))
      x = (from d in x where x.Field1 == stringParam1 select d);
   if(intParam2 > 0)
      x = (from d in x where x.Field2 == intParam2 select d);

   var listOfMyBigTableObjects = x.Distinct().ToList();  //point of sql execution
}

This definitely makes sense, and I totally agree that you should keep it an IQueryable as long as possible. I think I probably have had the misconception that LINQ might materialize it early sometimes or something -- that there's no guaranteed point, but I guess that's not true. — JoeCool, Oct 08 '12 at 15:01

score 1 · Answer 2 · answered Oct 05 '12 at 20:22

1

You kind of know when the query will be executed, e.g. .ToList(), .FirstOrDefault(), .Any(), etc. So you should put your try catch block around them.

answered Oct 05 '12 at 20:22

AD.Net

13,352
2
28
47

Is there any way to set a default exception handler to EF for all of these or anything like that? Or like a default SQLException handler? – JoeCool Oct 05 '12 at 20:24

score 1 · Answer 3 · edited May 23 '17 at 12:20

It sounds like you're trying to do a couple of things here: view the SQL that is being generated by Entity Framework, and add a global error handler.

If you are writing a .NET web app or api, you can add a default error handler, like elmah. If you are using MVC, the answer for the global error handler demonstrates setting elmah up as a global handler, but you could easily customize that example, to use your own logging utility.

Exception Handling in Entity Framework for SELECTs

3 Answers3