如何使用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.WhereEnumerableIterator
1.MoveNext() at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__17
2.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屋!