empty resultset using JSON_VALUE + JSON_QUERY sql server

[vigouredelaruse]

i want to enable ef core support for JSON_VALUE + JSON_QUERY in sql server

(yes, similar to #11295 and others - info seems to be out of date)

i have the following query that examines the id property of a (definitely persisted in the scenario) json object stored in a string column. i do not want to map the results to a poco representing the json ( there won't be one )

instead i want to query against properties in a json (string) column and return the containing entities (er the mapped entity with the string column containing json i want to filter by).

so no mapping from json to poco

assuming this is a supported scenario consider the linq (ef core debug view)

DbSet<Principal>()
    .SelectMany(m => m.MetaData)
    .Where(w => MSSqlContentContext.JsonValue(
        column: w.JsonSchema, 
        path: <>c__DisplayClass3_0<Principal>.jsonQueryPath).Contains("https"))

the above runtime value of the linq corresponds with what i supplied to the compiler, although i have no idea where c__DisplayClass3_0 came from

        public async Task<IQueryable<JSONAsset>> Read<THasMetaData>(Expression<Func<THasMetaData, bool>> query, Expression<Func<JSONAsset, bool>> assetFilter,
                string jsonQueryPath, List<string> includeClauses = null, int pageSize = 10, int pageNumber = 1, int pageCount = 1)
                where THasMetaData : class, IMetaDataModelEntity
...
...
            var dbSet = ((DbContext)_context).Set<THasMetaData>().SelectMany(m => m.MetaData);
            if(this._context.GetType().Name.Equals(nameof(MSSqlContentContext)))
            {
                // this code only works with sql server dbcontext

                var result = dbSet.Where(w => MSSqlContentContext.JsonValue(w.JsonSchema , jsonQueryPath).Contains("https")); //expects an expression referencing sql server JSON_VALUE function

                return result;
            }

consider the translation, the last line of which shows the emitted JSON_VALUE sql statement

DECLARE @__ef_filter__Id_1 nvarchar(64) = N'6da806b8-f7ab-4e3a-8833-7e834a40e9d0';
DECLARE @__ef_filter__Id_0 nvarchar(64) = N'6da806b8-f7ab-4e3a-8833-7e834a40e9d0';

SELECT [t0].[Id], [t0].[CreatedAt], [t0].[DictionaryKey], [t0].[DisplayName], [t0].[EntityTypeName], [t0].[IsSoftDeleted], [t0].[JsonPayloadSchemaVersion], [t0].[JsonSchema], [t0].[JsonValue], [t0].[ObjectId], [t0].[TenantId], [t0].[Timestamp], [t0].[UpdatedAt]
FROM [Principals] AS [p]
INNER JOIN (
    SELECT [t].[Id], [t].[CreatedAt], [t].[DictionaryKey], [t].[DisplayName], [t].[EntityTypeName], [t].[IsSoftDeleted], [t].[JsonPayloadSchemaVersion], [t].[JsonSchema], [t].[JsonValue], [t].[ObjectId], [t].[TenantId], [t].[Timestamp], [t].[UpdatedAt], [p0].[PrincipalMetadataId]
    FROM [PrincipalMetaData] AS [p0]
    INNER JOIN (
        SELECT [j].[Id], [j].[CreatedAt], [j].[DictionaryKey], [j].[DisplayName], [j].[EntityTypeName], [j].[IsSoftDeleted], [j].[JsonPayloadSchemaVersion], [j].[JsonSchema], [j].[JsonValue], [j].[ObjectId], [j].[TenantId], [j].[Timestamp], [j].[UpdatedAt]
        FROM [JSONAssets] AS [j]
        WHERE [j].[TenantId] = @__ef_filter__Id_1
    ) AS [t] ON [p0].[MetaDataId] = [t].[Id]
) AS [t0] ON [p].[Id] = [t0].[PrincipalMetadataId]
WHERE [p].[TenantId] = @__ef_filter__Id_0 AND (JSON_VALUE([t0].[JsonSchema], N'$.id') LIKE N'%https%')

this is the property definition for storing the json in the poco

        public string? JsonValue { get; set; } = string.Empty;
        public string? JsonSchema { get; set; } = string.Empty;

this is the json under test

                var jsonSchema = JsonConvert.DeserializeObject(@"{
                              ""$id"": ""https://example.com/person.schema.json"",
                              ""$schema"": ""https://json-schema.org/draft/2020-12/schema"",
                              ""title"": ""Person"",
                              ""type"": ""object"",
                              ""properties"": {
                                ""firstName"": {
                                  ""type"": ""string"",
                                  ""description"": ""The person's first name.""
                                },
                                ""lastName"": {
                                  ""type"": ""string"",
                                  ""description"": ""The person's last name.""
                                },
                                ""age"": {
                                  ""description"": ""Age in years which must be equal to or greater than zero."",
                                  ""type"": ""integer"",
                                  ""minimum"": 0
                                }
                              }
                            }");

the below static ef functions required static methods exist on the dbcontext in question

        [DbFunction("JSON_VALUE", IsBuiltIn = true, IsNullable = false)]
        public static string JsonValue(string column, [NotParameterized] string path)
            => throw new NotImplementedException();

        [DbFunction("JSON_QUERY", IsBuiltIn = true, IsNullable = false)]
        public static string JsonQuery(string column, [NotParameterized] string path)
            => throw new NotImplementedException();

the dbcontext and associated services in question tolerate different ef core providers and sql dialects.

the associated test works right away when using postgis with the npgsql provider with code like

 var result = dbSet.Where(w => w.JsonDocument.RootElement
                  .GetProperty(jsonQueryPath).GetString().Contains(jsonQueryValue))

please validate

• the appropriateness of the scenario
• the appropriateness of the translation

if the translation to sql server is valid then i welcome your advise

EF Core version:
Database provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer)
Target framework: (e.g. .NET 7.0)
Operating system:
IDE: (e.g. Visual Studio 2022 17.4)

[ajcvickers]

@vigouredelaruse There's a lot going on here. Can you put it together into something we can run? As I understand it, you have something that works with PostgreSQL, but not with SQL Server. That could be a translation issue, or it could be something that is not supported on SQL Server--it's hard to tell without being able to run the code.

[vigouredelaruse]

hi @ajcvickers i need to do certain things to understand the sql server behavior before i bring code to your attention

• need to see if default values differ between postgis and sql server and whether or not unfavorable nulls cause the sql server engine problems in the scenario, in which case it's not an efcore repository issue
• need to perturb the translations in sql query manager

meantime you didn't seem to notice anything 'wrong' about the mapping/query scenario itself which is useful information

how about i revisit this issue with some code if i can't get to root causes as i described earlier,.