Linq Group by and Order by

Question

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());

Solution 1

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());
}

Solution 2

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();

Comments

[agri]

I would like to take the newest Date/Comment item which is grouped by Name. FirstOrDefault() will give me the first item for entire query.

[ocuenca]

@agri, check now my answer

[agri]

Since the Date is type datetime in database it gives me "DbSortClause expressions must have a type that is order comparable" error

[agri]

..since the actual database has references to other tables I get "Self referencing loop detected". How to break the loop?

[ocuenca]

There are several ways to do it, here you will a lot. An easy way is disabling lazy loading and proxy creation in your context