Entity Framework 的动态 Where 实现

• 下载源代码 - 12.6 KB

引言

我有一个需求，需要动态过滤 SQL 查询——也就是说，我不知道列名、比较运算符或比较值，直到运行时。经过大量搜索，我整理了这篇文章，其中 90% 的内容都基于其他人的想法和代码。这篇文章的“附加价值”在于，它将所有零散的组件整合到一个解决方案中，解决了这个问题。

我的第一站是 CPian Fitim Skenderi 的这篇优秀文章， 动态构建 Linq 的 Where 子句（另请参阅附录 2：Skenderi 工作中的注意事项），它让我初步了解了如何使用 Linq 的 Expression 类构建动态查询。Skenderi 代码的缺点是它不适用于 IQueryable。它过滤的是查询结果，而不是将过滤器注入 SQL 并利用数据库的 SQL 引擎，更不用说过滤器的目的就是减少数据库返回的记录集了。

我的第二站是 Axel Heer 在 Stack Overflow 帖子中的内容。在某些方面，这是一个更简洁的实现，并且适用于 IQueryable，但有两个问题。第一个问题是将要比较的值硬编码为 Expression.Constant。虽然过滤器现在已进入 SQL where 子句，但字面量 string 会使 SQL 容易受到 SQL 注入攻击。我需要的是将值作为 SQL 参数添加。

我的第三站通过 Ivan Stoev 在 Stack Overflow 帖子中解决了这个问题。这涉及到创建一个返回值的 Func 表达式，并应用表达式体。由于值不再是常量，Entity Framework 会将 Func 的表达式体转换为参数！神奇的魔法。

然而，现在出现了一个新问题需要解决。作为常量，SQL 能很好地处理可空与非可空类型以及自动数据转换。例如，我可以对像 '1962-08-19' 这样的常量进行日期比较，但一旦这个值变成 SQL 参数，后端模型的数据类型就必须与表列的数据类型完全匹配。解决这个问题是我为 Skenderi、Heer 和 Stoev 的工作添加的内容。

测试架构和模型

我使用的架构包含两个表

• InventoryItem（Id 和 Name）
• Sale（Id、InventoryItem_ID 和 SaleDate）

有关架构，请参阅附录 1：架构。

请注意

1. 每个库存项有零个或多个销售记录，我想要的是每个库存项的扁平化视图以及该项被销售的日期列表。
2. 为了保持简单和不符合实际，库存项和销售是的一对多关系；显然，一次销售通常会应用于该销售的一项或多项库存项，因此会是多对多关系，但为了本篇文章的目的，没有理由创建第三个映射表。
3. 虽然 Sale 模型有一个指向 InventoryItem 的外键，但为了简单起见，我没有在 InventoryItem 模型中实现 Sale 的集合——这不是本文的重点。

InventoryItem 模型

[Table("InventoryItems")]
public class InventoryItem
{
  [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  public int Id { get; set; }
  public string Name { get; set; }
}

Sale 模型

[Table("Sales")]
public class Sale
{
  public int Id { get; set; }
  public int InventoryItem_Id { get; set; }
  public DateTime SaleDate { get; set; }
}

DbContext

这是不言自明的

public class MyContext : DbContext
{
  public DbSet<InventoryItem> InventoryItems { get; set; }
  public DbSet<Sale> Sales { get; set; }

  public MyContext(DbContextOptions<DbContext> options) : base(options)
  {
  }
}

测试数据初始化

测试数据在单元测试过程中进行初始化和清理。在下面的代码中，记录被删除并创建了两个库存项。三个销售记录与第一个项目相关联，两个与第二个库存项目相关联。

[TestClass]
public class DynamicWhereTests
{
  // Modify this for your local SQL Server instance.
  public static string connectionString = "Data Source=[your data source];
  Initial Catalog=DynamicWhere;Integrated Security=True;
  Connection Timeout=60;TrustServerCertificate=True";

  public static InventoryItem inv1;
  public static InventoryItem inv2;

  [ClassInitialize]
  public static void CreateRecords(TestContext _)
  {
    var builder = new DbContextOptionsBuilder<DbContext>().UseSqlServer(connectionString);
    using var context = new MyContext(builder.Options);
  
    context.Sales.ExecuteDelete();
    context.InventoryItems.ExecuteDelete();

    inv1 = new InventoryItem() { Name = "Item 1" };
    inv2 = new InventoryItem() { Name = "Item 2" };
    context.InventoryItems.Add(inv1);
    context.InventoryItems.Add(inv2);
    context.SaveChanges();
    context.Sales.Add(new Sale() 
    { InventoryItem_Id = inv1.Id, SaleDate = DateTime.Parse("08/19/1962") });
    context.Sales.Add(new Sale() 
    { InventoryItem_Id = inv1.Id, SaleDate = DateTime.Parse("08/20/1962") });
    context.Sales.Add(new Sale() 
    { InventoryItem_Id = inv1.Id, SaleDate = DateTime.Parse("08/21/1962") });
    context.Sales.Add(new Sale() 
    { InventoryItem_Id = inv2.Id, SaleDate = DateTime.Parse("03/21/1991") });
    context.Sales.Add(new Sale() 
    { InventoryItem_Id = inv2.Id, SaleDate = DateTime.Parse("03/22/1991") });
    context.SaveChanges();
  }
  ...

不使用 ClassCleanup 方法，而是在初始化过程中删除记录，因为调试测试并退出调试器不会运行 cleanup 方法。

一个基本查询

我们将查询结果序列化为 JSON，以便于检查。在第一个单元测试中，我们只创建一个非过滤的查询，将两个表连接成一个扁平视图。

[TestMethod]
public void BasicQuery()
{
  using var context = GetContext();

  var itemSales = (from item in context.InventoryItems
    select new 
    {
      Item = item, 
      Sales = context.Sales.Where
              (s => s.InventoryItem_Id == item.Id).ToList() // FK join
    })
    .OrderBy(item => item.Item.Id)
    .ToList();

  Assert.AreEqual(2, itemSales.Count);
  Assert.AreEqual(3, itemSales[0].Sales.Count);
  Assert.AreEqual(2, itemSales[1].Sales.Count);

  var json = JsonConvert.SerializeObject(itemSales);
}

我们在 JSON 中看到

[
  {
    "Item": {
      "Id": 19,
      "Name": "Item 1"
    },
    "Sales": [
      {
        "Id": 41,
        "InventoryItem_Id": 19,
        "SaleDate": "1962-08-19T00:00:00"
      },
      {
        "Id": 42,
        "InventoryItem_Id": 19,
        "SaleDate": "1962-08-20T00:00:00"
      },
      {
        "Id": 43,
        "InventoryItem_Id": 19,
        "SaleDate": "1962-08-21T00:00:00"
      }
    ]
  },
  {
    "Item": {
      "Id": 20,
      "Name": "Item 2"
    },
    "Sales": [
      {
        "Id": 44,
        "InventoryItem_Id": 20,
        "SaleDate": "1991-03-21T00:00:00"
      },
      {
        "Id": 45,
        "InventoryItem_Id": 20,
        "SaleDate": "1991-03-22T00:00:00"
      }
    ]
  }
]

重要的是，我们还在 Output 视图中看到了 SQL。

Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [i].[Id], [i].[Name], [s].[Id], [s].[InventoryItem_Id], [s].[SaleDate]
FROM [InventoryItems] AS [i]
LEFT JOIN [Sales] AS [s] ON [i].[Id] = [s].[InventoryItem_Id]
ORDER BY [i].[Id]

添加动态 Where

这里变得更有趣了。我们可以为销售记录创建 Linq 表达式，按销售日期 >= 1962 年 8 月 21 日进行过滤，并为库存项创建表达式，按第一个库存项的 ID 进行过滤。

[TestMethod]
public void DynamicWhereQuery()
{
  using var context = GetContext();

  var itemFilters = new List<Filter>()
  {
    new Filter()
    {
      PropertyName= "Item.Id",
      Operation = Op.Equals,
      Value = inv1.Id
    }
  };

  var saleFilters = new List<Filter>()
  {
    new Filter()
    {
      PropertyName = "SaleDate",
      Operation = Op.GreaterThanOrEqual,
      Value = DateTime.Parse("08/21/1962")
    }
  };

  var sales = (from sale in context.Sales select sale).Where(saleFilters);

  var filteredItemSales = (from item in context.InventoryItems
  select new
  {
    Item = item,
    Sales = sales.Where(s => s.InventoryItem_Id == item.Id).ToList()  // FK join
  })
  .Where(itemFilters)
  .ToList();

  Assert.AreEqual(1, filteredItemSales.Count);
  Assert.AreEqual(1, filteredItemSales[0].Sales.Count);

  var json = JsonConvert.SerializeObject(filteredItemSales);
}

在这里，我们创建了两个过滤器，一个用于 InventoryItem 记录的 ID，另一个用于 Sale 的 SaleDate 字段。销售过滤器应用于 Linq 表达式，而库存过滤器在构建库存项及其销售的扁平视图时应用。生成的 JSON 是

[
  {
    "Item": {
      "Id": 79,
      "Name": "Item 1"
    },
    "Sales": [
      {
        "Id": 193,
        "InventoryItem_Id": 79,
        "SaleDate": "1962-08-21T00:00:00"
      }
    ]
  }
]

我们看到 SQL 执行了左连接，并按库存项的 ID 和销售的 SaleDate 进行过滤。

Executed DbCommand (2ms) [Parameters=[@__p_0='1962-08-21T00:00:00.0000000', @__p_1='69'], 
CommandType='Text', CommandTimeout='30']
SELECT [i].[Id], [i].[Name], [t].[Id], [t].[InventoryItem_Id], [t].[SaleDate]
FROM [InventoryItems] AS [i]
LEFT JOIN (
  SELECT [s].[Id], [s].[InventoryItem_Id], [s].[SaleDate]
  FROM [Sales] AS [s]
  WHERE [s].[SaleDate] >= @__p_0
) AS [t] ON [i].[Id] = [t].[InventoryItem_Id]
WHERE [i].[Id] = @__p_1
ORDER BY [i].[Id]

请注意，需要 ToList()，否则我们会收到以下错误

最终投影中的集合必须是 'IEnumerable<T>' 类型，例如 'List<T>'。请考虑使用 'ToList' 或其他机制将 'IQueryable<T>' 或 'IOrderedEnumerable<T>' 转换为 'IEnumerable<T>'。

因此，我们无法同时应用这两个过滤器，因为 Sales 已被 ToList() 方法解析为集合——它不再是表达式。如果我们尝试对 Sales 应用过滤器，我们会收到此错误

'SaleDate' 不是类型 'System.Collections.Generic.List`1[DynamicWhere.Sale]' 的成员（参数 'propertyOrFieldName'）

这里的关键要点是，动态过滤器只能应用于表达式，这是有道理的，因为这毕竟是其全部意义所在。

幕后

借鉴 Skenderi 的工作，我们有一组操作，一个 Filter 类，以及三个 EntityFramework 知道如何转换为 SQL 的方法。

public enum Op
{
  Equals,
  GreaterThan,
  LessThan,
  GreaterThanOrEqual,
  LessThanOrEqual,
  Contains,
  StartsWith,
  EndsWith
}

public class Filter
{
  public string PropertyName { get; set; }
  public Op Operation { get; set; }
  public object Value { get; set; }
}

public static class ExpressionBuilder
{
  private static MethodInfo containsMethod = 
      typeof(string).GetMethod("Contains", new Type[] { typeof(string) });
  private static MethodInfo startsWithMethod = 
      typeof(string).GetMethod("StartsWith", new Type[] { typeof(string) });
  private static MethodInfo endsWithMethod = 
      typeof(string).GetMethod("EndsWith", new Type[] { typeof(string) });
  ...

接下来的部分来自 Heer 的帖子，修改为接受过滤器集合。

public static IQueryable<T> Where<T>(this IQueryable<T> query, List<Filter> filters)
{
  var target = Expression.Parameter(typeof(T));

  return query.Provider.CreateQuery<T>(CreateWhereClause<T>
                        (target, query.Expression, filters));
}

private static Expression CreateWhereClause<T>
 (ParameterExpression target, Expression expression, List<Filter> filters)
{
  var predicate = Expression.Lambda(CreateComparison<T>(target, filters), target);

  return Expression.Call(typeof(Queryable), nameof(Queryable.Where), 
         new[] { target.Type }, expression, Expression.Quote(predicate));
}

private static Expression CreateComparison<T>(ParameterExpression target, 
                          List<Filter> filters)
{
  Expression exp = null;

  filters.ForEach(filter =>
  {
    var memberAccess = CreateMemberAccess(target, filter.PropertyName);
    var exp2 = GetExpression<T>(memberAccess, filter);

    exp = exp == null ? exp2 : Expression.Or(exp, exp2);
  });

  return exp;
}

private static Expression CreateMemberAccess(Expression target, string selector)
{
  return selector.Split('.').Aggregate(target, Expression.PropertyOrField);
}

非常重要！请注意，我正在通过“或”操作将过滤器组合起来，而不是“和”。组合“和”与“或”的能力超出了本文的范围——这将在后续文章中讨论。

我添加的部分负责将过滤器值转换为预期的模型类型，特别是处理模型中的可空数据类型，即使过滤器是不可空类型。

public static Expression GetSelector<T>(Filter filter)
{
  switch (filter.Value)
  {
    case int t1: return GetTypedSelector<T, int>(filter);
    case float f1: return GetTypedSelector<T, float>(filter);
    case double d1: return GetTypedSelector<T, double>(filter);
    case long l1: return GetTypedSelector<T, long>(filter);
    case DateTime dt1: return GetTypedSelector<T, DateTime>(filter);
    case bool b1: return GetTypedSelector<T, bool>(filter);
    case decimal d1: return GetTypedSelector<T, decimal>(filter);
    case char c1: return GetTypedSelector<T, char>(filter);
    case byte by1: return GetTypedSelector<T, byte>(filter);
    case short sh1: return GetTypedSelector<T, short>(filter);
    case ushort ush1: return GetTypedSelector<T, ushort>(filter);
    case uint ui1: return GetTypedSelector<T, uint>(filter);
    case ulong ul1: return GetTypedSelector<T, ulong>(filter);
    case string s1:
    {
      Expression<Func<string>> valueSelector = () => (string)filter.Value;
      return valueSelector.Body;
    }
    default: return null;
  }
}

public static Expression GetTypedSelector<T, R>(Filter filter) where R : struct
{
  // We actually need to get the property type, chaining from the container class,
  // and converting the value type to the property type using Expression.Convert
  var pi = GetPropertyInfo(typeof(T), filter.PropertyName);

  // This seems to be the preferred way.
  // Alternate: if (type.IsGenericType && type.GetGenericTypeDefinition() == 
  // typeof(Nullable<>))
  var propIsNullable = Nullable.GetUnderlyingType(pi.PropertyType) != null;

  Expression<Func<object>> valueSelector = () => filter.Value;
  Expression expr = propIsNullable ? Expression.Convert(valueSelector.Body, 
             typeof(R?)) : Expression.Convert(valueSelector.Body, typeof(R));

  return expr;
}

private static PropertyInfo GetPropertyInfo(Type baseType, string propertyName)
{
  string[] parts = propertyName.Split('.');

  return (parts.Length > 1)
    ? GetPropertyInfo(baseType.GetProperty(parts[0]).PropertyType, 
                      parts.Skip(1).Aggregate((a, i) => a + "." + i))
    : baseType.GetProperty(propertyName);
}

这段代码可能可以利用某种缓存机制，这样对于已知模型属性的过滤器就不必进行所有这些反射操作了。

最后，再次借鉴 Skenderi 的代码，创建了所需操作的表达式。

private static Expression GetExpression<T>(Expression member, Filter filter)
{
  // How do we turn this into a SQL parameter 
  // so we're not susceptible to SQL injection attacks?
  // Like this: <a href="https://stackoverflow.com/a/71019524">
  // https://stackoverflow.com/a/71019524</a>
  //Expression<Func<object>> valueSelector = () => filter.Value;
  //var actualValue = valueSelector.Body;

  var actualValue = GetSelector<T>(filter);

  switch (filter.Operation)
  {
    case Op.Equals: return Expression.Equal(member, actualValue);
    case Op.GreaterThan: return Expression.GreaterThan(member, actualValue);
    case Op.GreaterThanOrEqual: 
         return Expression.GreaterThanOrEqual(member, actualValue);
    case Op.LessThan: return Expression.LessThan(member, actualValue);
    case Op.LessThanOrEqual: return Expression.LessThanOrEqual(member, actualValue);
    case Op.Contains: return Expression.Call(member, containsMethod, actualValue);
    case Op.StartsWith: return Expression.Call(member, startsWithMethod, actualValue);
    case Op.EndsWith: return Expression.Call(member, endsWithMethod, actualValue);
  }

  return null;
}

记录 SQL 和参数值

通常情况下，人们不会这样做，但由于我想查看 SQL 和参数值，因此使用以下代码来实例化数据库上下文。

private MyContext GetContext()
{
  var builder = new DbContextOptionsBuilder<DbContext>().UseSqlServer(connectionString);
  builder.EnableSensitiveDataLogging();
  builder.LogTo(s => Debug.WriteLine(s), LogLevel.Information);
  var context = new MyContext(builder.Options);

  return context;
}

结论

希望我已经演示了一个完整的动态 IQueryable 过滤解决方案，该解决方案不会受到 SQL 注入攻击，它建立在比我更聪明的三个人关于如何做到这一切的工作之上。

附录 1：架构

USE [DynamicWhere]
GO
/****** Object: Table [dbo].[InventoryItems] Script Date: 4/2/2023 6:22:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[InventoryItems](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [Name] [nvarchar](255) NOT NULL,
CONSTRAINT [PK_InventoryItems] PRIMARY KEY CLUSTERED 
(
  [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, _
       IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, _
       ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Sales] Script Date: 4/2/2023 6:22:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Sales](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [InventoryItem_Id] [int] NOT NULL,
  [SaleDate] [datetime] NOT NULL,
CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED 
(
  [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, _
 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, _
 OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Sales] WITH CHECK ADD CONSTRAINT [FK_Sales_InventoryItems] _
            FOREIGN KEY([InventoryItem_Id])
REFERENCES [dbo].[InventoryItems] ([Id])
GO
ALTER TABLE [dbo].[Sales] CHECK CONSTRAINT [FK_Sales_InventoryItems]
GO

附录 2：Skenderi 工作中的注意事项

我遇到了关于 Skenderi 的 动态构建 Linq 的 Where 子句 中大小写敏感性的 评论，我在此包含它，因为它很有用。

Contains 不适用于区分大小写的数据。所以我对 Contains 做了一些小改动，以防有人需要。基本上，使用 indexof。

var pi = param.Type.GetProperty(filter.PropertyName);
var propertyAccess = Expression.MakeMemberAccess(param, pi);
var indexOf = Expression.Call(propertyAccess, 
              "IndexOf", null, Expression.Constant(constant.Value, typeof(string)), 
              Expression.Constant(StringComparison.InvariantCultureIgnoreCase));
return Expression.GreaterThanOrEqual(indexOf, Expression.Constant(0));

由于 SQL 默认情况下进行不区分大小写的 string 比较，因此在这里这不是问题。