LINQ实现[PARTITION BY]命令 [英] Implementation of [PARTITION BY] command by LINQ

问题描述

我竭尽所能地将 PARTION BY 命令从 TSQL 转换为 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.

推荐答案

linq2db 具有此功能与CTE合作.如果您已经使用EF Core，则可以通过扩展 linq2db.EntityFrameworkCore

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

此SQL可以由LINQ编写

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;
         

我已经简化了您的OrderBy-如果您要按此字段进行分区，则不需要CustomerID.

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

这篇关于LINQ实现[PARTITION BY]命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！