Does LINQ with a scalar result trigger the lazy loading

Question

I read the Loading Related Entities post by the Entity Framework team and got a bit confused by the last paragraph:

Sometimes it is useful to know how many entities are related to another entity in the database without actually incurring the cost of loading all those entities. The Query method with the LINQ Count method can be used to do this. For example:

using (var context = new BloggingContext())
{
    var blog = context.Blogs.Find(1);

    // Count how many posts the blog has 
    var postCount = context.Entry(blog)
                          .Collection(b => b.Posts)
                          .Query()
                          .Count();
}

Why do the Query + Count method needed here?
Can't we simple use the LINQ's COUNT method instead?

var blog = context.Blogs.Find(1);
var postCount = blog.Posts.Count();

Will that trigger the lazy loading and all the collection will be loaded to the memory and just than I'll get my desired scalar value?

Answer 1

You will get your desired scalar value in bot cases. But consider the difference in what's happening.

With .Query().Count() you run a query on the database of the form SELECT COUNT(*) FROM Posts and assign that value to your integer variable.

With .Posts.Count, you run (something like) SELECT * FROM Posts on the database (much more expensive already). Each row of the result is then mapped field-by-field into your C# object type as the collection is enumerated to find your count. By asking for the count in this way, you are forcing all of the data to be loaded so that C# can count how much there is.

Hopefully it's obvious that asking the database for the count of rows (without actually returning all of those rows) is much more efficient!

Answer 2

The first method is not loading all rows since the Count method is invoked from an IQueryable but the second method is loading all rows since it is invoked from an ICollection.

I did some testings to verify it. I tested it with Table1 and Table2 which Table1 has the PK "Id" and Table2 has the FK "Id1" (1:N). I used EF profiler from here http://efprof.com/.

First method:

var t1 = context.Table1.Find(1);

var count1 = context.Entry(t1)
                        .Collection(t => t.Table2)
                        .Query()
                        .Count();

No Select * From Table2:

SELECT TOP (2) [Extent1].[Id] AS [Id]
FROM   [dbo].[Table1] AS [Extent1]
WHERE  [Extent1].[Id] = 1 /* @p0 */

SELECT [GroupBy1].[A1] AS [C1]
FROM   (SELECT COUNT(1) AS [A1]
        FROM   [dbo].[Table2] AS [Extent1]
        WHERE  [Extent1].[Id1] = 1 /* @EntityKeyValue1 */) AS [GroupBy1]

Second method:

var t1 = context.Table1.Find(1);
var count2 = t1.Table2.Count(); 

Table2 is loaded into memory:

SELECT TOP (2) [Extent1].[Id] AS [Id]
FROM   [dbo].[Table1] AS [Extent1]
WHERE  [Extent1].[Id] = 1 /* @p0 */

SELECT [Extent1].[Id]  AS [Id],
       [Extent1].[Id1] AS [Id1]
FROM   [dbo].[Table2] AS [Extent1]
WHERE  [Extent1].[Id1] = 1 /* @EntityKeyValue1 */

Why is this happening?

The result of Collection(t => t.Table2) is a class that implements ICollection but it is not loading all rows and has a property named IsLoaded. The result of the Query method is an IQueryable and this allows calling Count without preloading rows.

The result of t1.Table2 is an ICollection and it is loading all rows to get the count. By the way, even if you use only t1.Table2 without asking for the count, rows are loaded into memory.