EF Core中Partition by实现

一、SQL语句实现

Partition by是SQL Server数据库中提供的分区函数，跟Group by不同的是，Partition by能够按照分区返回所有记录，而Group by只能返回一条记录。

举个例子，有如下的数据库，需要找出每个唯一编号最新状态的数据。

显然，CW048201和CW048202它们的最新状态都是取消报废状态，用Group By去获取单行数据是无法获取的。这里我就可以借助于Partition By。

SQL语句如下：

select * from (
    select ROW_NUMBER() over (partition by EPC order by Scrapdate desc) as fid,* from [dbo].[hps_BaoFei]
) a  

语句执行结果如下：

可以看出分区函数Partitiion By按照编码分为了两个部分并且每个部分按照时间降序排列，因此我们只需再加一个条件即可获取每个编码的最新数据。

SQL语句如下：

select * from (
    select ROW_NUMBER() over (partition by EPC order by Scrapdate desc) as fid,* from [dbo].[hps_BaoFei]
) a where a.fid = 1 

语句执行结果如下：

二、EF Core的实现

直接上代码，我将其封装到了Web API里面，可以直接返回结果。

[HttpGet]
[Route("/api/GetBaofei")]
public List<hps_BaoFei> GetBaofei()
{
    using (BroadswordContext _bc = new BroadswordContext())
    {
        List<hps_BaoFei> list = (from p in _bc.hps_BaoFeis.Select(t => t.EPC).Distinct()
                                 from q in _bc.hps_BaoFeis
                                 .Where(t => t.EPC == p)
                                 .OrderByDescending(t => t.Scrapdate)
                                 .Take(1)
                                 select q).ToList();
        return list;
    }
}

注意其中的核心代码部分，我们可以Debug看一下其生成的SQL语句。

SELECT [t0].[id], [t0].[EPC], [t0].[Remarks], [t0].[ScrapRole], [t0].[ScrapState], [t0].[Scrapdate], [t0].[Scraptime]
FROM (
    SELECT DISTINCT [h].[EPC]
    FROM [hps_BaoFei] AS [h]
) AS [t]
INNER JOIN (
    SELECT [t1].[id], [t1].[EPC], [t1].[Remarks], [t1].[ScrapRole], [t1].[ScrapState], [t1].[Scrapdate], [t1].[Scraptime]
    FROM (
        SELECT [h0].[id], [h0].[EPC], [h0].[Remarks], [h0].[ScrapRole], [h0].[ScrapState], [h0].[Scrapdate], [h0].[Scraptime], ROW_NUMBER() OVER(PARTITION BY [h0].[EPC] ORDER BY [h0].[Scrapdate] DESC) AS [row]
        FROM [hps_BaoFei] AS [h0]
    ) AS [t1]
    WHERE [t1].[row] <= 1
) AS [t0] ON [t].[EPC] = [t0].[EPC]

执行结果：