Home > other >  Select only first row from each coincidence | Entity Framework 6
Select only first row from each coincidence | Entity Framework 6

Time:11-02

I have a table something like this

userId productName transactionId Date
6556656 apple 3534534 25.10
6556656 apple T423423 23.10
6556656 orange 7687898 22.10
6556656 orange 5675665 27.10
6556656 orange 1231312 25.09
6556656 banana 4564545 14.09
6556656 banana 7898878 30.09

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
  • Related