Why does this additional join increase # of queries?

Question

I'm having trouble coming up with an efficient LINQ-to-SQL query. I am attempting to do something like this:

from x in Items
select new
{
    Name = x.Name
    TypeARelated = from r in x.Related
                   where r.Type == "A"
                   select r
}

As you might expect, it produces a single query from the "Items" table, with a left join on the "Related" table. Now if I add another few similar lines...

from x in Items
select new
{
    Name = x.Name
    TypeARelated = from r in x.Related
                   where r.Type == "A"
                   select r,
    TypeBRelated = from r in x.Related
                   where r.Type == "B"
                   select r
}

The result is that a similar query to the first attempt is run, followed by an individual query to the "Related" table for each record in "Items". Is there a way to wrap this all up in a single query? What would be the cause of this? Thanks in advance for any help you can provide.

score 3 · Accepted Answer · answered May 25 '11 at 21:30

The above query if written directly in SQL would be written like so (pseudo-code):

SELECT 
    X.NAME AS NAME,
    (CASE R.TYPE WHEN A THEN R ELSE NULL) AS TypeARelated,
    (CASE R.TYPE WHEN B THEN R ELSE NULL) AS TypeBRelated
FROM Items AS X
JOIN Related AS R ON <some field>

However, linq-to-sql is not as efficient, from your explanation, it does one join, then goes to individually compare each record. A better way would be to use two linq queries similar to your first example, which would generate two SQL queries. Then use the result of the two linq queries and join them, which would not generate any SQL statement. This method would limit the number of queries executed in SQL to 2.

If the number of conditions i.e. r.Type == "A" etc., are going to increase over time, or different conditions are going to be added, you're better off using a stored procedure, which would be one SQL query at all times.

Hasanain

score 1 · Answer 2 · answered May 25 '11 at 22:50

You can use eager loading to do a single join on the server to see if that helps. Give this a try.

using (MyDataContext context = new MyDataContext())
{
    DataLoadOptions options = new DataLoadOptions();
    options.LoadWith<Item>(i => i.Related);
    context.LoadOptions = options;

    // Do your query now.
}

Why does this additional join increase # of queries?

2 Answers2

Linked