Select parent but order by certain child with dynamic linq

Question

I'am trying to write following in Dynamic-linq. I have following statement in ordinary linq

var result = DBContext.Report
     .Include(h => h.ReportRoleMemberships)
     .Join(DBContext.ReportRoleMemberships.Where(Rrm =>Rrm.ValidTo==null && Rrm.UserId==userId),
           t=>t.Id,
           y=>y.Report_Id,
           (t,y) => new { Ha=t, Rrm=y })
     .OrderBy(h => h.Rrm.ReportRoleValue);

userId is a int with the UserId in the code above.

I want to sort on ReportRoleValue and feels this feel a little bit over the top, but I havent a clue how I should write this is dynamic linq , since it is orderby on one-to-many parent-child relationship.

Answer 1

Assuming that there is a navigation property from ReportRoleMemberships to Reports, you can turn the query around and by that remove the separate Join, e.g.:

var result = DBContext.ReportRoleMemberships
     .Include(h => h.Report)
     .Where(Rrm => Rrm.ValidTo == null && Rrm.UserId == userId)
     .OrderBy(h => h.ReportRoleValue);

This query selects the entries from ReportRoleMemberships and applies the conditions to them and also populates the Report part so that you retrieve both the ReportRoleMembership and Report information in one query.