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.
