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.
When converting to
int, everything is fine:
var results = context.Set<SampleTable>() .Where(t1 => Convert.ToInt32( JsonExtensions.JsonValue(t1.SampleJson, "$.samplePath.sampleInt")) > 1); .ToList();
The resulting SQL is:
SELECT * FROM [SampleTable] AS [t1] WHERE (CONVERT(int, JSON_VALUE([t1].[SampleJson], N'$.samplePath.sampleInt')) > 1)
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();
Instead of being mapped,
JsonValue is called directly, which leads to the following exception:
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__172.MoveNext() at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
DateTime behaving differently than
int? What can I do to make
DateTime work correctly?
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
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.