Entity lambda framework with include returns empty Collection

Question

I have a select that returns chained data and plays it in a Viewmodel created for the simple reason that I cannot return all data from my table, as in the example:

var data = _context.Forms
.Include(i => i.OrganizationUnit)
.Include(i => i.FormSections)
.Include(i => i.FormSections.Select(p => p.Predecessors))
.Include(i => i.FormSections.Select(p => p.FormSectionSecurities))
.Include(i => i.FormSections.Select(p => p.FormSectionSecurities.Select(x => x.FormSectionSecurityPremission)))
.Include(i => i.FormSections.Select(p => p.FormSectionSecurities.Select(x => x.Role)))
.Include(i => i.FormSections.Select(p => p.FormSectionFields))
.Include(i => i.FormSections.Select(p => p.FormSectionFields.Select(x => x.FormSectionFieldType)))
.Include(i => i.FormSections.Select(p => p.FormSectionFields.Select(x => x.FormSectionFieldType)))
.Include(i => i.FormSections.Select(p => p.FormSectionFields.Select(x => x.List)))
.Include(i => i.FormSections.Select(p => p.FormSectionFields.Select(x => x.List).Select(j => j.ListItems)))
.OrderBy(i => i.Name)
.AsNoTracking()
.Where(i => i.EFormStatus == EFormStatus.Active)
.Select(f => new FormViewModel
{
    Id = f.Id,
    Name = f.Name,
    Description = f.Description,
    EFormStatus = f.EFormStatus,
    Instructions = string.Empty,
    OrganizationUnit = f.OrganizationUnit,
    FormSections = f.FormSections
}).ToList();

f. Formsection returns a Collection that has more Collections inside, as it is possible to notice by Includes for example: Formsectionfields. The problem occurs precisely in the return of this "third level" that comes empty, even with the includes. Also occurs for any collection that is below the collection f. Formsection.

Some indication of what to do or what might be wrong ?

Below Formviewmodel

public class FormViewModel
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public OrganizationUnit OrganizationUnit { get; set; }
    public string Description { get; set; }
    public string Instructions { get; set; }
    public EFormStatus EFormStatus { get; set; }
    public ICollection FormSections { get; set; }  
}

UPDATED

I discard the use of joins using Linq (query syntax), as it is necessary that only one row be returned with the chained objects.

Answer 1

You can create multiple selects including your tables as follows.

var data = _context.Forms
.Select(f => new 
{
  Forms = f.Forms,
  OrganizationUnit = _context.OrganizationUnit.FirstOrDefault(x => x.OrganizationUnitId == f.OrganizationUnitId) // Todo seus ids ... x.OrganizationUnitId == f.OrganizationUnitId
  FormSections = _context.FormSections.Where(x => x.FormSectionsId == f.FormSectionsId) // Todo seus ids ...x.FormSectionsId == f.FormSectionsId  
})
.Select(f => new 
{
   Forms = f.Forms,
   OrganizationUnit = f.OrganizationUnit,
   f.FormSections.Predecessors = _context.Predecessors.Where(x => x.PredecessorsId == f.FormSectionsId) 
   FormSections = f.FormSections,
   // ....  demais campos
})
.AsNoTracking()
.Where(i => i.EFormStatus == EFormStatus.Active)
.Select(f => new FormViewModel
{
    Id = f.Forms.Id,
    Name = f.Forms.Name,
    Description = f.Forms.Description,
    EFormStatus = f.EFormStatus,
    OrganizationUnit = f.OrganizationUnit,
    FormSections = f.FormSections
}).ToList();

In the end, this becomes a single select with JOIN, a problem that may occur in your case is that if there is no relationship between your tables, the properties of your FormViewModel will be null.

For example, if there is no relationship between Formsections and Form the return of the property below will be null.

FormSections = _context.FormSections.Where(x => x.FormSectionsId == f.FormSectionsId)