对实体集调用ToList（）的EF Linq to Entities生成包含多个左外部联接的SQL命令 [英] EF Linq to Entities calling ToList() on entity set generates SQL command containing multiple left outer join
问题描述
我的实体 Progetto 映射名称为 VW_AMY_PRG_WCS_Lookup 的视图
My entity "Progetto" map a view with name VW_AMY_PRG_WCS_Lookup
Progetto具有五个导航属性：ClienteDiFatturazione，ClienteDiLavorazione，PercentualeSuccesso，Agente具有多重性0..1和DocumentiWcs具有多重性*
Progetto has five navigations property: ClienteDiFatturazione, ClienteDiLavorazione, PercentualeSuccesso, Agente having multiplicity 0..1 and DocumentiWcs having mupltiplicity *
当我在LINQPad中运行此简单语句时
When I run this simple statement in 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的实体的多个联接会对查询性能产生重大影响。有解决方法吗？
I wonder why the generated SQL query involves so many left outer join; i would expect a simple select on VW_AMY_PRG_WCS_Lookup. What is the purpose of this behavior? Being the entity mapped to a View multiple joins have a heavy impact on query performance. Any workaround?
更新
正在VW_AMY_PRG_WCS_Lookup视图中，我必须手动添加所有关联和导航属性（未在fk处定义数据库级别，因此从数据库创建模型时不会生成任何关联）
UPDATE Being VW_AMY_PRG_WCS_Lookup a view I had to manually add all the associations and navigation properties (no fk defined at database level so no associations generated when model has been created from database)
IdAnagrafica_Fatturazione表示ClienteDIfatturazione，IdAnagrafica_Lavorazione表示ClienteDiLavorazione，IdPercentuale_Successo表示PercentualeSuccesse和IdA，重命名了模型中的字段，因此其名称与视图中的字段略有不同。
IdAnagrafica_Fatturazione refers to ClienteDIfatturazione, IdAnagrafica_Lavorazione refers to ClienteDiLavorazione, IdPercentuale_Successo refers to PercentualeSuccesso and IdAgente to Agente, I just renamed the field in the model so their name are a little differente from the fields in the view.
这是Progetto类的代码
This is the code of Progetto class
//------------------------------------------------------------------------------
// <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; }
}
}
在此为视图VW_AMY_PRG_WCS_Lookup的DefiningQuery
Here the DefiningQuery for the view VW_AMY_PRG_WCS_Lookup
<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>
推荐答案
原因是您不使用在您的查询中选择。因此，将选择原始类。
The cause is that you do not use Select in your query. Therefore the original classes are selected.
您应该只选择您真正打算使用的属性。
You should select only the properties you really plan to use.
下面是经过测试的解决方案，将仅联接您的请求中使用的表。 las，我的意大利语（？）有点生锈，如果我使用的标识符不正确，请原谅我。
Below is the tested solution that will only join the tables used in your requests. Alas my Italian(?) is a bit rusty, so forgive me if I do not use the correct identifiers.
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
DbSet implements IQueryable, so you won't need AsQueryable
如果您正确设计了一对（多对多？）类，则实体框架足够聪明，可以了解哪些表联接是为此需要。如果该表未请求任何属性，它将不会加入该表。
If you have designed your one-to-many (many-to-many?) classes correctly, entity framework is smart enough to understand which table joins are needed for this. it won't join tables if no properties are requested from that table.
这篇关于对实体集调用ToList（）的EF Linq to Entities生成包含多个左外部联接的SQL命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
