如何使用 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时，一切正常:

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.NotSupportedExceptionHResult=0x80131515Message=不支持指定的方法.堆栈跟踪:在 JsonExtensions.JsonValue(字符串列，字符串路径)在 System.Linq.Enumerable.WhereEnumerableIterator1.MoveNext()在 Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__172.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 允许 数据库提供者 来添加 CLR 方法和成员翻译器，以添加他们喜欢的任何内容.例如 SqlServer provider 目前支持 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 提供程序支持)，通过使用我对 类似帖子，例如

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) cast 用于避免 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屋！