理解 LINQ to SQL 中的 .AsEnumerable() [英] Understanding .AsEnumerable() in LINQ to SQL

问题描述

给定以下 LINQ to SQL 查询:

Given the following LINQ to SQL query:

var test = from i in Imports
           where i.IsActive
           select i;

被解释的SQL语句是:

The interpreted SQL statement is:

SELECT [t0].[id] AS [Id] .... FROM [Imports] AS [t0] WHERE [t0].[isActive] = 1

假设我想在 select 中执行一些无法转换为 SQL 的操作.我的理解是，完成此操作的常规方法是执行 AsEnumerable() 从而将其转换为可工作的对象.

Say I wanted to perform some action in the select that cannot be converted to SQL. Its my understanding that the conventional way to accomplish this is to do AsEnumerable() thus converting it to a workable object.

鉴于此更新的代码:

var test = from i in Imports.AsEnumerable()
           where i.IsActive
           select new 
           { 
               // Make some method call 
           };

和更新的 SQL:

SELECT [t0].[id] AS [Id] ... FROM [Imports] AS [t0] 

注意在执行的 SQL 语句中缺少 where 子句.

Notice the lack of a where clause in the executed SQL statement.

这是否意味着整个导入"表都缓存到内存中?如果表包含大量记录，这会降低性能吗?

Does this mean the entire "Imports" table is cached into memory? Would this slow performance at all if the table contained a large amount of records?

帮助我了解这里幕后实际发生的事情.

Help me to understand what is actually happening behind the scenes here.

推荐答案

AsEnumerable 是

AsEnumerable(TSource)(IEnumerable(TSource))可用于在查询之间进行选择序列时的实现实现 IEnumerable(T) 但也有一组不同的公共查询可用的方法

AsEnumerable(TSource)(IEnumerable(TSource)) can be used to choose between query implementations when a sequence implements IEnumerable(T) but also has a different set of public query methods available

因此，当您之前调用 Where 方法时，您调用的是与 IEnumerable.Where 不同的 Where 方法.Where 语句用于 LINQ 转换为 SQL，新的 Where 是 IEnumerable 一个接受 IEnumerable, 枚举它并产生匹配的项目.这解释了为什么您会看到正在生成不同的 SQL.在将 Where 扩展应用到您的第二版代码之前，将从数据库中完整获取该表.这可能会造成严重的瓶颈，因为整个表必须在内存中，或者更糟的是整个表必须在服务器之间移动.允许 SQL 服务器执行 Where 并做它最擅长的事情.

So when you were calling the Where method before, you were calling a different Where method from the IEnumerable.Where. That Where statement was for LINQ to convert to SQL, the new Where is the IEnumerable one that takes an IEnumerable, enumerates it and yields the matching items. Which explains why you see the different SQL being generated. The table will be taken in full from the database before the Where extension will be applied in your second version of the code. This could create a serious bottle neck, because the entire table has to be in memory, or worse the entire table would have to travel between servers. Allow SQL server to execute the Where and do what it does best.

这篇关于理解 LINQ to SQL 中的 .AsEnumerable()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！