エンティティセットでToList（）を呼び出すEF Linq to Entitiesは、複数の左外部結合を含むSQLコマンドを生成します
ギニ・アントニオ
私のエンティティ「Progetto」は、VW_AMY_PRG_WCS_Lookupという名前のビューをマップします
Progettoには5つのナビゲーションプロパティがあります：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で単純な選択を期待します。この動作の目的は何ですか？ビューにマップされたエンティティである複数の結合は、クエリのパフォーマンスに大きな影響を与えます。回避策はありますか？
UPDATE VW_AMY_PRG_WCS_Lookupビューであるため、すべての関連付けとナビゲーションプロパティを手動で追加する必要がありました（データベースレベルでfkが定義されていないため、データベースからモデルが作成されたときに関連付けが生成されません）
IdAnagrafica_FatturazioneはClienteDIfatturazioneを指し、IdAnagrafica_LavorazioneはClienteDiLavorazioneを指し、IdPercentuale_SuccessoはPercentualeSuccessoを指し、IdAgenteはAgenteを指します。モデルのフィールドの名前を変更したため、名前はビューのフィールドと少し異なります。
これは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; }
}
}
ここでは、ビュー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>
ハラルドコププールス
原因は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
1対多（多対多？）クラスを正しく設計した場合、エンティティフレームワークは、これに必要なテーブル結合を理解するのに十分スマートです。そのテーブルからプロパティが要求されていない場合、テーブルは結合されません。
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
編集
コメントを追加