c# - EF Linq to Entities 在实体集上调用 ToList() 生成包含多个左外连接的 SQL 命令

Question

我的实体 “Progetto” 映射名称为 VW_AMY_PRG_WCS_Lookup 的 View

Progetto 有五个导航属性:ClienteDiFatturazione、ClienteDiLavorazione、PercentualeSuccesso、具有多重性 0..1 的 Agente 和具有多重性的 DocumentiWcs *

当我在 LINQPad 中运行这个简单语句时

var prj = Progetti.AsQueryable(); 
prj.ToList();

生成的sql是

SELECT 
    [Extent1].[IdProgetto] AS [IdProgetto], 
    [Extent1].[IdSerie_Progetto] AS [IdSerie_Progetto], 
    [Extent1].[Importo] AS [Importo], 
    [Extent1].[Data_Prevista_Chiusura] AS [Data_Prevista_Chiusura], 
    [Extent1].[IdStato] AS [IdStato], 
    [Extent1].[Oggetto] AS [Oggetto], 
    [Extent1].[IdMezzo_Pervenuto] AS [IdMezzo_Pervenuto], 
    [Extent1].[IdAgente] AS [IdAgente], 
    [Extent1].[Fido_Residuo] AS [Fido_Residuo], 
    [Extent2].[IdAnagrafica_Fatturazione] AS [IdAnagrafica_Fatturazione], 
    [Extent3].[IdAnagrafica_Lavorazione] AS [IdAnagrafica_Lavorazione], 
    [Extent4].[IdPercentuale_Successo] AS [IdPercentuale_Successo]
    FROM    (SELECT 
    [VW_AMY_PRG_WCS_Lookup].[IdProgetto] AS [IdProgetto], 
    [VW_AMY_PRG_WCS_Lookup].[IdSerie_Progetto] AS [IdSerie_Progetto], 
    [VW_AMY_PRG_WCS_Lookup].[Importo] AS [Importo], 
    [VW_AMY_PRG_WCS_Lookup].[IdPercentuale_Successo] AS [IdPercentuale_Successo], 
    [VW_AMY_PRG_WCS_Lookup].[Data_Prevista_Chiusura] AS [Data_Prevista_Chiusura], 
    [VW_AMY_PRG_WCS_Lookup].[IdAnagrafica_Lavorazione] AS [IdAnagrafica_Lavorazione], 
    [VW_AMY_PRG_WCS_Lookup].[IdAnagrafica_Fatturazione] AS [IdAnagrafica_Fatturazione], 
    [VW_AMY_PRG_WCS_Lookup].[IdMezzo_Pervenuto] AS [IdMezzo_Pervenuto], 
    [VW_AMY_PRG_WCS_Lookup].[IdStato] AS [IdStato], 
    [VW_AMY_PRG_WCS_Lookup].[Oggetto] AS [Oggetto], 
    [VW_AMY_PRG_WCS_Lookup].[IdAgente] AS [IdAgente], 
    [VW_AMY_PRG_WCS_Lookup].[Fido_Residuo] AS [Fido_Residuo]
    FROM [dbo].[VW_AMY_PRG_WCS_Lookup] AS [VW_AMY_PRG_WCS_Lookup]) AS [Extent1]
    LEFT OUTER JOIN (SELECT 
    [VW_AMY_PRG_WCS_Lookup].[IdProgetto] AS [IdProgetto], 
    [VW_AMY_PRG_WCS_Lookup].[IdSerie_Progetto] AS [IdSerie_Progetto], 
    [VW_AMY_PRG_WCS_Lookup].[Importo] AS [Importo], 
    [VW_AMY_PRG_WCS_Lookup].[IdPercentuale_Successo] AS [IdPercentuale_Successo], 
    [VW_AMY_PRG_WCS_Lookup].[Data_Prevista_Chiusura] AS [Data_Prevista_Chiusura], 
    [VW_AMY_PRG_WCS_Lookup].[IdAnagrafica_Lavorazione] AS [IdAnagrafica_Lavorazione], 
    [VW_AMY_PRG_WCS_Lookup].[IdAnagrafica_Fatturazione] AS [IdAnagrafica_Fatturazione], 
    [VW_AMY_PRG_WCS_Lookup].[IdMezzo_Pervenuto] AS [IdMezzo_Pervenuto], 
    [VW_AMY_PRG_WCS_Lookup].[IdStato] AS [IdStato], 
    [VW_AMY_PRG_WCS_Lookup].[Oggetto] AS [Oggetto], 
    [VW_AMY_PRG_WCS_Lookup].[IdAgente] AS [IdAgente], 
    [VW_AMY_PRG_WCS_Lookup].[Fido_Residuo] AS [Fido_Residuo]
    FROM [dbo].[VW_AMY_PRG_WCS_Lookup] AS [VW_AMY_PRG_WCS_Lookup]) AS [Extent2] ON ([Extent2].[IdAnagrafica_Fatturazione] IS NOT NULL) AND ([Extent1].[IdProgetto] = [Extent2].[IdProgetto]) AND ([Extent1].[IdSerie_Progetto] = [Extent2].[IdSerie_Progetto])
    LEFT OUTER JOIN (SELECT 
    [VW_AMY_PRG_WCS_Lookup].[IdProgetto] AS [IdProgetto], 
    [VW_AMY_PRG_WCS_Lookup].[IdSerie_Progetto] AS [IdSerie_Progetto], 
    [VW_AMY_PRG_WCS_Lookup].[Importo] AS [Importo], 
    [VW_AMY_PRG_WCS_Lookup].[IdPercentuale_Successo] AS [IdPercentuale_Successo], 
    [VW_AMY_PRG_WCS_Lookup].[Data_Prevista_Chiusura] AS [Data_Prevista_Chiusura], 
    [VW_AMY_PRG_WCS_Lookup].[IdAnagrafica_Lavorazione] AS [IdAnagrafica_Lavorazione], 
    [VW_AMY_PRG_WCS_Lookup].[IdAnagrafica_Fatturazione] AS [IdAnagrafica_Fatturazione], 
    [VW_AMY_PRG_WCS_Lookup].[IdMezzo_Pervenuto] AS [IdMezzo_Pervenuto], 
    [VW_AMY_PRG_WCS_Lookup].[IdStato] AS [IdStato], 
    [VW_AMY_PRG_WCS_Lookup].[Oggetto] AS [Oggetto], 
    [VW_AMY_PRG_WCS_Lookup].[IdAgente] AS [IdAgente], 
    [VW_AMY_PRG_WCS_Lookup].[Fido_Residuo] AS [Fido_Residuo]
    FROM [dbo].[VW_AMY_PRG_WCS_Lookup] AS [VW_AMY_PRG_WCS_Lookup]) AS [Extent3] ON ([Extent3].[IdAnagrafica_Lavorazione] IS NOT NULL) AND ([Extent1].[IdProgetto] = [Extent3].[IdProgetto]) AND ([Extent1].[IdSerie_Progetto] = [Extent3].[IdSerie_Progetto])
    LEFT OUTER JOIN (SELECT 
    [VW_AMY_PRG_WCS_Lookup].[IdProgetto] AS [IdProgetto], 
    [VW_AMY_PRG_WCS_Lookup].[IdSerie_Progetto] AS [IdSerie_Progetto], 
    [VW_AMY_PRG_WCS_Lookup].[Importo] AS [Importo], 
    [VW_AMY_PRG_WCS_Lookup].[IdPercentuale_Successo] AS [IdPercentuale_Successo], 
    [VW_AMY_PRG_WCS_Lookup].[Data_Prevista_Chiusura] AS [Data_Prevista_Chiusura], 
    [VW_AMY_PRG_WCS_Lookup].[IdAnagrafica_Lavorazione] AS [IdAnagrafica_Lavorazione], 
    [VW_AMY_PRG_WCS_Lookup].[IdAnagrafica_Fatturazione] AS [IdAnagrafica_Fatturazione], 
    [VW_AMY_PRG_WCS_Lookup].[IdMezzo_Pervenuto] AS [IdMezzo_Pervenuto], 
    [VW_AMY_PRG_WCS_Lookup].[IdStato] AS [IdStato], 
    [VW_AMY_PRG_WCS_Lookup].[Oggetto] AS [Oggetto], 
    [VW_AMY_PRG_WCS_Lookup].[IdAgente] AS [IdAgente], 
    [VW_AMY_PRG_WCS_Lookup].[Fido_Residuo] AS [Fido_Residuo]
    FROM [dbo].[VW_AMY_PRG_WCS_Lookup] AS [VW_AMY_PRG_WCS_Lookup]) AS [Extent4] ON ([Extent4].[IdPercentuale_Successo] IS NOT NULL) AND ([Extent1].[IdProgetto] = [Extent4].[IdProgetto]) AND ([Extent1].[IdSerie_Progetto] = [Extent4].[IdSerie_Progetto])

我想知道为什么生成的SQL查询会涉及到这么多左外连接；我希望在 VW_AMY_PRG_WCS_Lookup 上进行简单选择。这种行为的目的是什么？作为映射到 View 的实体，多个连接对查询性能有重大影响。任何解决方法？

更新作为 VW_AMY_PRG_WCS_Lookup View ，我必须手动添加所有关联和导航属性(没有在数据库级别定义 fk，因此从数据库创建模型时没有生成关联)

IdAnagrafica_Fatturazione 指的是 ClienteDIfatturazione，IdAnagrafica_Lavorazione 指的是 ClienteDiLavorazione，IdPercentuale_Successo 指的是 PercentualeSuccesso，IdAgente 指的是 Agente，我只是重命名了模型中的字段，因此它们的名称与 View 中的字段略有不同。

这是Progetto类的代码

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated from a template.
//
//     Manual changes to this file may cause unexpected behavior in your application.
//     Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace EntityModel
{
    using System;
    using System.Collections.Generic;

    public partial class Progetto
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public Progetto()
        {
            this.DocumentiWcs = new HashSet<DocumentoWcsProgetto>();
        }

        public int Codice { get; set; }
        public int Serie { get; set; }
        public Nullable<decimal> Importo { get; set; }
        public Nullable<System.DateTime> DataPrevistaChiusura { get; set; }
        public Nullable<int> IdStato { get; set; }
        public string Oggetto { get; set; }
        public Nullable<int> IdMezzoPervenuto { get; set; }
        public Nullable<int> IdAgente { get; set; }
        public Nullable<decimal> FidoResiduo { get; set; }

        public virtual Cliente ClienteDiFatturazione { get; set; }
        public virtual Cliente ClienteDiLavorazione { get; set; }
        public virtual PercentualeSuccesso PercentualeSuccesso { get; set; }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<DocumentoWcsProgetto> DocumentiWcs { get; set; }
        public virtual Agente Agente { get; set; }
    }
}

这里是 View VW_AMY_PRG_WCS_Lookup 的 DefiningQuery

      <EntitySet Name="VW_AMY_PRG_WCS_Lookup" EntityType="Self.VW_AMY_PRG_WCS_Lookup" store:Type="Views" store:Schema="dbo">
        <DefiningQuery>SELECT 
[VW_AMY_PRG_WCS_Lookup].[IdProgetto] AS [IdProgetto], 
[VW_AMY_PRG_WCS_Lookup].[IdSerie_Progetto] AS [IdSerie_Progetto], 
[VW_AMY_PRG_WCS_Lookup].[Importo] AS [Importo], 
[VW_AMY_PRG_WCS_Lookup].[IdPercentuale_Successo] AS [IdPercentuale_Successo], 
[VW_AMY_PRG_WCS_Lookup].[Data_Prevista_Chiusura] AS [Data_Prevista_Chiusura], 
[VW_AMY_PRG_WCS_Lookup].[IdAnagrafica_Lavorazione] AS [IdAnagrafica_Lavorazione], 
[VW_AMY_PRG_WCS_Lookup].[IdAnagrafica_Fatturazione] AS [IdAnagrafica_Fatturazione], 
[VW_AMY_PRG_WCS_Lookup].[IdMezzo_Pervenuto] AS [IdMezzo_Pervenuto], 
[VW_AMY_PRG_WCS_Lookup].[IdStato] AS [IdStato], 
[VW_AMY_PRG_WCS_Lookup].[Oggetto] AS [Oggetto], 
[VW_AMY_PRG_WCS_Lookup].[IdAgente] AS [IdAgente], 
[VW_AMY_PRG_WCS_Lookup].[Fido_Residuo] AS [Fido_Residuo]
FROM [dbo].[VW_AMY_PRG_WCS_Lookup] AS [VW_AMY_PRG_WCS_Lookup]</DefiningQuery>
      </EntitySet>

Answer 1

原因是您没有在查询中使用 Select。因此选择了原始类。

您应该只选择您真正打算使用的属性。

以下是经过测试的解决方案，它只会连接您的请求中使用的表。唉，我的意大利语(？)有点生疏，如果我没有使用正确的标识符，请原谅我。

var result = myDbContext.Progretti           
    .Select(progret => new                 // from every Progret make one new object
    {                                      // containing only the properties you plan to use
        Id = progret.Id,
        Name = progret.Name

        ProgrettiDiFacturazione = new      // you can even use properties from other tables
        {                                  // again: only properties you plan to use
             Name = progret.ProgrettiDiFacturazione.Name
             Date = progret.ProgrettiDiFacturazione.Date,
             ...
        },
        ...
    })
    .ToList();

DbSet 实现 IQueryable，因此您不需要 AsQueryable

如果您正确设计了一对多(多对多？)类， Entity Framework 足够智能，可以理解为此需要哪些表连接。如果没有从该表请求属性，它不会连接表。