Tip 29 – How to avoid LazyLoad or Load() reader issues

  • [アーティクル]

If you have code that looks like this:

var results = from c in ctx.Customers
where c.SalesPerson.EmailAddress == “…”
select c;

foreach(var customer in results)
{
Console.WriteLine(customer.Name);
if (IsInteresting(customer))
{
customer.Orders.Load();
foreach(var order in customer.Orders)
{
Console.WriteLine(“\t” + order.Value);
}
}
}

This code will result in two simultaneous readers being opened. I.e. One for enumerating the Customers, and one for enumerating the current Customer’s Orders. And this is only possible if you enable Multiple Active ResultSets (aka MARS). So if MARS isn’t enabled you will get a nasty exception.

NOTE: You may wonder why I put the IsInteresting(..) call in there. I did that because without it this pattern of code is definitely not recommended. You shouldn’t talk to the database in a loop if you can avoid it, i.e. if you know in advance you are going to need the Orders for all Customers, you should use Include(..) to eagerly load the orders.

Enabling MARS is easy you simply put Multiple Active ResultSets=true; in your connection string.

Generally this is not needed though because most Connection Strings are created by the EF Designer, which does this for you. This is the case in Database First in 3.5 and Model First in 4.0 too.

But if *you* provide the ConnectionString, like for example in Code Only, you need to remember to turn on MARS.

This is an equal opportunity offender,because it effects both 3.5 and 4.0.

But is perhaps more insidious in 4.0 because of our new LazyLoading (previously called DeferredLoading) feature.

Anyway, the moral of the story is remember to turn on MARS!

Comments

  • Anonymous
    August 05, 2009
    Actually enabling MARS in applications that plan to targets different database providers through EF would cause an issue. MySQL for example doesn't support MARS.I turn off MARS manually in connection string with SQL Server just to make sure I am safe when building LINQ Queries.