The Four Deadly Sins of LINQ Data Access: Part 1–Overly Chatty Queries

Introduction

I love using LINQ to filter, transform, and aggregate collections. LINQ provides a very concise and expressive syntax that lets you do more with fewer lines of code, which in turn means generally higher productivity, reduced maintenance, and fewer bugs. However, all of these things are unconditionally true only regarding in-memory collections. When it comes to querying a relational DB, LINQ has a dark side: it’s almost too good at hiding what’s really happening. This could cause a naïve developer to write poor data access code, because a query that accesses a DB is almost indistinguishable from one that accesses an in-memory collection. This series will illustrate a few of the “sins” of poor data access that are made easy by LINQ to commit, and how to repent of them.

Confession

The Sin of Overly Chatty queries can take many shapes and forms. Consider the following snippet.

var orders =
     from order in Orders
     where order.ShipCountry == "USA"
     select order;

 foreach (var order in orders)
 {
     #if !LAZY_LOADING_ENABLED
     // Explicitly load reference
     this.Entry(order).Reference(o => o.Customer).Load();
     #endif

    Console.WriteLine(order.Customer.ContactName);
 }

Another manifestation of this sin could be calling a repository method within the loop that takes a single CustomerID parameter and returns the Customer object. Whatever the case may be, can you see what is happening? Every iteration through the loop will result in another new connection and query to the database. This is bad for performance because opening a new connection to a database is a relatively expensive operation. Just to be clear, the problem here is not that there is any danger of connections being used up or improperly disposed. Each connection in this loop will open, request the data for a single customer, close, and be returned to the connection pool. The problem is simply that opening the connection takes time.

Repentance

To repent of this sin, it would be much more efficient to include the Customer in the original query, so only one connection and query is needed. This is where Eager Loading comes into play. Here is one way to reduce the chattiness of the snippet above.

var orders =
    from order in Orders.Include(o => o.Customer) // Alternately, Orders.Include("Customer")
    where order.ShipCountry == "USA"
    select order;

foreach (var order in orders)
{
    Console.WriteLine(order.Customer.ContactName);
}

Note the addition of the Include method. As noted in the comment, either version of the method will have the same effect, though the version that takes an expression as a parameter will yield better compile-time checking that the reference name is correct. In this version, a single query will be issued against the database, returning full Order and Customer objects.

The above example shows eagerly loading a 1:1 “parent” or “lookup” property, but the same approach works for a 1:n “child” or “details” property.

var orders =
    from order in Orders.Include(o => o.Customer).Include(o => o.Order_Details)
    where order.ShipCountry == "USA"
    select order;

foreach (var order in orders)
{
    foreach (var detail in order.Order_Details)
    {
        Console.WriteLine (detail.UnitPrice);
    }
    Console.WriteLine(order.Customer.ContactName);
}

Note that while eagerly loading 1:n properties will reduce query chattiness, it comes at a price: it comes perilously close to, but not quite being, the Sin of Insufficiently Chatty Queries. You see, even though LINQ to Entities represents the results of the query as a collection of Order objects each with their own collection of Order_Details, the database is returning one row per Order_Detail, and each row contains full Order data for that Order_Detail, so a lot of redundant data is being returned. I will discuss this and other problems that arise from the Sin of Insufficiently Chatty Queries as well as their solutions later in this series.

That is all it takes to reduce query chattiness, but the observant penitent will notice that yet another sin is still being committed: the Sin of Too Many Columns. That will be the subject of the next lesson.

For an in-depth look at LINQ independent of its use for data access, please check out Jesse Liberty’s series, LINQ From Scratch