I have a table something like this

As you can see I have 7 rows where are 3 kinds of products. I needn't get all 7 rows. I need get only one of each. In result I need only 3 rows where will be only one apple, orange and banana ordered by Date(the most late from each group)

need to write query something like this var result = _db.Fruits.GroupBy(o => o.ProductName).Select(g => g.OrderByDescending(o => o.Date).FirstOrDefault() I try any cases but without result.

CodePudding user response：

Quick answer

You want to take the first occurrence of each group.

You can use OrderBy First over a GroupBy:

var top =
    db
    .Transactions
    .GroupBy(
        t=> t.Product
    )
    .Select(t=>new {
        t.Key, 
        date=t.OrderBy(x => x.Date).Select(x=>x.Date).First()  // <- Magic is Here!
    })
    .ToList();

That generates:

SELECT t.Product AS Key, (
    SELECT t0.Date
    FROM Transactions AS t0
    WHERE t.Product = t0.Product
    ORDER BY t0.Date
    LIMIT 1) AS date
FROM Transactions AS t
GROUP BY t.Product

Note, use OrderByDescending to get the last Date (instead the first one)

More elaborate

If you want the whole Transaction model for each group:

var top =
    db
    .Transactions
    .GroupBy(
        t=> t.Product
    )
    .Select(t=>new {
        productname = t.Key, 
        lasttransaction= t.OrderByDescending(x => x.Date).First()} )
    .AsEnumerable() // <-- at this point you should to move sql to client
    .Select(t => new {t.productname, t.lasttransaction.Date} )
    .ToList();

That is translated as:

SELECT t0.Product, t1.TransactionId, t1.Date, t1.Product
FROM (
    SELECT t.Product
    FROM Transactions AS t
    GROUP BY t.Product
) AS t0
LEFT JOIN (
    SELECT t2.TransactionId, t2.Date, t2.Product
    FROM (
        SELECT t3.TransactionId, t3.Date, t3.Product, ROW_NUMBER() OVER(PARTITION BY t3.Product ORDER BY t3.Date DESC) AS row
        FROM Transactions AS t3
    ) AS t2
    WHERE t2.row <= 1
) AS t1 ON t0.Product = t1.Product