CODEWITHSUNDEEP
c#sqllinqlinq-to-entities
agri
agri

Reputation: 507

Linq Group by and Order by

I have a sql query where I group by Name and then order by Date. I would also need to select only one entry per group with the newest Date. How do I do that in linq (using lambda expression)?

select Name,Date,Comment 
from Feedback
Group by Name,Date,Comment
order by Date desc

My linq

var query= db.Feedback
                .GroupBy(f => f.Name, (f, g) => new
                {
                    Name = f,
                    Comment = g.Select(h => h.Comment),
                    Date= g.Select(h => h.Date)
                })
                .Select(f => new { f.Name, f.Date, f.Comment}).ToList();

How to sort each group by Date desc and take the first entry?

EDIT

I have updated my query to 

    var query= db.Feedback.GroupBy(f => new { f.Name, f.Comment })
                .Select(a => a.AsEnumerable())
                .Select(b => b.OrderByDescending(f => f.Date)
                .Select(c=>new{c.Name,c.Date,c.Comment}));

...and doing the last Select prevents having reference loop error in JsonSerializer.

My Data should look like this

Name:Name1 Date:28 Sept Comment:comment text

Name:Name2 Date: 15 Aug Comment:comment text

...but it looks like that:

Name:Name1 Date:28 Sept Comment:comment text

Name:Name2 Date: 15 Aug Comment:comment text

Name:Name1 Date: 01 Aug Comment:comment text

...AND HERE IS THE SOLUTION

        var query= db.Feedback
                .GroupBy(f => new { f.Name})
                .Select(a => a.AsEnumerable())
                .Select(b => b.OrderByDescending(f => f.Date)
                    .Select(c => new { c.Name, c.Date, c.Comment})
                    .FirstOrDefault());

Upvotes: 6

Views: 20356

Answers (2)

Umair M
Umair M

Reputation: 10720

You can not apply GroupBy and then OrderBy on Date column. Because if you have it in GroupBy it will give you only one Date value in each group. So if you want to order by Date, you should remove it from group by and use this:

var q = db.Feedback
            .GroupBy(f => new {f.Name, f.Comment})
            .Select( a => a.AsEnumerable())
            .Select(b=> b.OrderByDescending(f => f.Date))
        ;

foreach (var item in q)
{
    Debug.Log(item.FirstOrDefault().ToString());
}

Upvotes: 3

ocuenca
ocuenca

Reputation: 39326

Your query would be:

var query= from f in db.Feedback
           group f by new {f.Name, f.Date, f.Comment} into g
           orderby g.Key.Date descending
           select new {g.Key.Name, g.Key.Date, g.Key.Comment};

Also:

var query= from f in db.Feedback
           group f by new {f.Name, f.Date, f.Comment} into g
           orderby g.Key.Date descending
           select g.Key;

And if you want the firs element, use FirstOrDefault extension method:

var first= query.FirstOrDefault();

If you want the first element of each group ordered by Date then you can also do this:

var query= from f in db.Feedback
           group f by new {f.Name, f.Date, f.Comment} into g
           select g.OrderByDescending(e=>e.Date).FirstOrDefault();

If you want to group only by name then do the following:

var query= from f in db.Feedback
           group f by f.Name into g
           select g.OrderByDescending(e=>e.Date).FirstOrDefault();

Upvotes: 6

Related Questions