Does LINQ with a scalar result trigger lazy loading

Question

I read the message Loading similar objects with the Entity Framework command and messed up a bit with the last paragraph:

Sometimes it’s useful to know how many objects are associated with another entity in the database, without actually having to download all of these objects. To do this, you can use the Query method with the LINQ Count method. For instance:

 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 is the Query + Count method needed?
Can't we just use the LINQ Count method?

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

Will this trigger lazy loading and the entire collection will be loaded into memory, and only I will get the desired scalar value?

Answer 1

The first method does not load all rows, since the Count method is called from IQueryable , but the second method loads all rows, since it is called from ICollection .

I checked a few checks. I tested it with table 1 and table 2, table 1 of which has PK identifier, and table 2 has FK "Id1" (1: N). I used the 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(); 

Table 2 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 does not load all the rows and has a property called IsLoaded . The result of the Query method is IQueryable , and this allows you to call Count without first loading the rows.

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

Answer 2

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

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

With .Posts.Count you run (something like) SELECT * FROM Posts in a database (much more expensive already). Each row of the result is then displayed field by field in your C # object type, as the collection is counted to find your score. By requesting a counter this way, you force all the data to load so that C # can count how much there is.

Hopefully this is obvious that querying the database to count the rows (without actually returning all these rows) is much more efficient!

Answer 3

The first solution does not cause lazy loading, because it will most likely never directly access the collection property. The Collection method accepts Expression , not just the delegate. It is used only to get the name of the property, which is used to access the mapping information and build the correct query.

Even if he gains access to the collection property, he can use the same strategy as other internal parts of EF (for example, checking), which temporarily disables lazy loading before accessing navigation properties to avoid unexpected lazy loading.

Btw. this is a significant improvement, unlike the ObjectContext API, where you need to build a request for access to the navigation property and, therefore, it can cause lazy loading.

There is another difference between the two approaches:

• The first always queries the database and returns the number of items in the database
• The second only queries the database once to load all the elements, and then returns the number of elements in the application without checking the status in the database

As a third rather interesting option, you can use the extra download. Arthur Vickers' implementation shows how to use the navigation property to retrieve an invoice from a database without lazy loading elements.