Why did the following linq to sql query generate a subquery?

Question

I did the following query:

var list = from book in books
          where book.price > 50
          select book;

list = list.Take(50);

I would expect the above to generate something like:

SELECT top 50 id, title, price, author
FROM Books
WHERE price > 50

but it generates:

SELECT
[Limit1].[C1] as [C1]
[Limit1].[id] as [Id], 
[Limit1].[title] as [title], 
[Limit1].[price] as [price], 
[Limit1].[author]
FROM (SELECT TOP (50) 
             [Extent1].[id] as as [Id], 
             [Extent1].[title] as [title], 
             [Extent1].[price] as [price], 
             [Extent1].[author] as [author]
      FROM Books as [Extent1]
      WHERE [Extent1].[price] > 50
     ) AS [Limit1]

Why does the above linq query generate a subquery and where does the C1 come from?

Answer 1

The subquery is generated for projection purposes, it makes more sense when you select from multiple tables into a single anonymous object, then the outer query is used to gather the results.

Try what happens with something like this:

from book in books
where price > 50
select new 
{
  Title = book.title,
  Chapters = from chapter in book.Chapters
             select chapter.Title
}

Answer 2

Disclaimer: I've never used LINQ before...

My guess would be paging support? I guess you have some sort of Take(50, 50) method that gets 50 records, starting at record 50. Take a look at the SQL that query generates and you will probably find that it uses a similar sub query structure to allow it to return any 50 rows in a query in approximately the amount of time that it returns the first 50 rows.

In any case, the nested sub query doesn't add any performance overhead as it's automagically optimised away during compilation of the execution plan.

Answer 3

Isn't it a case of the first query returning the total number of rows while the second extracts the subset of rows based on the call to the .Take() method?

Answer 4

You could still make it cleaner like this:

var c = (from co in db.countries
                    where co.regionID == 5
                    select co).Take(50);

This will result in:

Table(country).Where(co => (co.regionID = Convert(5))).Take(50)

Equivalent to:

SELECT TOP (50) [t0].[countryID], [t0].[regionID], [t0].[countryName], [t0].[code]
FROM [dbo].[countries] AS [t0]
WHERE [t0].[regionID] = 5

EDIT: Comments, Its Not necessarily because with separate Take(), you can still use it like this:

var c = (from co in db.countries
                     where co.regionID == 5
                     select co);
            var l = c.Take(50).ToList();

And the Result would be the same as before.

SELECT TOP (50) [t0].[countryID], [t0].[regionID], [t0].[countryName], [t0].[code]
FROM [dbo].[countries] AS [t0]
WHERE [t0].[regionID] = @p0

The fact that you wrote IQueryable = IQueryable.Take(50) is the tricky part here.

Answer 5

1. I agree with @Justin Swartsel. There was no error involved, so this is largely an academic matter.
2. Linq-to-SQL endeavors to generate SQL that runs efficiently (which it did in your case).
   1. But it does not make any effort to generate conventional SQL that a human would likely create.
3. The Linq-to-SQL implementers likely used the builder pattern to generate the SQL.
   
   1. If so, it would be easier to append a substring (or a subquery in this case) than it would be to backtrack and insert a 'TOP x' fragment into the SELECT clause.