CODEWITHSUNDEEP
c#.netlinqentity-frameworklazy-loading
gdoron
gdoron

Reputation: 150253

Does LINQ with a scalar result trigger the lazy loading

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?

Upvotes: 7

Views: 1115

Answers (3)

Amiram Korach
Amiram Korach

Reputation: 13286

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.

Upvotes: 3

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364269

The first solution doesn't trigger the lazy loading because it most probably never access the collection property directly. The Collection method accepts Expression, not just delegate. It is used only to get the name of the property which is than used to access mapping information and build correct query.

Even if it would access the collection property it could use the same strategy as other internal parts of EF (for example validation) which turns off lazy loading temporarily before accessing navigation properties to avoid unexpected lazy loading.

Btw. this is a huge improvement in contrast to ObjectContext API where building query required accessing the navigation property and thus it could trigger lazy loading.

There is one more difference between those two approaches:

  • The first always executes query to database and returns count of items in the database
  • The second executes query to database only once to load all items and then returns counts of items in the application without checking state in the database

As the third quite interesting option you can use extra loading. The implementation by Arthur Vickers shows how to use navigation property to get count from the database without lazy loading items.

Upvotes: 1

Dan Puzey
Dan Puzey

Reputation: 34198

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!

Upvotes: 6

Related Questions