Sort by join field with linq in MySql (C #, MVC)

Question

Sort by join field with linq in MySql (C #, MVC)

Navigation

#1 by (1 votes)
#2 by (0 votes)

2

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 sort by c.data .

The cardinality of the relationship between person and contact is 1: n. That is, a person can 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, however, it is getting the highest% of% of contacts to sort by its date. What I need is to get the contact with the highest date and sort by date. However, the following snippet does not work:

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

Can anyone help with how to solve this problem?

mysql c# linq asp.net-mvc

asked by anonymous 24.07.2015 / 22:54

2 answers

Requests array How to "erase" an image from the screen

score 1 · Answer 1

You have a People relationship to Contacts with cardinality < / em>.

Entities Layout:

Person Entity:

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 Contact

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 question by selecting only the contact with the longest date, below the two examples below:

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

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 your joins you enter the group by part of my example.

score 0 · Answer 2

You can use Where with Max, however, you do not need to sort a list by date if there is only one date in the list.

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