c# - LINQ 生成具有重复嵌套选择的 SQL

Question

我是 .NET Entity Framework 的新手，我认为它很棒，但不知何故我遇到了这个奇怪的问题(抱歉是西类牙语，但我的程序是用那种语言编写的，无论如何这没什么大不了的，只是列或属性名称):我正在执行正常的 LINQ To Entities 查询以获取 UltimaConsulta 列表，如下所示:

var query = from uc in bd.UltimasConsultas
            select uc;

顺便说一下，UltimasConsultas 是一种观点。问题是 LINQ 正在为查询生成此 SQL:

SELECT 
[Extent1].[IdPaciente] AS [IdPaciente], 
[Extent1].[Nombre] AS [Nombre], 
[Extent1].[PrimerApellido] AS [PrimerApellido], 
[Extent1].[SegundoApellido] AS [SegundoApellido], 
[Extent1].[Fecha] AS [Fecha]
FROM (SELECT 
      [UltimasConsultas].[IdPaciente] AS [IdPaciente], 
      [UltimasConsultas].[Nombre] AS [Nombre], 
      [UltimasConsultas].[PrimerApellido] AS [PrimerApellido], 
      [UltimasConsultas].[SegundoApellido] AS [SegundoApellido], 
      [UltimasConsultas].[Fecha] AS [Fecha]
      FROM [dbo].[UltimasConsultas] AS [UltimasConsultas]) AS [Extent1]

为什么 LINQ 会生成嵌套的 Select？我从视频和示例中想到，它会为此类查询生成正常的 SQL 选择。我是否必须配置一些东西(实体模型是从向导生成的，所以它是默认配置)？预先感谢您的回答。

Answer 1

需要明确的是，LINQ to Entities 不会生成 SQL。相反，它会生成一个 ADO.NET 规范命令树，而您的数据库的 ADO.NET 提供程序(在本例中可能是 SQL Server)会生成 SQL。

那么为什么它会生成这个派生表(我认为“派生表”是这里使用的 SQL 特性的更正确的术语)？因为生成 SQL 的代码必须为各种各样的 LINQ 查询生成 SQL，其中大多数都不像您显示的那样简单。这些查询通常会选择多种类型的数据(其中许多可能是匿名的，而不是命名类型)，并且为了保持 SQL 生成相对合理，它们被分组为每种类型的范围。

另一个问题:你为什么要关心？很容易证明，从性能的角度来看，此语句中派生表的使用是“免费”的。

我从填充的数据库中随机选择了一个表，并运行以下查询:

SELECT [AddressId]
      ,[Address1]
      ,[Address2]
      ,[City]
      ,[State]
      ,[ZIP]
      ,[ZIPExtension]
  FROM [VertexRM].[dbo].[Address]

让我们看看成本:

<StmtSimple StatementCompId="1" StatementEstRows="7900" StatementId="1" StatementOptmLevel="TRIVIAL" StatementSubTreeCost="0.123824" StatementText="/****** Script for SelectTopNRows command from SSMS  ******/&#xD;&#xA;SELECT [AddressId]&#xD;&#xA;      ,[Address1]&#xD;&#xA;      ,[Address2]&#xD;&#xA;      ,[City]&#xD;&#xA;      ,[State]&#xD;&#xA;      ,[ZIP]&#xD;&#xA;      ,[ZIPExtension]&#xD;&#xA;  FROM [VertexRM].[dbo].[Address]" StatementType="SELECT">
  <StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />
  <QueryPlan CachedPlanSize="9" CompileTime="0" CompileCPU="0" CompileMemory="64">
    <RelOp AvgRowSize="246" EstimateCPU="0.008847" EstimateIO="0.114977" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="7900" LogicalOp="Clustered Index Scan" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.123824">

现在让我们将其与派生表的查询进行比较:

SELECT 
       [Extent1].[AddressId]
      ,[Extent1].[Address1]
      ,[Extent1].[Address2]
      ,[Extent1].[City]
      ,[Extent1].[State]
      ,[Extent1].[ZIP]
      ,[Extent1].[ZIPExtension]
  FROM (SELECT [AddressId]
          ,[Address1]
          ,[Address2]
          ,[City]
          ,[State]
          ,[ZIP]
          ,[ZIPExtension]
  FROM[VertexRM].[dbo].[Address]) AS [Extent1]

成本:

<StmtSimple StatementCompId="1" StatementEstRows="7900" StatementId="1" StatementOptmLevel="TRIVIAL" StatementSubTreeCost="0.123824" StatementText="/****** Script for SelectTopNRows command from SSMS  ******/&#xD;&#xA;SELECT &#xD;&#xA;       [Extent1].[AddressId]&#xD;&#xA;      ,[Extent1].[Address1]&#xD;&#xA;      ,[Extent1].[Address2]&#xD;&#xA;      ,[Extent1].[City]&#xD;&#xA;      ,[Extent1].[State]&#xD;&#xA;      ,[Extent1].[ZIP]&#xD;&#xA;      ,[Extent1].[ZIPExtension]&#xD;&#xA;  FROM (SELECT [AddressId]&#xD;&#xA;          ,[Address1]&#xD;&#xA;          ,[Address2]&#xD;&#xA;          ,[City]&#xD;&#xA;          ,[State]&#xD;&#xA;          ,[ZIP]&#xD;&#xA;          ,[ZIPExtension]&#xD;&#xA;  FROM[VertexRM].[dbo].[Address]) AS [Extent1]" StatementType="SELECT">
  <StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />
  <QueryPlan CachedPlanSize="9" CompileTime="0" CompileCPU="0" CompileMemory="64">
    <RelOp AvgRowSize="246" EstimateCPU="0.008847" EstimateIO="0.114977" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="7900" LogicalOp="Clustered Index Scan" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.123824">

在这两种情况下，SQL Server 都简单地扫描聚集索引。毫不奇怪，成本几乎完全相同。

让我们来看一个稍微更复杂的查询。我启动了 LINQPad，并针对同一张表以及一个相关表输入了以下查询:

from a in Addresses
select new
{
    Id = a.Id,
    Address1 = a.Address1,
    Address2 = a.Address2,
    City = a.City,
    State = a.State,
    ZIP = a.ZIP,
    ZIPExtension = a.ZIPExtension,
    PersonCount = a.EntityAddresses.Count()
}

这会生成以下 SQL:

SELECT 
1 AS [C1], 
[Project1].[AddressId] AS [AddressId], 
[Project1].[Address1] AS [Address1], 
[Project1].[Address2] AS [Address2], 
[Project1].[City] AS [City], 
[Project1].[State] AS [State], 
[Project1].[ZIP] AS [ZIP], 
[Project1].[ZIPExtension] AS [ZIPExtension], 
[Project1].[C1] AS [C2]
FROM ( SELECT 
    [Extent1].[AddressId] AS [AddressId], 
    [Extent1].[Address1] AS [Address1], 
    [Extent1].[Address2] AS [Address2], 
    [Extent1].[City] AS [City], 
    [Extent1].[State] AS [State], 
    [Extent1].[ZIP] AS [ZIP], 
    [Extent1].[ZIPExtension] AS [ZIPExtension], 
    (SELECT 
        COUNT(cast(1 as bit)) AS [A1]
        FROM [dbo].[EntityAddress] AS [Extent2]
        WHERE [Extent1].[AddressId] = [Extent2].[AddressId]) AS [C1]
    FROM [dbo].[Address] AS [Extent1]
)  AS [Project1]

分析这个，我们可以看到 Project1 是匿名类型的投影。 Extent1 是 Address 表/实体。 Extent2 是关联表。现在 Address 没有派生表，但是有一个用于投影。

不知道大家有没有写过SQL生成系统，其实并不容易。我相信证明 LINQ to Entities 查询和 SQL 查询等效的一般问题是 NP 难问题，尽管某些特定情况显然要容易得多。 SQL 是有意设计为图灵不完备的，因为它的设计者希望所有 SQL 查询都在限定时间内执行。 LINQ，不是这样。

简而言之，这是一个很难解决的问题， Entity Framework 及其提供者的组合偶尔会牺牲一些可读性以支持广泛查询的一致性。但这不应该是性能问题。