前言

    在解决本文提到的问题之前,对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);
}

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