CODEWITHSUNDEEP
c#entity-frameworklinqentity-framework-core
Vijay
Vijay

Reputation: 825

IQueryable vs IEnumerable - how many DB calls?

I know how IQueryable and IEnumerable work. But today while I was revisiting those topics with an example.

Below are my questions

How many DB calls are made?

If I'm correct there are 2 DB calls, one with the where clause and the other when Take(1) is used.

    public void GetEmployeesByDept(long deptId)
    {
        IQueryable<EmployeeDetails> empDetails = _context.EmployeeDetails.Where(x => x.Idseq == deptId);

        // First DB call
        var firstEmployee = empDetails.Take(1);

        // Second DB call
        Console.WriteLine(empDetails.GetType());
    }

This is my explanation - but when I hovered over the empDetails, I could see the expression contains two arguments

  1. With the table

  2. $x.Idseq == .Constant<TestProject.Program+<>c__DisplayClass2_0>(TestProject.Program+<>c__DisplayClass2_0).deptId

So,now when the execution finished the firstEmployee, I hovered over the firstEmployee variable, I could see the expression as below

Is this a DB call as well?

.Call System.Linq.Queryable.Take(
    .Call System.Linq.Queryable.Where(
        .Constant<Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[Employee.Models.EmployeeDetails]>(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[Employee.Models.EmployeeDetails]),
        '(.Lambda #Lambda1<System.Func`2[Employee.Models.EmployeeDetails,System.Boolean]>)),
    1).Lambda #Lambda1<System.Func`2[Employee.Models.EmployeeDetails,System.Boolean]>(Employee.Models.EmployeeDetails $x)
    {
        $x.Idseq == .Constant<TestProject.Program+<>c__DisplayClass2_0>(TestProject.Program+<>c__DisplayClass2_0).deptId
    }

If this is the only DB call made among the two, then how come I am able to load the data for empDetails?

Question #2: now, I changed the type from IQueryable to IEnumerable.

My understanding here is DB call is made with where clause and then loads the data in-memory and then takes the first element.

Is that true?

public void GetEmployeesByDept(long deptId)
{
    IEnumerable<EmployeeDetails> empDetails = _context.EmployeeDetails.Where(x => x.Idseq == deptId);
    // First DB call

    var firstEmployee = empDetails.Take(1); // in-memory object
    Console.WriteLine(empDetails.GetType());
}

Could anyone please correct me if my understanding is incorrect.

Thanks in advance

Upvotes: 1

Views: 591

Answers (1)

Athanasios Kataras
Athanasios Kataras

Reputation: 26450

Check the Query execution documentation

After a LINQ query is created by a user, it is converted to a command tree. A command tree is a representation of a query that is compatible with the Entity Framework. The command tree is then executed against the data source. At query execution time, all query expressions (that is, all components of the query) are evaluated, including those expressions that are used in result materialization.

After a LINQ query is created by a user

You are creating the query in the lines below

    IQueryable<EmployeeDetails> empDetails = _context.EmployeeDetails.Where(x => x.Idseq == deptId);
    // first DB call

    var firstEmployee = empDetails.Take(1);

LINQ queries are always executed when the query variable is iterated over, not when the query variable is created.

So only one call is made, when you access the firstEmployee variable.

Second part explanation

IQueryable is also IEnumerable. So this line:

IEnumerable<EmployeeDetails> empDetails = _context.EmployeeDetails.Where(x => x.Idseq ==deptId);

Is not casting to IEnumerable. The empDetails remains IQueryable and will be executed again, when you access the firstEmployee variable.

Is is optimised by executing the query, only when it is needed.

Upvotes: 2

Related Questions