LP on .NET

June 17, 2009

LINQ Queries the Easy Way

Filed under: .NET,LINQ,SQL Server — Larry Parker @ 1:14 pm

A colleague recently approached me about a LINQ query he wrote that was a bit challenging since it traversed about a half dozen tables in the database.  He had it working but it was very long (several dozen lines) and he was curious if there was a better way.

The query he wrote used the LINQ query syntax instead of the method syntax (sometimes called lambda syntax) .  Query syntax tends to be a bit verbose, while method syntax can be a bit cryptic at times (see here for a comparison of the two styles).  I can’t really say one is “better” than the other, and I find that I use both in many cases, sometimes within the same query.

But more importantly, he was doing joins the “old” way — i.e. his LINQ code looked very much like SQL code.  When I started writing LINQ queries, I took the same approach.  SQL was familiar to me so being able to write strongly-typed SQL code in LINQ was at least a step up.  But writing queries this way is not thinking in LINQ.

I will demonstrate a query that is along the lines of how we ended up translating his original query into something very simple that flowed very nicely.  (His new query ended up being four lines of code instead of the original several dozen.)

Using the ever awesome LINQPad utility as our development environment, and coding against the Northwind database, we have the following challenge:

Retrieve the customer ID and company name for all customers who have ever placed an order that contained a product in the Condiments category.

LINQPad queries execute within the context of the database context, so I won’t be prefixing anything with “db”, “dc”, “ctx”, etc.

The first step is to just get the customer ID and company name:

Customers.Select(o => new {o.CustomerID, o.CompanyName})

This returns 91 rows and generates the following SQL:

SELECT [t0].[CustomerID], [t0].[CompanyName]
FROM [Customers] AS [t0]

Next, we refine the query to only get customers who have placed an order:

Customers.Where(o => o.Orders.Any())
    .Select(o => new {o.CustomerID, o.CompanyName})

This returns 88 rows and generates the following SQL:

SELECT [t0].[CustomerID], [t0].[CompanyName]
FROM [Customers] AS [t0]
WHERE EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [Orders] AS [t1]
    WHERE [t1].[CustomerID] = [t0].[CustomerID]
    )

Notice how the LINQ Any operator was translated into a SQL “WHERE EXISTS”.

Now things get a bit more complicated because we only want orders that contain condiments, but the query still flows very nicely:

Customers.Where(o => o.Orders.Any(o2 => o2.OrderDetails
    .Any(o3 => o3.Product.Category.CategoryName == "Condiments")))
    .Select(o => new {o.CustomerID, o.CompanyName})

The final result set is 68 customers, with the SQL looking like this:

SELECT [t0].[CustomerID], [t0].[CompanyName]
FROM [Customers] AS [t0]
WHERE EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [Orders] AS [t1]
    WHERE (EXISTS(
        SELECT NULL AS [EMPTY]
        FROM [OrderDetails] AS [t2]
        INNER JOIN [Products] AS [t3] ON [t3].[ProductID] = [t2].[ProductID]
        LEFT OUTER JOIN [Categories] AS [t4] ON [t4].[CategoryID] = [t3].[CategoryID]
        WHERE ([t4].[CategoryName] = @p0) AND ([t2].[OrderID] = [t1].[OrderID])
        )) AND ([t1].[CustomerID] = [t0].[CustomerID])
    )

Note how LINQ generates the JOINs in the SQL for us.  There was no need to explicitly do a join in the LINQ code.  Instead, we used the navigation properties that are created for us (provided your database has foreign keys!).

So the final result was a fairly straightforward LINQ query using method syntax that got translated into some pretty nice SQL (pretty much what I would have written myself).

There are always many ways to write LINQ queries, and I recommend experimenting directly in LINQPad to see what makes the most sense for you.  You’ll find that as you start thinking more in LINQ, as opposed to SQL, that the queries become much simpler.

Hope this helps.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: