CODEWITHSUNDEEP
c#.netlinqt-sql
Sasan
Sasan

Reputation: 614

Implementation of [PARTITION BY] command by LINQ

I did my best search to convert PARTION BY command from TSQL to LINQ command. But apparently there is no way to convert it.
This is my code to which I am going to convert:

WITH MyRowSet
AS
(
SELECT OrderDate
      ,SalesOrderNumber
      ,AccountNumber
      ,CustomerID
      ,ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY CustomerID, OrderDate DESC) AS RowNum
FROM [Sales].[SalesOrderHeader] 
)
SELECT * FROM MyRowSet WHERE RowNum = 1

If exist any solution I would be greatful to know.

Upvotes: 0

Views: 275

Answers (1)

Svyatoslav Danyliv
Svyatoslav Danyliv

Reputation: 27376

linq2db has this feature among with CTE. If you already work with EF Core, you can extend your LINQ queries by extension linq2db.EntityFrameworkCore

This SQL can be written by LINQ

var rnQuery = 
    from oh in db.SalesOrderHeader
    select new 
    {
       oh.OrderDate,
       oh.SalesOrderNumber,
       oh.AccountNumber,
       oh.CustomerID,
       RowNum = Sql.Ext.RowNumber().Over().PartitionBy(oh.CustomerID)
          .OrderByDesc(oh.OrderDate).ToValue()
    };

// switch to alternative LINQ Translator
rnQuery = rnQuery.ToLinqToDB();

var query =
    from q in rnQuery.AsCte("MyRowSet")
    where q.RowNum == 1
    select q;

I have simplified your OrderBy - CustomerID is not needed if you are making partition by this field.

Upvotes: 3

Related Questions