带有EF Core的SQL表达式树 [英] Expression tree to SQL with EF Core

问题描述

我们有一列，其中JSON数据存储为字符串。读取此JSON数据，并通过实例化将其转换为 IDictionary< string，object> 。一切正常，直到我想对其进行过滤。仅在从数据库中获取数据之后才应用过滤。我们将有数百万条记录，因此这是不可接受的。我的过滤器被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 我'已经成功地获取了与
接近的值[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映射 JSON_VALUE .microsoft.com / en-us / ef / core / what-is-new / ef-core-2.0＃database-scalar-function-mapping rel = noreferrer>数据库标量函数映射。

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)));

现在，您可以在LINQ to Entities查询中使用它，类似于 EF。函数。请注意，该函数返回 string ，因此，为了使编译器将其强制转换为数字，您可以使用下面显示的double cast技术（已测试并可以使用在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

另一种（可能类型更安全）执行转换的方法是使用转换类方法（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屋！