What is the Execution Location for the below Snippet?

Question

Explain the location of each operation i.e WHERE and TAKE. In Memory or on Database side for both IEnumerable and IQueryable.

IEnumerable<Student> listStudents = DBContext.Students.Where(x => x.Gender == "Male");
listStudents = listStudents.Take(2);

foreach (var std in listStudents)
{
    Console.WriteLine(std.FirstName + " " + std.LastName);
}

IQueryable<Student> listStudents = DBContext.Students
    .AsQueryable()
    .Where(x => x.Gender == "Male");
listStudents = listStudents.Take(2);

foreach (var std in listStudents)
{
    Console.WriteLine(std.FirstName + " " + std.LastName);
}

Answer 1

Assuming DBContext.Students is DbSet<Student> then for the first snippet EF should translate Where into SQL but Take will be performed on the client side when/if materialization will happen. The second snippet should result in both Where and Take translated into appropriate SQL. Note that actual difference would not be that big - both queries should not fetch extra data (since only Take(2) is "different" and you don't need to fetch more than 2 elements either way). But if you will rewrite the first snippet to something like:

IEnumerable<Student> listStudents = DBContext.Students;
listStudents = listStudents
    .Where(x => x.Gender == "Male")
    .Take(2);

Then it can potentially lead to extra data fetched to the client since the filtering will be done on the client side too (i.e. you will need to fetch data until 2 "Male"s are encountered and depending on the ordering used by the DB it potentially can return a lot of non-"Male"s).

All of this can be easily tested with EF Core by enabling logging which will output the executed queries and other info (also note the EntityFrameworkQueryableExtensions.ToQueryString(IQueryable) available since EF Core 5th version).

Read also:

• What is the difference between IQueryable<T> and IEnumerable<T>?
• EF Core docs: How Queries Work