使用 EF Core 将表达式树转换为 SQL [英] Expression tree to SQL with EF Core

问题描述

我们有一列将 JSON 数据存储为字符串.读取此 JSON 数据并将其通过物化转换为 IDictionary.这一切正常，直到我想对其进行过滤.过滤仅在从数据库中获取数据后应用.我们将拥有数百万条记录，因此这是不可接受的.我的过滤器显然被 EF Core 完全忽略为 WHERE 子句，因为它可能不知道如何解析 MethodCallExpressions.

We have a column where JSON data is stored as a string. This JSON data is read and converted through materialization to an IDictionary<string, object>. This all works fine until I want to filter on it. The filtering is only applied after getting the data from the database. We will have millions of records so this is not acceptable. My filter is being completely ignored as a WHERE clause by EF Core obviously since probably it has no idea how to parse the MethodCallExpressions.

我正在寻找一种方法，使尽可能接近下面的 SQL 查询以及我拥有的表达式树.

I'm looking for a way to get as close as possible to the SQL query I have below with the expression tree I have.

我需要转换这个:

.Call System.Linq.Queryable.Where(
    .Constant<QueryTranslator`1[Setting]>(QueryTranslator`1[Setting]),
    '(.Lambda #Lambda1<System.Func`2[Setting,System.Boolean]>))

.Lambda #Lambda1<System.Func`2[Setting,System.Boolean]>(Setting $$it)
{
    ((System.Nullable`1[System.Int32]).If (
        $$it.Value != null && .Call ($$it.Value).ContainsKey("Name")
    ) {
        ($$it.Value).Item["Name"]
    } .Else {
        null
    } > (System.Nullable`1[System.Int32]).Constant<Microsoft.AspNet.OData.Query.Expressions.LinqParameterContainer+TypedLinqParameterContainer`1[System.Int32]>(Microsoft.AspNet.OData.Query.Expressions.LinqParameterContainer+TypedLinqParameterContainer`1[System.Int32]).TypedProperty)
    == .Constant<System.Nullable`1[System.Boolean]>(True)
}

进入这个:

SELECT *
FROM [Setting]
WHERE JSON_VALUE([Value], 'lax $.Name') > 1; -- [Value_Name] > 1 is also fine

使用 ExpressionVisitor 我已经成功地接近WHERE [Value] = 'Something' 但这仅适用于字符串并且缺少键名.

With an ExpressionVisitor I've succeeded in getting as close as WHERE [Value] = 'Something' but this only works for strings and the key name is lacking.

推荐答案

在获得官方"支持之前，您可以使用 EF Core 2.0 引入的 数据库标量函数映射.

Until it get "official" support, you can map the JSON_VALUE using the EF Core 2.0 introduced Database scalar function mapping.

例如，在您的上下文派生类或单独的静态类中添加以下静态方法，如下所示:

For instance, add the following static method inside your context derived class or in separate static class as below:

public static class MyDbFunctions
{
    [DbFunction("JSON_VALUE", "")]
    public static string JsonValue(string source, string path) => throw new NotSupportedException();
}

如果它在单独的类中，请将以下内容添加到您的上下文 OnModelCreating 覆盖(如果方法在上下文中，则不需要):

and if it is in separate class, add the following to your context OnModelCreating override (not needed if the method is in the context):

modelBuilder.HasDbFunction(() => MyDbFunctions.JsonValue(default(string), default(string)));

现在您可以在类似于 EF.Functions 的 LINQ to Entities 查询中使用它.请注意，该函数返回 string，因此为了欺骗编译器将其转换"为数字，您可以使用如下所示的双重转换技术(在 EF Core 2.1.2 中测试和工作)):

Now you can use it inside your LINQ to Entities queries similar to EF.Functions. Just please note that the function returns string, so in order to trick the compiler to "cast" it to numeric, you can use the double cast technique shown below (tested and working in EF Core 2.1.2):

var query = db.Set<Setting>()
    .Where(s => (int)(object)MyDbFunctions.JsonValue(s.Value, "lax $.Name") > 1);

翻译成想要的

WHERE JSON_VALUE([Value], 'lax $.Name') > 1

另一种(可能类型更安全)执行转换的方法是使用 Convert 类方法(令人惊讶的是 SqlServer EF Core 提供程序支持):

Another (probably type safer) way to perform the conversion is to use Convert class methods (surprisingly supported by SqlServer EF Core provider):

var query = db.Set<Setting>()
    .Where(s => Convert.ToInt32(MyDbFunctions.JsonValue(s.Value, "lax $.Name")) > 1);

翻译成

WHERE CONVERT(int, JSON_VALUE([Value], 'lax $.Name')) > 1

这篇关于使用 EF Core 将表达式树转换为 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！