In LINQ, how can I do an .OrderBy() on data that came from many to many relationship?-CodePudding

I have already seen the answers to these questions In LINQ, how can I do an .OrderBy() on data that came from my .Include()?, ^ and ^, However, None is the answer to my question.

I have three entities: Letter, Person, LetterPerson as follows:

public class Letter
{
    public int LetterId { get; set; }
    public string  Title { get; set; }

    //MtoM
    public ICollection<LetterPerson> LetterPersons { get; set; }
}

public class Person
{
    public int PersonId { get; set; }
    public string? FirstName { get; set; }
    public string? LastName { get; set; }

    //MtoM
    public ICollection<LetterPerson> LetterPersons { get; set; }
}

    public class LetterPerson
{
    public int LetterPersonId { get; set; }
    [ForeignKey("Letter")]
    public int LetterId { get; set; }
    [ForeignKey("Person")]
    public int PersonId { get; set; }
    public DateTimeOffset? AssignDate { get; set; }=DateTimeOffset.Now;
    public Letter Letter { get; set; }
    public Person Person { get; set; }
}

The Letter entity has Many To Many relationship with the Person entity by the LetterPerson entity. Now, I'd like to get a list of the person according to a specific letter's id and order by on the LetterPerson's id.

I have something like the following query in mind:

var PersonRec = await _dbContext.Persons
                    .Include(u => u.LetterPersons)
                    .Where(u => u.LetterPersons.Any(i => i.LetterId == LetterId))
                    .OrderBy(u => u.LetterPersons.LetterPersonId)
                    //.Include(u => u.LetterPersons.OrderBy(f=>f.LetterPersonId))
                    //.Where(u => u.LetterPersons.OrderBy(f=>f.LetterPersonId).Any(i => i.LetterId == LetterId))
                    //.OrderBy(u => u.LetterPersons.FirstOrDefault().LetterPersonId)
                    .ProjectTo<PersonDTO>(_mapperConfiguration).ToListAsync();

The above commented codes are the attempts that I made, but still the desired result was not achieved. I need .OrderBy(u => u.LetterPersons.LetterPersonId) , However, it clearly gives a compile error.

Question:
How should I correct the OrderBy part?

Just note that I have to send the query as a Data Transfer Object (PersonDTO) that the same as Person entity except FirstName field.

I use EF6 in .Net6.

CodePudding user response：

If the combination (LetterId, PersonId) is unique in the joining table (typical for many-to-many), then the one-to-many relation from Person to LetterPerson for specific LetterId value becomes one-to-one, hence you can use Select or SelectMany with filter to get the single LetterPerson entry, which then could be used for ordering.

For isntance, using LINQ query syntax (more natural for such type of queries):

var query =
(
    from p in _dbContext.Persons
    from lp in p.LetterPersons
    where lp.LetterId == LetterId
    orderby lp.LetterPersonId
    select p
)
.ProjectTo<PersonDTO>(_mapperConfiguration);

var result = await query.ToListAsync();

Note that you don't need Include in order to access related data inside LINQ to Entities query. Also for projection queries Includes are ignored.