CODEWITHSUNDEEP
c#entity-framework
JoeCool
JoeCool

Reputation: 4432

Exception Handling in Entity Framework for SELECTs

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?

Upvotes: 1

Views: 2722

Answers (3)

andes
andes

Reputation: 514

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.

Upvotes: 1

Daniel Graham
Daniel Graham

Reputation: 399

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
}

Upvotes: 4

AD.Net
AD.Net

Reputation: 13409

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.

Upvotes: 1

Related Questions