Suppose we have classes

public class Forest { public int Id { get; set; } public virtual ICollection<TreeType> TreeTypes { get; set; } = new List<TreeType>(); } public class TreeType { public int Id { get; set; } public string Name { get; set; } public virtual SomeLinkedData SomeLinkedData { get; set; } public int ForestId { get; set; } public virtual Forest Forest { get; set; } } public class ApplicationDbContext : IdentityDbContext { public DbSet<TreeType> TreeTypes { get; set; } public DbSet<Forest> Forests { get; set; } }

And I need to optimize the query. I can do this in db.TreeTypes.Where(x=>x.Forest.Id == forest.Id).Count() in this query, I have only one query to the database and it will immediately return a number

And I can do so forest.TreeTypes.Count()

And I cannot understand how it will work in the second case, whether it will receive all types of trees first and then calculate their number, as a result, the load on the base will be greater than the first request and there will be data that can not be processed at all. handle. Or, in the second case, it also optimizes the query. As I understand the optimization in the second case will not. And if it does not, then why, it's so much extra resources to spend.

And in the continuation of the issue. I can do db.Forests.Include("TreeTypes.SomeLinkedData") and how this can be done through forest.TreeTypes there is no such function Include there is some kind of trimmed functionality in the navigation properties.

At the moment I am doing all such requests through db. but this is not particularly convenient, and the db object is not available in any context, I have to pass it, which I also do not particularly like when it would be possible to simply refer to the navigation property of the object.

  • public virtual ICollection<TreeType> { get; set; } = new List<TreeType>(); and unless that should not be a property name? - Bald
  • @Bald yes, I apologize)), correcting - Dmitry Polyanin

2 answers 2

The call to forest.TreeTypes.Count() consists of two parts. First, forest.TreeTypes is called - and at this moment (if LazyLoading is enabled), all related records are loaded from the database. By the time data is loaded from the database, the EF library does not yet know that all you need is Count() . You did quite right, finding such a code suspicious.

Determining whether a query will be executed on the database side or on the application side is usually very simple. On the database side, requests to collections made through the IQueryable<> interface will be executed. DbSet this interface, but ICollection<> does not contain it.

EF has a special syntax for turning navigation properties into queries. Perhaps you should use it:

 db.Entry(forest).Collection(f => f.TreeTypes).Query().Count();

Downloading SomeLinkedData at the same time as TreeTypes also quite simple:

 db.Entry(forest).Collection(f => f.TreeTypes).Query().Include("SomeLinkedData").Load(); foreach (var tt in forest.TreeTypes) { // Этот цикл больше не вызовет загрузки коллекции - она уже загружена Console.WriteLine(tt.SomeLinkedData.Foo); // И SomeLinkedData уже загружены тоже }
  • I thought so, thanks for clarifying my doubts about this. The only thing that seems to me here is not a logical one, that it is impossible to get a copy of the current db from the object itself, I will have to pass it every time. But in theory, the proxy forest.TreeTypes somewhere already contains information about db pity that there is no syntax for extracting it. - Dmitry Polyanin
  1. When developing code using the EF library, you need to understand that most LINQ functions are not actually called, in place of this EF builds a tree of expressions, which is used to dynamically form an SQL query. Therefore, db.TreeTypes.Where(x=>x.Forest.Id == forest.Id).Count() and forest.TreeTypes.Count() should theoretically lead to the formation of identical SQL queries to the database. What actual requests will be sent to the database server can be viewed using the SQL Server Profiler utility, which can be found, for example, as part of the MS SQL Server Developer Edition distributor.
  2. The Include method is defined only for the DbSet<> , and cannot be called for a simple collection. And one more thing, Include is a kind of directive that instructs EF that it is necessary to build a SQL query that loads additional related entities. And all this can happen only within the framework of this initial request. An EA cannot dynamically load data on related entities after these entities have already been selected from the database.
  • db.TreeTypes.Where(x=>x.Forest.Id == forest.Id).Count() and forest.TreeTypes.Count() should not form identical queries either in theory or in practice! - Pavel Mayorov
  • Yes, I apologize, as Pavel correctly noted, really when calling forest.TreeTypes.Count() the IEnumerable<T>.Count() method will be called because forest.TreeTypes is of type ICollection<> to which the IEnumerable <T> method is already applied. Count () - Sergey Limonov