We have a column where JSON data is stored as a string. This JSON data is read and converted through materialization to an IDictionary<string, object>
. This all works fine until I want to filter on it. The filtering is only applied after getting the data from the database. We will have millions of records so this is not acceptable. My filter is being completely ignored as a WHERE clause by EF Core obviously since probably it has no idea how to parse the MethodCallExpressions.
I'm looking for a way to get as close as possible to the SQL query I have below with the expression tree I have.
I need to convert this:
.Call System.Linq.Queryable.Where(
.Constant<QueryTranslator`1[Setting]>(QueryTranslator`1[Setting]),
'(.Lambda #Lambda1<System.Func`2[Setting,System.Boolean]>))
.Lambda #Lambda1<System.Func`2[Setting,System.Boolean]>(Setting $$it)
{
((System.Nullable`1[System.Int32]).If (
$$it.Value != null && .Call ($$it.Value).ContainsKey("Name")
) {
($$it.Value).Item["Name"]
} .Else {
null
} > (System.Nullable`1[System.Int32]).Constant<Microsoft.AspNet.OData.Query.Expressions.LinqParameterContainer+TypedLinqParameterContainer`1[System.Int32]>(Microsoft.AspNet.OData.Query.Expressions.LinqParameterContainer+TypedLinqParameterContainer`1[System.Int32]).TypedProperty)
== .Constant<System.Nullable`1[System.Boolean]>(True)
}
Into this:
SELECT *
FROM [Setting]
WHERE JSON_VALUE([Value], 'lax $.Name') > 1; -- [Value_Name] > 1 is also fine
With an ExpressionVisitor
I've succeeded in getting as close as
WHERE [Value] = 'Something' but this only works for strings and the key name is lacking.