Is it possible to order by a joined column in Entity Framework Core?-CodePudding

I've got a relational database mapped via EF Core with a custom many to many table which holds a sort order alongside the mapping.

Stripped down example classes:

class A 
{
    int Id;
    IEnumerable<AB> Bs;
    DateTime Created;
}

class B 
{
    int Id;
    IEnumerable<AB> As
}

class AB 
{
    int AId;
    A A;
    int BId;
    B B;
    int Ordering;
}

I want to get a list of As (filtered by some criteria omitted for brevity) and then ordered, either by the created time or by the ordering specified in the join table. When ordering by the specified Ordering property the results will be filtered to only As linked to one particular B (although I don't think that should make any difference).

So I have something like

_dbContext.As.Include(a => a.Bs)
             .ThenInclude(ab => ab.B)
             .Where( a => a.Bs.Any(b => b.Id == 1))
             .OrderBy(a => a.Created)
             .ToList()

which returns all As linked to B with Id 1, and then sorted by their created date.

Now I want to run the same query for all As linked to B1 but order by the Ordering from the link table.

In SQL it would be simple since I know I have joined on the link table and could just order by that column, but I can't find a way to specify that to Entity Framework.

I can pass the filtered B Id to the clause, but that constructs a SQL subquery for every single row, which I want to avoid for performance reasons

.OrderBy(a => a.Bs.First(ab => ab.BId == 1).Ordering)

I've tried passing an anonymous type to the OrderBy clause in the hope that EF would interpret Ordering as the joined Ordering column, but it didn't.

.OrderBy(a => new {Ordering = 0}.Ordering)

I've tried starting from the join table and working outwards instead, which then causes issues with duplication when one A is linked to multiple Bs, and using Distinct then removes the previously applied ordering.

_dbContext.ABs
          .Include(ab => ab.A)
          .Include(ab => ab.B)
          .OrderBy(ab => ab.Ordering)
          .Select(ab => ab.A)
          .Distinct()

I've tried joining the A and AB tables into an anonymous type so that I have access to the ordering property from the join table, but that didn't work either, same issue with deduplication.

_dbContext.As.Join( _dbContext.ABs, a => a.Id, ab => ab.AId, (a, ab) => new {a, ab})
             .Where( x => x.ab.BId = 1)
             .OrderBy( x => x.ab.Ordering)
             .Select(x => x.A)
             .Distinct()

I think the only other option might be to materialise the filtered data into memory and sort on the client. But I want to avoid that when I should just be able to apply ORDER BY directly in the generated SQL.

Is there any way I can tell Entity Framework to order by the Ordering column in the join table without creating subqueries?

CodePudding user response：

Try the following query, I hope understand your question.

var query = 
    from a in _dbContext.As
    from ab in _dbContext.ABs
        .Where(ab => ab.AId == ab.AId)
        .Where(ab => ab.BId == 1)
        .Orderby(ab => ab.Ordering)
        .Take(1)
    orderby ab.Ordering
    select a;