IQueryable vs IEnumerable – how many DB calls?

huangapple go评论74阅读模式
英文:

IQueryable vs IEnumerable - how many DB calls?

问题

IQueryable 和 IEnumerable 的工作原理我了解。但今天我在一个示例中重新学习这些主题时,有以下问题。

有多少次数据库调用?

如果我没记错的话,有 2 次数据库调用,一次是在 where 子句使用时,另一次是在使用 Take(1) 时。

这是我的解释 - 但当我悬停在 empDetails 上时,我发现表达式包含两个参数:

  1. With the table

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

所以,现在当执行完成 firstEmployee 后,当我悬停在 firstEmployee 变量上时,我可以看到以下表达式。

这也算是一个数据库调用吗?

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

如果这是两次数据库调用中唯一的一次,那么我是如何加载 empDetails 的数据的呢?

问题 #2:现在,我将类型从 IQueryable 更改为 IEnumerable

我理解在 where 子句中会进行一次数据库调用,然后加载数据到内存中,然后取第一个元素。这样理解对吗?

请在需要时纠正我。

谢谢!

英文:

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&lt;EmployeeDetails&gt; empDetails = _context.EmployeeDetails.Where(x =&gt; 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&lt;TestProject.Program+&lt;&gt;c__DisplayClass2_0&gt;(TestProject.Program+&lt;&gt;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&lt;Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[Employee.Models.EmployeeDetails]&gt;(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[Employee.Models.EmployeeDetails]),
        &#39;(.Lambda #Lambda1&lt;System.Func`2[Employee.Models.EmployeeDetails,System.Boolean]&gt;)),
    1).Lambda #Lambda1&lt;System.Func`2[Employee.Models.EmployeeDetails,System.Boolean]&gt;(Employee.Models.EmployeeDetails $x)
	{
		$x.Idseq == .Constant&lt;TestProject.Program+&lt;&gt;c__DisplayClass2_0&gt;(TestProject.Program+&lt;&gt;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&lt;EmployeeDetails&gt; empDetails = _context.EmployeeDetails.Where(x =&gt; 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

答案1

得分: 2

Check the 查询执行文档

> 当用户创建了一个 LINQ 查询,它会被转换成一个命令树。命令树是一个与 Entity Framework 兼容的查询表示。然后,命令树会在数据源上执行。在查询执行时,所有查询表达式(即查询的所有组件)都会被评估,包括用于结果材料化的表达式。

当用户创建了一个 LINQ 查询

您在下面的代码行中创建了查询

    IQueryable&lt;EmployeeDetails&gt; empDetails = _context.EmployeeDetails.Where(x =&gt; x.Idseq == deptId);
    // 第一个数据库调用

    var firstEmployee = empDetails.Take(1);

> 当查询变量被迭代时,LINQ 查询始终会被执行,而不是在查询变量创建时执行。

因此,只有在访问 firstEmployee 变量时才会进行一次调用。

第二部分解释

IQueryable 也是 IEnumerable。所以这一行代码:

IEnumerable&lt;EmployeeDetails&gt; empDetails = _context.EmployeeDetails.Where(x =&gt; x.Idseq ==deptId);

不是将其转换为 IEnumerable。empDetails 仍然是 IQueryable,只有在访问 firstEmployee 变量时才会再次执行。

通过仅在需要时执行查询,可以进行优化。

英文:

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&lt;EmployeeDetails&gt; empDetails = _context.EmployeeDetails.Where(x =&gt; 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&lt;EmployeeDetails&gt; empDetails = _context.EmployeeDetails.Where(x =&gt; 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.

huangapple
  • 本文由 发表于 2020年1月6日 15:29:45
  • 转载请务必保留本文链接:https://go.coder-hub.com/59608219.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定