Loading multiple levels of nested navigation and collection properties with proxy disabled

Question

I'm having a hard time figuring out how to do queries with levels of alternating collection and navigation properties with proxy and lazy loading disabled to serialize the result.

public ApplicationDbContext()
            : base("Debug")
        {
            this.Configuration.LazyLoadingEnabled = false;
            this.Configuration.ProxyCreationEnabled = false;
        }

The entities look like this (omitting non-navigation or non-collections):

public class Comprobante: ComprobanteGeneric 
{
}

public class ComprobanteGenericStructure
    public int Id { get; set; }
    [Required]
    public virtual Conceptos Conceptos { get; set; }
}

public class Conceptos
{
    public Conceptos()
    {
        Concepto = new List<Concepto>();
    }
    [ForeignKey("Comprobante")]
    public int Id { get; set; }
    [Required]
    public virtual ICollection<Concepto> Concepto { get; set; }
    public virtual Comprobante Comprobante { get; set; }
}

public class Concepto :RelatedComprobante
{
    [Key]
    public int Id { get; set; }
    public virtual OrderOfImpuestosForConceptos Impuestos { get; set; }
    public virtual Conceptos Conceptos { get; set; }
}

public class RelatedComprobante
{
    [ForeignKey("Comprobante")]
    public int ComprobanteId { get; set; }
    public virtual Comprobante Comprobante { get; set; }
}

public class OrderOfImpuestosForConceptos : RelatedComprobante
{
    public int Id { get; set; }
    public virtual TrasladosNode Traslados { get; set; }
    public virtual Retenciones Retenciones { get; set; }
}

public class TrasladosNode : RelatedComprobante
{
    public TrasladosNode()
    {
        Traslado = new HashSet<Traslado>();
    }
    [Key]
    public int Id { get; set; }
    [Required]
    public virtual ICollection<Traslado> Traslado { get; set; }
}

public class Traslado : RelatedComprobante
{
    public int Id { get; set; }
    public virtual TrasladosNode TrasladosNode { get; set; }
}

public class Retenciones : RelatedComprobante
{
    public Retenciones()
    {
        Retencion = new List<Retencion>();
    }
    public int Id { get; set; }
    [Required]
    public virtual ICollection<Retencion> Retencion { get; set; }
}

public class Retencion : RelatedComprobante
{
    public int Id { get; set; }
    public virtual Retenciones Retenciones { get; set; }
}

I need the whole graph. With lazy loading enabled it returns it all eventually but serialization takes several seconds and several queries to DB. I disabled it and tried the following query:

comprobantes = _db.Comprobante
            .Include(c => c.Conceptos.Concepto.Select(co => co.Impuestos.Retenciones).Select(r=>r.Retencion))
            .Include(c => c.Conceptos.Concepto.Select(co => co.Impuestos.Traslados).Select(t => t.Traslado));

However I get an empty concepto list:

[{
  "id": 324,
  "conceptos": {
    "concepto": []
  }
},
{
  "id": 340,
  "conceptos": {
    "concepto": []
  }
}
}]

Even simpler queries like the following yield the exact same results:

comprobantes = _db.Comprobante
            .Include(c => c.Conceptos.Concepto.Select(co => co.Impuestos));

I know is not the serialization for when I inspect the object while debugging is empty:

What am I doing wrong?

If you need a large graph, try loading each entity separately and let the DbContext fix-up the navigation properties. — David Browne - Microsoft, Jun 07 '18 at 17:21

Emanuel · Accepted Answer · 2018-06-07T23:08:38.543

0

Thanks to David comment I was able to build a working query(es):

var comprobantes = _db.Comprobante
            .Include(b => b.Conceptos.Concepto.Select(c => c.Impuestos.Retenciones.Retencion))
            .Include(b => b.Conceptos.Concepto.Select(c => c.Impuestos.Traslados.Traslado));
foreach (var comprobante in comprobantes)
{
    _db.ComprobanteTraslado.Where(t => t.ComprobanteId == comprobante.Id);
    _db.ComprobanteRetencion.Where(r => r.ComprobanteId == comprobante.Id);
}

edited Jun 07 '18 at 23:08

answered Jun 07 '18 at 19:05

Emanuel

1,981
1
20
28

I fail to see how this helps load `concepto` lists. The addition `.Include(b => b.Conceptos)` doesn't have any effect because `Include(b => b.Conceptos.Concepto....` already does that. So how would this `concepto` lists? Also, the statements inside the `foreach` loop don't do anything because they only create transient `IQueryable`s that aren't executed. – Gert Arnold Jun 07 '18 at 21:15
@GertArnold I can't pretend I know the details of the inner workings of EF, but I based this solution on https://stackoverflow.com/questions/11917926/loading-related-entities-in-separate-queries-and-sorting-of-children-collection I guess as per David's suggestion by loading them to context EF will fixup the relationships automatically. – Emanuel Jun 07 '18 at 22:11
You are right on .Include(b => b.Conceptos). It doesnt have an effect.I'm removing it. – Emanuel Jun 07 '18 at 23:08

Loading multiple levels of nested navigation and collection properties with proxy disabled

1 Answers1