如何使用EF Core 2.2将JSON_VALUE转换为DateTime? [英] How can a JSON_VALUE be converted to a DateTime with EF Core 2.2?

问题描述

我正在使用如何编写DbFunction的翻译中的技术映射JSON_VALUE .由于不是JSON中的所有值都是字符串，因此有时有必要进行转换.

I am mapping JSON_VALUE using the technique from How to write DbFunction's translation. Since not all values in the JSON are strings, conversion is sometimes necessary.

转换为int时，一切都很好:

When converting to int, everything is fine:

var results = context.Set<SampleTable>()
    .Where(t1 => Convert.ToInt32(
        JsonExtensions.JsonValue(t1.SampleJson, "$.samplePath.sampleInt")) > 1);
    .ToList();

生成的SQL是:

SELECT *
FROM [SampleTable] AS [t1]
WHERE (CONVERT(int, JSON_VALUE([t1].[SampleJson], N'$.samplePath.sampleInt')) > 1)

但是，当转换为DateTime时，它不起作用:

However, when converting to DateTime, it doesn't work:

DateTime date = new DateTime(2019, 6, 1);
var results = context.Set<SampleTable>()
    .Where(t1 => Convert.ToDateTime(
        JsonExtensions.JsonValue(t1.SampleJson, "$.samplePath.sampleDate")) >= date);
    .ToList();

JsonValue而不是被映射，而是直接被调用，这导致以下异常:

Instead of being mapped, JsonValue is called directly, which leads to the following exception:

System.NotSupportedException HResult = 0x80131515 Message =不支持指定的方法. 堆栈跟踪: 在JsonExtensions.JsonValue(字符串列，字符串路径) 在System.Linq.Enumerable.WhereEnumerableIterator 1.MoveNext() at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__17 2.MoveNext() 在Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()

System.NotSupportedException HResult=0x80131515 Message=Specified method is not supported. StackTrace: at JsonExtensions.JsonValue(String column, String path) at System.Linq.Enumerable.WhereEnumerableIterator1.MoveNext() at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__172.MoveNext() at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()

为什么DateTime的行为与int不同?我该怎么做才能使DateTime正常工作?

Why is DateTime behaving differently than int? What can I do to make DateTime work correctly?

推荐答案

问题是，并非所有Convert方法都受支持.

The problem is that not all Convert method are supported.

实际上它们都不是标准支持的 -EF Core允许当前支持 ToByte，ToDecimal，ToDouble，ToInt16，ToInt32，ToInt64和ToString.

In fact none of them are standardly supported - EF Core allows database providers to add CLR method and member translators for whatever they like. For instance SqlServer provider currently supports ToByte, ToDecimal, ToDouble, ToInt16, ToInt32, ToInt64 and ToString.

这意味着没有与数据库无关的方法来执行服务器端转换.

This means there is no database agnostic way of performing server side conversions.

由于您似乎正在使用SqlServer，因此，作为解决方法，我建议使用隐式数据转换(当前由SqlServer提供程序支持)，方法是使用从我对的答案中使用的双重转换"技术href ="https://stackoverflow.com/questions/52017204/expression-tree-to-sql-with-ef-core/52018037#52018037">类似帖子，例如

Since you seem to be using SqlServer, as workaround I could suggest utilizing the implicit data conversions (currently supported by SqlServer provider) by using the "double cast" technique from my answer to a similar post, e.g.

.Where(t1 => (DateTime)(object)JsonExtensions.JsonValue(t1.SampleJson, "$.samplePath.sampleDate") >= date);

(object)强制转换用于避免C#编译器错误.在查询转换期间，将删除所有强制类型转换，并且SQL Server隐式数据转换最终将完成这项工作.

(object) cast is used to avoid the C# compiler error. During the query translation, both casts will be removed and the SQL Server implicit data conversion will eventually do the job.

这篇关于如何使用EF Core 2.2将JSON_VALUE转换为DateTime?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！