Field sorting of Join with Line in Mysql (C#, MVC)

Question

I have the following query:

query = from p in db.pessoa
        join f in db.pessoa_origem on p.Pessoa_Origem_Id equals f.Id
        join s in db.pessoa_status on p.Pessoa_Status_Id equals s.Id
        join c in db.contato on p.Id equals c.Pessoa_Id
        select p;

And I want to order by c.data.

The cardinality of the relationship between person and contact is 1 : n. That is, a person may have 0 or n contacts.

When I use the code:

query = query.OrderByDescending(u => u.contato.Count > 0 ? u.contato.OrderByDescending(t => t.Id).FirstOrDefault().Data : null);

It works though, it’s catching the biggest Id among the contacts to sort by his date. What I need is to pick up the contact with the highest date and sort by date. However, the following part does not work:

query = query.OrderByDescending(u => u.contato.Count > 0 ? u.contato.OrderByDescending(t => t.Data).FirstOrDefault().Data : null);

Could anyone help in how to solve this problem?

Answer 1

Has a relationship of People for Contacts with cardinality 1:N, that is, a Person may have none or several contacts.

Layout of Entities:

Entidade Pessoa:

public class Pessoa
{
    public int Id { get; set; }
    public string Nome { get; set; }    
    public virtual System.Collections.Generic.ICollection<Contato> Contatoes { get; set; }

    public Pessoa()
    {
        Contatoes = new System.Collections.Generic.List<Contato>();
    }
}

Entity Contato

public class Contato
{
    public int Id { get; set; }
    public int PessoaId { get; set; }
    public System.DateTime Data { get; set; }

    public virtual Pessoa Pessoa { get; set; }
}

---

I will reproduce your doubt to select only the contact with the greatest date, follows below the two examples:

Ascending ()

var query = (from p in db.Pessoas
             join c in db.Contatoes on p.Id equals c.PessoaId
             group p by new { p.Id, p.Nome, Data = p.Contatoes.Max(x => x.Data) } into g
             orderby g.Key.Data ascending
             select new
             {
                 g.Key.Id,
                 g.Key.Nome, 
                 g.Key.Data                                  
             }).AsQueryable();

var result = query.ToList();

---

Descending (Descending)

var query = (from p in db.Pessoas
             join c in db.Contatoes on p.Id equals c.PessoaId
             group p by new { p.Id, p.Nome, Data = p.Contatoes.Max(x => x.Data) } into g
             orderby g.Key.Data descending
             select new
             {
                 g.Key.Id,
                 g.Key.Nome, 
                 g.Key.Data                                 
             }).AsQueryable();

var result = query.ToList();

Tip: at the end of his joins introduces the part group by the same is in my two examples.

Answer 2

You can use Where with Max, but you don’t need to sort a list by date if there will be only one date on the list.

query = query.Where(
    w => 
        w.contato.data == query.Max(
            m => 
                m.contato.data
         )
)