Multiple select query in linq c#

Question

I have the following classes.

public class Candidate
{
    public long Id { get; set; )
    public List<JobAssigned> JobAssigned { get; set; }       
}

public class JobAssigned
{
    public long Id { get; set; }     
    public List<StageScore> StageScore { get; set; }
    public List<CriteriaScore> CriteriaScore { get; set; }
    public List<StageComment> StageComment { get; set; }       
}

public class StageComment
{
    public long Id { get; set; }
    public JobAssigned  JobAssigned { get; set; }
    public long JobAssignedId { get; set; }
    public long PipelineStageId { get; set; }
    public long CandidateId { get; set; }
    public long JobId { get; set; }
    public string Comment { get; set; }
}

public class StageScore
{
    public long Id { get; set; }
    public JobAssigned JobAssigned { get; set; }
    public long JobAssignedId { get; set; }
    public long Rating { get; set; }
    public long PipelineStageId { get; set; }
    public long CandidateId { get; set; }
    public long JobId { get; set; }
}

public class CriteriaScore
{
    public long Id { get; set; }
    public JobAssigned JobAssigned { get; set; }
    public long JobAssignedId { get; set; }
    public long Rating { get; set; }
    public long PipelineStageCriteriaId { get; set; }
    public long CandidateId { get; set; }
    public long JobId { get; set; }
}

I want eager load all the related table at once. I was trying to the followning,

List<Candidate> candidate = _context.Candidates.                
          Include(f => f.JobAssigned.Select(g => g.StageScore))
          .OrderBy(x => x.Id).ToList();

When I did .Select().Select() it was giving an error. How to get all the collections in a single query?

Look at Include...https://stackoverflow.com/questions/3186009/linq-to-entities-eager-loading-using-include — Prisoner ZERO, Feb 08 '19 at 17:40
List candidate = _context.Candidates. Include(f => f.JobAssigned.Select(g => g.StageScore).Select(h => h.StageComment)). .OrderBy(x => x.Id).ToList(); I was trying to do that, but for 2nd Select() there were no suggestions. — sanjidulhaque, Feb 08 '19 at 17:44
try `ThenInclude` as given [here](https://entityframework-classic.net/then-include) — Matt.G, Feb 08 '19 at 17:52

score 0 · Accepted Answer · edited Feb 08 '19 at 18:37

0

Look at this article I wrote, it loads all the objects dynamically.

And here is how you could include all objects.

I hope that there won't be circle references.

The plugin I wrote in the above url can handle circle references

List<Candidate> candidate = _context.Candidates.                
    Include(f => f.JobAssigned.Select(g => g.StageScore.Select(a=> a.JobAssigned))).
    Include(f => f.JobAssigned.Select(g => g.CriteriaScore.Select(a=> a.JobAssigned))).
    Include(f => f.JobAssigned.Select(g => g.StageComment.Select(a=> a.JobAssigned))).
    OrderBy(x => x.Id).ToList();

edited Feb 08 '19 at 18:37

TanvirArjel

30,049
14
78
114

answered Feb 08 '19 at 17:54

Alen.Toma

4,684
2
14
31

I think it also will work like the following: List candidate = _context.Candidates. Include(f => f.JobAssigned.Select(a => a.StageScore)). Include(g => h.JobAssigned.Select(b => b.StageComment)). Include(h => h.JobAssigned.Select(c => c.CriteriaScore)) .OrderBy(x => x.Id).ToList(); – sanjidulhaque Feb 08 '19 at 18:17
well you could test it :) – Alen.Toma Feb 08 '19 at 18:20

Multiple select query in linq c#

1 Answers1