Provide EF.Functions translations for JSON functions

[roji]

For users using JSON without a strongly-typed schema, mapping the column as a string is the only option at the moment. We should provide translations for JSON_VALUE/JSON_QUERY for SQL Server and json_extract for SQLite.

[joelmandell]

If this is up for grabs I might look into it @roji

[roji]

@joelmandell sure, go ahead - the "good first issue" label is a good indication that it's a relatively accessible issue that external contributors can work on (but always good to check).

[joelmandell]

Hi @roji,
I tried to add an IMethodCallTranslator for JSON_VALUE for the SqlServer provider. But the Translate() method is never called. I am having a hard time finding out why it is not being picked up. Do you have anything that can point me to in the correct direction?

[roji]

Have you added your method call translator to SqlServerMethodCallTranslatorProvider?

[joelmandell]

Yes @roji . I have added it there. But I am missing something else :)
This is how it is currently joelmandell@0a81a96

[roji]

I can't see anything immediately wrong with that code, what does debugging through it show? Does it maybe get into your method translator but doesn't recognize the method?

[joelmandell]

Okay my bad. I did some stupid stuff @roji
I did this: ss.Set<JsonEntityBasic>().Where(c => EF.Functions.JsonValue(c.OwnedReferenceRoot, "$.Name") == "foo")
OwnedReferenceRoot is not a string but an complex property on the JsonEntityBasic class.

When I did an EF.Functions.JsonValue with a string property i.e. c.MyStringWithJson, it works.

[roji]

Makes sense.

We could have the function accept a string parameter - this would prevent this kind of mistake. On the other hand, it's probably fairly common for users to use value converters to convert some user object to a string, and they may wish to call JsonValue over that (and having an object allows that).

In theory, it would be nice if users could even invoke this function over an EF7 JSON column property (i.e. an owned entity type); but that likely requires some more significant infrastructure changes to support.

In any case, I suggest leaving as-is for now (with object). When you submit your PR, I'll discuss that with the team.