Extending SqlServerQuerySqlGenerator

[meikeric]

AFAIK JSON mapping is not supported yet... Therefore, I dug into the EFCore code and came up with the following implementation which has pretty good success and allows me to have a base class with many child classes that maps all the additional class properties that don't have a physical db field into a single field using JSON and deserializes (on the db side using JSON_VALUE or JSON_QUERY if it's a complex type) to the property. The only part left I have to figure out is the JSON serialization on saving.

I had to make copies of Check.cs, CodeAnnotations.cs, and maybe another internal class. If you could make these not "internal" that would be fantastic!

Steps: Create new Attribute

using System;

namespace EFExtensions
{
    public class JsonMapAttribute : Attribute
    {
        public string PathValue;

        public JsonMapAttribute(string pathValue)
        {
            this.PathValue = pathValue;
        }
    }
}

Override the SqlGenerator Factory

using JetBrains.Annotations;
using Microsoft.EntityFrameworkCore.Infrastructure.Internal;
using Microsoft.EntityFrameworkCore.Query.Expressions;
using Microsoft.EntityFrameworkCore.Utilities;

using Microsoft.EntityFrameworkCore.Storage;
using Microsoft.EntityFrameworkCore.Query.Sql.Internal;
using Microsoft.EntityFrameworkCore.Query.Sql;

namespace EFExtensions
{
    public class SqlServerQuerySqlGeneratorWithJsonFactory : SqlServerQuerySqlGeneratorFactory   
    {
    /// <summary>
    ///     This API supports the Entity Framework Core infrastructure and is not intended to be used
    ///     directly from your code. This API may change or be removed in future releases.
    /// </summary>
    /// 
    public SqlServerQuerySqlGeneratorWithJsonFactory([NotNull] QuerySqlGeneratorDependencies dependencies,
        [NotNull] ISqlServerOptions sqlServerOptions)
    : base(dependencies, sqlServerOptions)
    {
    }

        /// <summary>
        ///     This API supports the Entity Framework Core infrastructure and is not intended to be used
        ///     directly from your code. This API may change or be removed in future releases.
        /// </summary>
        public override IQuerySqlGenerator CreateDefault(SelectExpression selectExpression)
            => new SqlServerQueryWithJsonSqlGenerator(
        Dependencies,
        Check.NotNull(selectExpression, nameof(selectExpression)),
        true); // Default rowNumberPagingEnabled to true
    }
}

Override the SqlGenerator

// Copyright (c) .NET Foundation. All rights reserved.
// Licensed under the Apache License, Version 2.0. See License.txt in the project root for license information.

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using Extensions;
using JetBrains.Annotations;
using Microsoft.EntityFrameworkCore.Internal;
using Microsoft.EntityFrameworkCore.Query.Expressions;
using Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal;
using Microsoft.EntityFrameworkCore.Query.Sql.Internal;
using Microsoft.EntityFrameworkCore.Storage;
using Microsoft.EntityFrameworkCore.Utilities;
using Remotion.Linq;
using Remotion.Linq.Clauses;
using Remotion.Linq.Parsing;

// ReSharper disable SwitchStatementMissingSomeCases
namespace Microsoft.EntityFrameworkCore.Query.Sql
{
    public class SqlServerQueryWithJsonSqlGenerator : SqlServerQuerySqlGenerator, ISqlServerExpressionVisitor
    {
        /// <summary>
        ///     This API supports the Entity Framework Core infrastructure and is not intended to be used
        ///     directly from your code. This API may change or be removed in future releases.
        /// </summary>
        public SqlServerQueryWithJsonSqlGenerator(
            [NotNull] QuerySqlGeneratorDependencies dependencies,
            [NotNull] SelectExpression selectExpression,
            bool rowNumberPagingEnabled)
            : base(dependencies, selectExpression, rowNumberPagingEnabled)
        {
        }

        public override Expression VisitColumn(ColumnExpression columnExpression)
        {
            Check.NotNull(columnExpression, nameof(columnExpression));

            JsonMapAttribute jsonMapAttribute = null;

            if (columnExpression.Property.PropertyInfo != null)
            {
                jsonMapAttribute = columnExpression.Property
                    .PropertyInfo
                    .GetCustomAttribute<JsonMapAttribute>();
            }

            if (jsonMapAttribute != null)
            {
                var jsonMap = (JsonMapAttribute)jsonMapAttribute;

                Sql.Append(jsonMap.PathValue); //"(CONVERT([int],json_value([ActionBase],'$.ApiId')))"
            }
            else
            {
                base.VisitColumn(columnExpression);
            }

            Sql.AppendLine("--" + columnExpression.Name);

            return columnExpression;
        }
    }
}

Replace the IQuerySqlGeneratorFactory in Startup.cs/ConfigureServices

// Add Entity Framework services to the services container.
            services
                .AddDbContext<ApiDataContext>(options => 
                    options
                        .ReplaceService<IQuerySqlGeneratorFactory, SqlServerQuerySqlGeneratorWithJsonFactory>()
                        .UseSqlServer(Configuration["Data:Manager:ConnectionString"])
                );

Add a discriminator to my DbContext

builder.Entity<Base>(base =>
            {
                    base
                    .HasDiscriminator<string>("BaseType")
                        .HasValue<Alert>("Alert");

And finally in my model, use the JsonMap attribute

public class Alert : Base ...

 [JsonMap("(CONVERT([varchar],json_value([BaseType],'$.Message')))")]
        public string Message { get; set; }

[JsonMap("(CONVERT([varchar],JSON_QUERY([BaseType],'$.Parameters')))")]
        public string _Parameters { get; set; }

        [NotMapped]
        public Dictionary<string, string> Parameters
        {
            get
            {
                var param = (_Parameters == null ? null :
                    JsonConvert.DeserializeObject<Dictionary<string, string>>(_Parameters)
                    );

                return param;
            }
            set
            {
                _Parameters = JsonConvert.SerializeObject(value);
            }
        }

And now this all gets magically unwrapped into a typed object on load.

I'd be more than happy to contribute something more refined to EFCore with the teams guidance as I think this is a feature the community would love to use.

[ajcvickers]

@meikeric Thanks for showing an interest in this. Here is some guidance based on our current understanding of how this would be best implemented and general patterns/considerations in the EF Core code.

• We had envisioned this would be implemented, or at least participate in, types mapping and type conversions. Put another way, rather than having ad-hoc code that does conversions, this should be part of a more generalized mechanism. Otherwise we end up with multiple blocks of code doing similar things which leads to inconsistencies and bugs.
• In a similar vein to the first comment, we would drive any behavior changes from information in the EF model, and not directly from an Attribute class. This may be just an annotation in the model. Then there should be fluent API to configure the model information and, if we want tot support an attribute, a convention to do that. For example, look at how data annotations are handled. Note however, that we rarely introduce new attributes.
• It is important that whatever we do works across the stack. This means not just query, but also change tracking, the updates, Migrations etc.

With regard to your questions about internal classes.

• Check and CodeAnnotations are very simple and are really implementation details. Feel free to copy the source or just do these things in your own way. This is what, for example, provider writers do. It's unlikely that we will make this public.
• We typically make provider-specific implementations of services internal. However, if there is a specific case where there is significant value in promoting the class to public, then please file a separate issue on that and we will consider it.

[meikeric]

Thanks for the feedback. Regarding the type mapping that seems to be a popular topic of concern: Hypotetically, and assuming it makes it into 2.0, would you be using native types on the provider side, e.g. SQL server?

I'm trying to invision how EF could use SQL Server's new support of JSON_VALUE and JSON_QUERY to offload the work to the DB server. That's essentially what I was trying to encompass with a JsonMap attribute. I hadn't finished working through but I wanted to present a scenario to solicit feedback.

[ajcvickers]

We are closing this issue because no further action is planned for this issue. If you still have any issues or questions, please log a new issue with any additional details that you have.