英文:
IQueryable vs IEnumerable - how many DB calls?
问题
IQueryable 和 IEnumerable 的工作原理我了解。但今天我在一个示例中重新学习这些主题时,有以下问题。
有多少次数据库调用?
如果我没记错的话,有 2 次数据库调用,一次是在 where
子句使用时,另一次是在使用 Take(1)
时。
这是我的解释 - 但当我悬停在 empDetails
上时,我发现表达式包含两个参数:
-
With the table
-
$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.EntityQueryable
1[Employee.Models.EmployeeDetails]>(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable1[Employee.Models.EmployeeDetails]), '(.Lambda #Lambda1<System.Func
2[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<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
-
With the table
-
$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
答案1
得分: 2
Check the 查询执行文档
> 当用户创建了一个 LINQ 查询,它会被转换成一个命令树。命令树是一个与 Entity Framework 兼容的查询表示。然后,命令树会在数据源上执行。在查询执行时,所有查询表达式(即查询的所有组件)都会被评估,包括用于结果材料化的表达式。
当用户创建了一个 LINQ 查询
您在下面的代码行中创建了查询
IQueryable<EmployeeDetails> empDetails = _context.EmployeeDetails.Where(x => x.Idseq == deptId);
// 第一个数据库调用
var firstEmployee = empDetails.Take(1);
> 当查询变量被迭代时,LINQ 查询始终会被执行,而不是在查询变量创建时执行。
因此,只有在访问 firstEmployee 变量时才会进行一次调用。
第二部分解释
IQueryable 也是 IEnumerable。所以这一行代码:
IEnumerable<EmployeeDetails> empDetails = _context.EmployeeDetails.Where(x => 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<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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论