The correct LINQ expression for this schematic-CodePudding

I am using the entity framework core with entities as shown below (see picture) what I want is to find the correct LINQ expression to include the name property in CourseBranch and their sub-collection CourseBranchClassifiers and CourseBranchClassifierDetails when I want to query by ListAsync

I know how to include the name of Course and branch.

var qry = (from cb in originQry
                   join crs in dbContext.Courses on cb.CourseId equals crs.Id
                   join brch in dbContext.Branches on cb.BranchId equals brch.Id into compCrsBrch
                   from brch in compCrsBrch.DefaultIfEmpty()
                   select new CourseBranchAlias(cb, crs.Name, brch == null ? null : brch.Name));

However, I didn't find a way to do the same thing with the entity subcollection.

I want my query to return the definition below

[
 {
    CourseName:string,
    BranchName:string,
    Classifiers:[
                   {
                     ..., 
                     ClassifierName,
                     ...,
                   }
                ],
    ClassifierDetails:[
                  {
                      ..., 
                      ClassifierDetailName,
                      ...,
                 }],

 }

]

CodePudding user response：

If this is set up for navigation itl'll be something like

courseBranches.Select(cb => new { 
  CourseName = cb.Course.Name,
  BranchName = cb.Branch.Name,
  Classifiers = cb.CourseBranchClassifiers.Select(cbc => new { ClassifierName = cbc.Classifier.Name }).ToArray(),
  ClassifierDetails = cb.CourseBranchClassifierDetails.Select(cbcd => new { ClassifierDetailName = cbcd.ClassifierDetail.Name }).ToArray()
}

CodePudding user response：

Here is how I solved it using a lambda expression. the entities out of the aggregate are referenced by PKs without navigation properties, So we need to use join to select the needed properties related to to target PK as follows. classes with alias suffix are just decorator classes.

var qry = ctx.DebSet<CourseBranch>()
                .Join(dbContext.Courses, outer => outer.CourseId, inner => inner.Id, (cb, c) => new { CourseBranch = cb, c.Name })
                .GroupJoin(dbContext.Branches, outer => outer.CourseBranch.BranchId, inner => inner.Id, (cb, b) => new { CourseBranch = cb.CourseBranch, Course = cb.Name, Branches = b })
                .SelectMany(b => b.Branches.DefaultIfEmpty(),
                (x, y) => new { CourseBranch = x.CourseBranch, x.Course, Branch = y.Name })
                 .Select(x => new
                 {
                     CourseBranch = x.CourseBranch,

                     x.Course,

                     x.Branch,

                     classifiers = x.CourseBranch.Classifiers.Join(dbContext.Classifiers, outer => outer.ClassifierId, inner => inner.Id, (a, b) => new { a, b.Name, b.Description }),

                     classifierDetails = x.CourseBranch.ClassifierDetails.Join(dbContext.Classifiers, outer => outer.ClassifierDetailId, inner => inner.Id, (a, b) => new { a, b.Name, b.Description })
                 })
                 .Select(e => new CourseBranchAlias(e.CourseBranch,
                 e.Course,
                 e.Branch,
                 e.classifiers.Select(c => new CourseBranchClassifierAlias(c.a, c.Name, c.Description)),
                 e.classifierDetails.Select(c => new CourseBranchClassifierDetailAlias(c.a, c.Name, c.Description))));