How can a JSON_VALUE be converted to a DateTime with EF Core 2.2?
The problem is that not all Convert
method are supported.
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.
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 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.