Top one row in group set with Entity framework core

c# entity-framework-core linq

Question

My questions is very simple how I can write query with EF Core 2.1 which would return top 1 row on group set. My example - having following dataset:

Products
+----+----------+------+-------+-------+
| id |  ItemId  | Qty  | Color | Price |
+----+----------+------+-------+-------+
|  1 |    1     |  3   | Red   | 18.2  |
|  2 |    1     |  3   | Blue  | 18.3  |
|  3 |    1     |  3   | Red   | 19.2  |
|  4 |    1     |  3   | Blue  | 19.3  |
|  5 |    2     |  1   | Red   | 10.2  |
|  6 |    2     |  2   | Blue  | 10.4  |
|  7 |    2     |  1   | Red   | 10.2  |
|  8 |    2     |  2   | Blue  | 10.4  |
+----+----------+------+-------+-------+

I want to get how many item groups there are, and inside this item group I want to find the cheepest product

I can express this in SQL:

CREATE TABLE Products (ItemId INT NOT NULL, Quantity INT NOT NULL, Color VARCHAR(20) NOT NULL, Price DECIMAL(10,2) NOT NULL)

INSERT INTO Products 
SELECT 1, 3, 'Red', 18.2
UNION ALL
SELECT 1, 3, 'Blue', 18.3
UNION ALL
SELECT 2, 1, 'Red', 10.2
UNION ALL
SELECT 2, 2, 'Blue', 10.4
UNION
SELECT 1, 3, 'Red', 19.2
UNION ALL
SELECT 1, 3, 'Blue', 19.3
UNION ALL
SELECT 2, 1, 'Red', 10.2
UNION ALL
SELECT 2, 2, 'Blue', 10.4

Then I would write somewhat complex query to get my result:

WITH CTE AS 
(
SELECT ItemId, Quantity, Color, Price
FROM Products
WHERE Color = 'Red'
), CTE2 AS (
SELECT T.ItemId, T.Cnt, C.Quantity, C.Price, C.Color ROW_NUMBER() OVER(PARTITION BY T.ItemId ORDER BY C.Price, C.Color ASC) AS RowNumber 
FROM (
SELECT ItemId, Count(*) AS Cnt
FROM CTE
GROUP BY ItemId) AS T(ItemId, Cnt)
INNER JOIN CTE AS C
ON C.ItemId = T.ItemID)
SELECT *
FROM CTE2
WHERE RowNumber = 1

My expected result set would be:

Products
+----------+------+-------+-------+-------+
|  ItemId  | Qty  | Color | Price | Count |
+----------+------+-------+-------+-------+
|    1     |  3   | Red   | 18.2  |   2   |
|    2     |  1   | Red   | 10.2  |   2   |
+----------+------+-------+-------+-------+

But I have no idea how to get this done using EF core. I found this question Entity Framework select one of each group by date but using that example client simple fetches all the rows from database and then does it's magic, this might become inefficient with large dataset.

There is also suggestion to use raw SQL or stored procedure, but then it beats the purpose of using EF core.

1
0
9/20/2018 10:19:46 AM

Popular Answer

If I follow what you are asking for correctly, this should work:

  from r in Products
  group r by r.ItemID into rr
  select new {rr.Key, Lowest = rr.Min(x=>x.Price)}

However, it would help if you explained exactly what you mean by "item group" (grouped by what?), and perhaps a sample expected output...

(Note: that was actually written in Linq2Sql, but it should be the same in EF Core)

0
9/19/2018 1:40:23 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow