EF Linq to Entities, calling ToList () on a set of objects, generates an SQL command containing several left outer joins

My "Progetto" object displays a view named VW_AMY_PRG_WCS_Lookup

enter image description here

Progetto has five navigation properties: ClienteDiFatturazione, ClienteDiLavorazione, PercentualeSuccesso, Agente with a multiplicity of 0..1 and DocumentiWcs having mupltiplicity *

When I run this simple statement in LINQPad

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

generated 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, . ?

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; }
    }
}

DefiningQuery 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>
+6

Source: https://habr.com/ru/post/1016291/


All Articles