Linq Select new with GroupBy and OrderBy

Question

For the given data set, I want to return the unique rows for each OrderId that has the lowest number for Status, so the result would be:

I have a working query that does that:

var result = _dbContext.Orders
        .GroupBy(s => s.OrderId)
        .Select(group => group.OrderBy(x => x.Status).First()).ToList();

However, I would like to modify this query to only return three selected fields for each table row, rather than the dozens that exist. I know I need to add something like this:

.Select(group => new
{
    OrderId = ???,
    Status = ???,
    Date = ???
}

But I am unable to add this to my existing query and have it still work. How can I do this?

In SQL you'd use MIN(Status). It's the same here. As for `Date` - which date would you expect? Earliest? Latest? — Panagiotis Kanavos, Jul 01 '22 at 14:05
Whichever `Date` exists for the record with the lowest `Status` — PixelPaul, Jul 05 '22 at 12:34
First, there's no `Date` in your data. Second, there's no "whichever" in SQL. *You* have to specify that with an aggregate function. The answer you accepted doesn't do what you asked. It doesn't group items with `GROUP BY`. It executes a `SELECT TOP 1 OrderID, Status, Date ORDER BY Status` for every Order ID. That's far, far more expensive than a simple query. What you seem to be asking is how to return the data of the *row* with the lowest status. — Panagiotis Kanavos, Jul 05 '22 at 12:45
What you try to do right now is very, very slow. There are ways to speed this up in T-SQL and possibly LINQ. There are several similar SO questions already. You'll have to post the actual table schema and actual sample data though. [This question](https://stackoverflow.com/questions/41840829/using-group-by-with-first-value-and-last-value) asks how to return the first and last stock price per date for example. This can be done with `FIRST_VALUE` and `LAST_VALUE` — Panagiotis Kanavos, Jul 05 '22 at 12:49
@PanagiotisKanavos Yes, you are correct. I would like to return the row with the lowest `Status` for each `OrderId`. — PixelPaul, Jul 05 '22 at 13:00

Terentiy Gatsukov · Answer 1 · 2022-07-01T14:02:03.003

1

You can try to do something like this:

var result = _dbContext.Orders
    .GroupBy(s => s.OrderId)
    .Select(group => group.OrderBy(x => x.Status).First())
    .Select(order => new 
    {
        OrderId = order.OrderId,
        Status = order.Status,
        Date = order.Date
    })
    .ToList();

edited Jul 01 '22 at 14:02

answered Jul 01 '22 at 14:01

Terentiy Gatsukov

37
5

score 1 · Answer 2 · answered Jul 01 '22 at 14:08

1

In SQL you'd use:

SELECT OrderID,MIN(Status) as Status
FROM Orders
GROUP BY OrderID

A LINQ query is similar:

var query = context.Orders
                   .GroupBy(o=>o.OrderId)
                   .Select(g=> new {
                       OrderId=g.Key.OrderId,
                       Status=g.Min(o=>o.Status)
                   });
var results=query.ToList();

answered Jul 01 '22 at 14:08

Panagiotis Kanavos

120,703
13
188
236

Is Min convertible to a Linq query? A lot of those functions aren't. – Robert Harvey Jul 01 '22 at 14:09
From the very first day. These are basic aggregate functions, not BCL-specific calls – Panagiotis Kanavos Jul 01 '22 at 14:09
This seems to be the better solution as the currently accepted answer would very likely issue a less efficient query. – ckuri Jul 01 '22 at 16:44

Linq Select new with GroupBy and OrderBy

2 Answers2