EF Core attaching all entities while adding query on existing query

Question

I have some complex scenarios, but I will show them here as simple. So, my context class is pretty simple:

public class AppDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("data source=DILSHODKPC;integrated security=True;Database=ODataTEst; MultipleActiveResultSets=true");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        var student = modelBuilder.Entity<Student>();
        student.HasKey(s => s.Id);
    }

    public IQueryable<T> GetEntities<T>() where T : class
    {
        return Set<T>();
    }
}

My model is simple too:

public class Student
{
    public Guid Id { get; set; }

    public string Name { get; set; }

    public int Score { get; set; }
}

I have 8 records on the database:

My problem is when I add a query to the existing entity framework is tracking all entities. Look at two scenarios:

Scenario 1:

AppDbContext appDbContext = new AppDbContext();
var query1 = appDbContext.GetEntities<Student>().Where(s => s.Score > 3);
var query2 = query1.Where(s => s.Name == "Messi");
var loadedEntitiesCount = query2.ToList().Count();//Count is 4. It is good
var trackedEntitiesCount = appDbContext.ChangeTracker.Entries<Student>().Select(e => e.Entity).Count();//Count is 4. It is good

In this scenario, everything is expected.

Scenario 2:

 static void Main(string[] args)
        {
            AppDbContext appDbContext = new AppDbContext();
            var query1 = GetFirstQuery(appDbContext);
            var query2 = query1.Where(s => s.Name == "Messi");
            var loadedEntitiesCount = query2.ToList().Count();//Count is 4. It is good
            var trackedEntitiesCount = appDbContext.ChangeTracker.Entries<Student>().Select(e => e.Entity).Count();//Count is 8. Confusing
        }

        static IEnumerable<Student> GetFirstQuery(AppDbContext appDbContext)
        {
            return appDbContext.GetEntities<Student>().Where(s => s.Score > 3);
        }

In this scenario, the count of tracked entities is 8. I was not expecting it

Scenario 3:

   AppDbContext appDbContext = new AppDbContext();
            var query1 = GetFirstQuery(appDbContext).AsQueryable();
            var query2 = query1.Where(s => s.Name == "Messi");
            var loadedEntitiesCount = query2.ToList().Count();//Count is 4. It is good
            var trackedEntitiesCount = appDbContext.ChangeTracker.Entries<Student>().Select(e => e.Entity).Count();//Count is 4. It is good

The count of tracked entities is 4.

The question is why ef tracking all entities in Scenario 2? Is it something expected? What are best practices to add query on existing query with different kinds of collections? (IEnumerable, IQueryable)

score 0 · Answer 1 · answered Dec 08 '21 at 11:47

0

Generally speaking, if you don't want objects to be tracked, you can call .AsNoTracking() on your query. Then the results of this query will not be tracked.

In your specific case:

static IEnumerable<Student> GetFirstQuery(AppDbContext appDbContext)

this means the query ends here, because you return an IEnumerable. The result will then be further filtered in memory of your application, so you get the correct result, but all data will have been queried from the database.

Use IQueryable<> if you want the query to continue and further filters or expressions to be transported to the database instead of being executed in the memory of your application:

static IQueryable<Student> GetFirstQuery(AppDbContext appDbContext)

answered Dec 08 '21 at 11:47

nvoigt

75,013
26
93
142

What are you mean by ' query ends here'? It will not be loaded in memory until I call ToList. GetFirstQuery return type is IEnumerable, but returning value 'instance' (actual) type is IQuerable, changing IEnumerable to IQueryable will not fit me – Dilshod K Dec 08 '21 at 11:58
To be honest, I cannot do a lot about what will fit you. I can tell you what the compiler *does*, which I just did. You even have the code that provides evidence of what I said. Once you make an IEnumerable out of the IQueryable through whatever means, you are operating in memory after that. – nvoigt Dec 08 '21 at 12:02
1

nvoigt is saying you enumerated the enumerable returned by GetFirstQuery, causing the first query `appDbContext.GetEntities().Where(s => s.Score > 3)` to run (and all 8 entities are matched by this where clause) in its entirety and then you locally filtered it to Messi with `.Where(s => s.Name == "Messi");`. – Caius Jard Dec 08 '21 at 12:02
Caius is correct. I am not talking about deferred execution, which, you are right, happens at your ToList call. I am talking about the fact that parts of your chain are handling IQueryable. Those parts are transformed into SQL and handled on the database. And parts are on IEnumerable, those happen in memory. You will need to keep it an IQueryable, as long as you want continuations of the chain to operate on the database as well. – nvoigt Dec 08 '21 at 12:05
2

In essence if you do this with GetFirstQuery returning IEnumerable it's like doing `appDbContext.GetEntities().Where(s => s.Score > 3).AsEnumerable().Where(s => s.Name == "Messi");` - the second Where enumerates the enumerable, triggering the first query to run; the two Where don't act in compound like they would ordinarily – Caius Jard Dec 08 '21 at 12:06

EF Core attaching all entities while adding query on existing query

1 Answers1