My entity "Progetto" map a view with name VW_AMY_PRG_WCS_Lookup
Progetto has five navigations property: ClienteDiFatturazione, ClienteDiLavorazione, PercentualeSuccesso, Agente having multiplicity 0..1 and DocumentiWcs having mupltiplicity *
When I run this simple statement in LINQPad
var prj = Progetti.AsQueryable(); prj.ToList(); the sql generated is
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]) 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?
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 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.
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; } } } 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> 
0 comments:
Post a Comment