Nested Select Count(*) Sub Queries

Question

 

I've seen many LINQ to SQL queries that generate a query with a nested sub-query that is essentially a Select Count(*)

 

For example the following LINQ query...

 

Code Snippet

   var query = from subject in db.Subjects

                        orderby subject.Name

                        select new

                        {

                            subject.Name,

                            Books = from book in subject.Books

                                    where book.Price < 70

                                    select new { book.Title, book.Price }

                        };

 

 

 

Generates the following SQL

 

Code Snippet

exec sp_executesql N'SELECT [t0].[Name], [t1].[Title], [t1].[Price], (

    SELECT COUNT(*)

    FROM [dbo].[Book] AS [t2]

    WHERE ([t2].[Price] < @p0) AND ([t2].[Subject] = [t0].[ID])

    ) AS [value]

FROM [dbo].[Subject] AS [t0]

LEFT OUTER JOIN [dbo].[Book] AS [t1] ON ([t1].[Price] < @p0) AND ([t1].[Subject] = [t0].[ID])

ORDER BY [t0].[Name], [t0].[ID], [t1].[ID]',N'@p0 decimal(33,4)',@p0=20.0000

 

 

 

I can't see the purpose of the nested sub-query - but I suspect it maybe used as some sort of helper for the LINQ engine to know how many items in the Books collection to populate. Can somebody explain?

 

Thanks

 

Barry Gervin

www.ObjectSharp.com/Barry

 

Answer 1

The Count(*) sub query is there so the object materializer can simply read the data in order and construct the resulting object heirarchy.

 

Answer 2

Thanks Matt although I'm thinking that it's still not 100% necessary and maybe it's a little extra strain on the SQL Server. Is there a way to turn this off? I'm assuming this would cause a little extra overhead on the client side to iterate through the records to know exactly how many objects to materialize.

 

Barry Gervin

www.ObjectSharp.com/Barry