How to convert from PostgreSQL query to LINQ or lambda expression

Question

I have a PostgreSQL query that I need to convert to Lamba exp or LINQ to use in my ASP.NET Core C# project. Furthermore, the name of the table is "DeliveryFinances".

This is my PostgreSQL query

SELECT t1."DriverId", t2."OrderNo", COUNT(*)
FROM public."DeliveryFinances" t1
JOIN
(
    SELECT MIN("OrderNo") AS "OrderNo", "DriverId"
    FROM public."DeliveryFinances"
    GROUP BY "DriverId"
) t2 ON t1."DriverId" = t2."DriverId"
GROUP BY t1."DriverId", t2."OrderNo"
LIMIT 25

This is the result I get after running the query in pgAdmin

| DriverId| OrderNo | count |
|----------------------------
| 123123  | REQWFA  |      3|
| 345534  | ASDCA3  |      2|
| 565534  | MCJSL1  |      1|

Is there any way that I can do this in lambda or LINQ? Please help me.

What you have tried so far? Anyway [similar question with answer](https://stackoverflow.com/questions/69715184/converting-sql-to-linq-with-sub-query) — Svyatoslav Danyliv, Oct 27 '21 at 10:48
Given that this query is probably more efficiently done with row-numbering and window functions, and LINQ doesn't usually support those, I'm not convinced you should convert it to LINQ anyway. — Charlieface, Oct 27 '21 at 12:00
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Nov 05 '21 at 00:49
What LINQ are you using: LINQ to Objects / SQL / EF 6.x / EF Core 2.0 / 2.1 / 3.x / 5.x / 6.x? What are your entity or database classes? — NetMage, Nov 05 '21 at 00:49

score 0 · Answer 1 · answered Nov 05 '21 at 01:00

Using EF Core 5, a straight forward translation seems to work:

var qmin = from t2 in DeliveryFinances
           group t2.OrderNo by t2.DriverId into dg
           select new { OrderNo = dg.Min(), DriverId = dg.Key };

var q = from t1 in DeliveryFinances
        join t2 in qmin on t1.DriverId equals t2.DriverId
        group new { t1, t2 } by new { t1.DriverId, t2.OrderNo } into t1t2g
        select new {
            t1t2g.Key.DriverId,
            t1t2g.Key.OrderNo,
            Count = t1t2g.Count()
        };
var ans = q.Take(25);

It did not seem particularly efficient on my database, but translated pretty much directly to your SQL.

A better look at your data might provide a way to use a Group Join in the query, but it might not be translatable to SQL.

How to convert from PostgreSQL query to LINQ or lambda expression

1 Answers1