前言

    在解决本文提到的问题之前，对SQL中的Row_Number概念还不是很强，在此之后加深了一定的认识。

发现问题

    最近碰到一个关于“通过复杂排序后，在页面下拉加载数据时，需使用瀑布流的方式来规避原始分页可能出现重复数据的情况”的需求。此需求有以下关键点：

  • 复杂排序
  • 下拉通过瀑布流方式加载数据

    根据上述关键点，此时就会衍生出两个很重要的问题。当下拉到页面最下方时，最后一条数据到底该如何定位？且以哪个字段作为关键字段去获取后续的数据

解决问题

    于是带着问题寻求公司大佬的帮助，在一番讨论过后，在公司大佬的建议下，最后决定定位最后一条数据的关键字段使用Row_Number，但是Google、Baidu了一轮后发现EF Core并无支持排序后获取Row_Number的方法，在不得已的情况下只能通过编写SQL语句在排序之后去获取Row_Number。(PS：虽在Google上发现了thinktecture上有提供对Row_Number的支持，但发现其并不能对布尔值的排序条件做出适配，故放弃)
遂使用如下SQL语句：

1
2
3
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY 
CASE WHEN '2020-05-11T00:00:00' > CreateTime THEN 1 ELSE 0 END DESC, 
CASE WHEN(CityCode = N'441900') AND CityCode IS NOT NULL THEN 1 ELSE 0 END DESC, CreateTime DESC, Id DESC) AS RowNumber, * FROM EstateProjectInfo) temp

    上述语句已经解决了获取Row_Number的问题。但此时又发现了另外一个问题，就是在EF Core里使用SQL语句时，所用实体类需有Row_Number这个字段，才可获取到相应的值。
    一开始，并不想将Row_Number这个字段映射到数据库中，担心会出现不可控的错误（例如：在上述语句中，如果把Row_Number字段映射到数据库中，通过查询就会出现字段重复的问题）。于是尝试用[NotMapped]特性标记其不映射到数据库，但此时就会出现字段能获取到，但具体的值获取不到的问题。原本以为是个无解的问题，但是思前想后，觉得有一个方法可行：将Row_Number映射到表中，然后在获取数据的时候，在第一层查询的时候将除了RowNumber的所有字段取出，这样就规避了字段重复的风险，同时解决了获取Row_Number的问题。效果如图：
image
    下面附上一个封装好的方法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
using System;
using System.Reflection;
using Microsoft.EntityFrameworkCore;
using System.Text;
public static class Function
{    
    /// <summary>
    /// 组合出获取RowNumber的SQL语句   
    /// </summary>   
    /// <typeparam name="T">实体类</typeparam>    
    /// <param name="value"></param>
    /// <param name="orderBy">排序字符串，e.g. "UpdateTime DESC, Id DESC"</param>
    /// <returns></returns>
    public static IQueryable<T> GetPropertyToSql<T>(this DbSet<T> value, string orderBy = "Id DESC") where T : class  
    {
    var type = value.GetType().GenericTypeArguments[0];
    var builder = new StringBuilder("");
    foreach (var property in type.GetProperties())
    {
        if (property.PropertyType.IsPrimitive || property.PropertyType.IsEnum || !property.PropertyType.IsClass || property.PropertyType.Name == "String")
        {
            builder.Append(property.Name + ",");
        }
    }
    var properties = builder.Replace(",RowNumber", "").ToString().TrimEnd(',');
    var sql = string.Format("SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY {0}) AS RowNumber, {1} FROM {2}) temp", orderBy, properties, type.Name);       
    return value.FromSqlRaw(sql);    
}

    上述方法仅为个人观点，如有更好方案希望各位大佬不吝赐教！