Linq Select new with GroupBy and OrderBy-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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