Last active
May 21, 2025 07:39
-
-
Save martinzima/40b61ef3c3a6aa8512017344a713589c to your computer and use it in GitHub Desktop.
Extension for IDictionary<string, object> translation support for Npgsql.EntityFrameworkCore.PostgreSQL 5.0.0 (proof of concept). Register by calling AddJsonDictionarySupport().
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| using Microsoft.EntityFrameworkCore.Query; | |
| using Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal; | |
| using System.Collections.Generic; | |
| using System.Diagnostics.CodeAnalysis; | |
| using System.Linq.Expressions; | |
| using System.Reflection; | |
| namespace JsonDictsGist | |
| { | |
| public class CustomNpgsqlSqlTranslatingExpressionVisitor : NpgsqlSqlTranslatingExpressionVisitor | |
| { | |
| private static readonly MethodInfo DictionaryGetItem = typeof(IDictionary<string, object>).GetProperty("Item").GetMethod; | |
| private static readonly MethodInfo ContainsKey = typeof(IDictionary<string, object>).GetRuntimeMethod(nameof(IDictionary<string, object>.ContainsKey), new[] { typeof(string) }); | |
| private static readonly MethodInfo JsonGet = typeof(PgsqlFunctions).GetMethod(nameof(PgsqlFunctions.JsonGet)); | |
| public CustomNpgsqlSqlTranslatingExpressionVisitor( | |
| [NotNull] RelationalSqlTranslatingExpressionVisitorDependencies dependencies, | |
| [NotNull] QueryCompilationContext queryCompilationContext, | |
| [NotNull] QueryableMethodTranslatingExpressionVisitor queryableMethodTranslatingExpressionVisitor) | |
| : base(dependencies, queryCompilationContext, queryableMethodTranslatingExpressionVisitor) | |
| { | |
| } | |
| protected override Expression VisitIndex(IndexExpression node) | |
| { | |
| if (node.Object.Type == typeof(IDictionary<string, object>)) | |
| { | |
| var methodCall = Expression.Call(JsonGet, node.Object, node.Arguments[0]); | |
| return base.VisitMethodCall(methodCall); | |
| } | |
| return base.VisitIndex(node); | |
| } | |
| [return: NotNullIfNotNull("node")] | |
| protected override LabelTarget VisitLabelTarget(LabelTarget node) | |
| { | |
| return base.VisitLabelTarget(node); | |
| } | |
| protected override Expression VisitMethodCall(MethodCallExpression methodCall) | |
| { | |
| if (methodCall.Method == DictionaryGetItem) | |
| { | |
| var result = Expression.Call(JsonGet, | |
| methodCall.Object, | |
| methodCall.Arguments[0]); | |
| methodCall = result; | |
| } | |
| else if (methodCall.Method == ContainsKey) | |
| { | |
| methodCall = Expression.Call(methodCall.Object, methodCall.Method, methodCall.Arguments[0]); | |
| } | |
| return base.VisitMethodCall(methodCall); | |
| } | |
| } | |
| } |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| using Microsoft.EntityFrameworkCore.Query; | |
| using Npgsql.EntityFrameworkCore.PostgreSQL.Query.Internal; | |
| using System.Diagnostics.CodeAnalysis; | |
| namespace JsonDictsGist | |
| { | |
| public class CustomNpgsqlSqlTranslatingExpressionVisitorFactory : NpgsqlSqlTranslatingExpressionVisitorFactory | |
| { | |
| [NotNull] readonly RelationalSqlTranslatingExpressionVisitorDependencies dependencies; | |
| public CustomNpgsqlSqlTranslatingExpressionVisitorFactory([NotNull] RelationalSqlTranslatingExpressionVisitorDependencies dependencies) : base(dependencies) | |
| { | |
| this.dependencies = dependencies; | |
| } | |
| public override RelationalSqlTranslatingExpressionVisitor Create( | |
| QueryCompilationContext queryCompilationContext, | |
| QueryableMethodTranslatingExpressionVisitor queryableMethodTranslatingExpressionVisitor) | |
| => new CustomNpgsqlSqlTranslatingExpressionVisitor( | |
| dependencies, | |
| queryCompilationContext, | |
| queryableMethodTranslatingExpressionVisitor); | |
| } | |
| } |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| using Microsoft.EntityFrameworkCore.Infrastructure; | |
| using Npgsql.EntityFrameworkCore.PostgreSQL.Infrastructure; | |
| namespace JsonDictsGist | |
| { | |
| public static class JsonDictionaryDbContextOptionsBuilderExtensions | |
| { | |
| public static NpgsqlDbContextOptionsBuilder AddJsonDictionarySupport( | |
| this NpgsqlDbContextOptionsBuilder optionsBuilder) | |
| { | |
| var infrastructure = (IRelationalDbContextOptionsBuilderInfrastructure)optionsBuilder; | |
| var builder = (IDbContextOptionsBuilderInfrastructure)infrastructure.OptionsBuilder; | |
| var extension = infrastructure.OptionsBuilder.Options | |
| .FindExtension<JsonDictionaryDbContextOptionsExtension>() | |
| ?? new JsonDictionaryDbContextOptionsExtension(); | |
| builder.AddOrUpdateExtension(extension); | |
| return optionsBuilder; | |
| } | |
| } | |
| } |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| using System.Collections.Generic; | |
| using Microsoft.EntityFrameworkCore.Infrastructure; | |
| using Microsoft.EntityFrameworkCore.Query; | |
| using Microsoft.Extensions.DependencyInjection; | |
| namespace JsonDictsGist | |
| { | |
| public class JsonDictionaryDbContextOptionsExtension : IDbContextOptionsExtension | |
| { | |
| public void ApplyServices(IServiceCollection services) | |
| { | |
| services.AddSingleton<IMethodCallTranslatorPlugin, JsonDictionaryMethodCallTranslatorPlugin>(); | |
| } | |
| public void Validate(IDbContextOptions options) | |
| { | |
| } | |
| public DbContextOptionsExtensionInfo Info => new ExtensionInfo(this); | |
| sealed class ExtensionInfo : DbContextOptionsExtensionInfo | |
| { | |
| public ExtensionInfo(IDbContextOptionsExtension extension) | |
| : base(extension) | |
| { | |
| } | |
| public override bool IsDatabaseProvider => false; | |
| public override long GetServiceProviderHashCode() => 0; | |
| public override void PopulateDebugInfo(IDictionary<string, string> debugInfo) | |
| => debugInfo["Olify:" + nameof(JsonDictionaryDbContextOptionsBuilderExtensions.AddJsonDictionarySupport)] = "1"; | |
| public override string LogFragment => "using AddJsonDictionarySupport "; | |
| } | |
| } | |
| } |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| using System.Collections.Generic; | |
| using System.Reflection; | |
| using Microsoft.EntityFrameworkCore; | |
| using Microsoft.EntityFrameworkCore.Diagnostics; | |
| using Microsoft.EntityFrameworkCore.Query; | |
| using Microsoft.EntityFrameworkCore.Query.SqlExpressions; | |
| using Microsoft.EntityFrameworkCore.Storage; | |
| using Npgsql.EntityFrameworkCore.PostgreSQL.Query; | |
| using Npgsql.EntityFrameworkCore.PostgreSQL.Query.Expressions.Internal; | |
| using Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.Mapping; | |
| namespace JsonDictsGist | |
| { | |
| public class JsonDictionaryMethodCallTranslator : IMethodCallTranslator | |
| { | |
| private static readonly MethodInfo ContainsKey = typeof(IDictionary<string, object>).GetRuntimeMethod(nameof(IDictionary<string, object>.ContainsKey), new[] { typeof(string) }); | |
| private static readonly MethodInfo JsonGet = typeof(PgsqlFunctions).GetMethod(nameof(PgsqlFunctions.JsonGet)); | |
| private readonly IRelationalTypeMappingSource typeMappingSource; | |
| private readonly NpgsqlSqlExpressionFactory sqlExpressionFactory; | |
| private readonly RelationalTypeMapping stringTypeMapping; | |
| private readonly RelationalTypeMapping jsonbTypeMapping; | |
| public JsonDictionaryMethodCallTranslator(IRelationalTypeMappingSource typeMappingSource, NpgsqlSqlExpressionFactory sqlExpressionFactory) | |
| { | |
| this.typeMappingSource = typeMappingSource; | |
| this.sqlExpressionFactory = sqlExpressionFactory; | |
| stringTypeMapping = typeMappingSource.FindMapping(typeof(string)); | |
| jsonbTypeMapping = typeMappingSource.FindMapping("jsonb"); | |
| } | |
| public SqlExpression Translate(SqlExpression instance, MethodInfo method, IReadOnlyList<SqlExpression> arguments, IDiagnosticsLogger<DbLoggerCategory.Query> logger) | |
| { | |
| if (method.DeclaringType == typeof(IDictionary<string, object>) | |
| && instance.TypeMapping is NpgsqlJsonTypeMapping) | |
| { | |
| if (method == ContainsKey) | |
| { | |
| return sqlExpressionFactory.MakePostgresBinary(PostgresExpressionType.JsonExists, Jsonb(instance), arguments[0]); | |
| } | |
| } | |
| else if (method.DeclaringType == typeof(PgsqlFunctions) | |
| && arguments.Count == 2 | |
| && arguments[0].TypeMapping is NpgsqlJsonTypeMapping instanceMapping) | |
| { | |
| if (method == JsonGet) | |
| { | |
| return sqlExpressionFactory.JsonTraversal(arguments[0], | |
| new[] { arguments[1] }, | |
| true, typeof(string), stringTypeMapping); | |
| } | |
| } | |
| SqlExpression Jsonb(SqlExpression e) => sqlExpressionFactory.ApplyTypeMapping(e, jsonbTypeMapping); | |
| return null; | |
| } | |
| } | |
| } |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| using System.Collections.Generic; | |
| using Microsoft.EntityFrameworkCore.Query; | |
| using Microsoft.EntityFrameworkCore.Storage; | |
| using Npgsql.EntityFrameworkCore.PostgreSQL.Query; | |
| namespace JsonDictsGist | |
| { | |
| public class JsonDictionaryMethodCallTranslatorPlugin : IMethodCallTranslatorPlugin | |
| { | |
| public JsonDictionaryMethodCallTranslatorPlugin(IRelationalTypeMappingSource typeMappingSource, ISqlExpressionFactory sqlExpressionFactory) | |
| { | |
| Translators = new[] | |
| { | |
| new JsonDictionaryMethodCallTranslator(typeMappingSource, (NpgsqlSqlExpressionFactory) sqlExpressionFactory) | |
| }; | |
| } | |
| public IEnumerable<IMethodCallTranslator> Translators { get; } | |
| } | |
| } |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| using System.Collections.Generic; | |
| using Microsoft.EntityFrameworkCore; | |
| namespace JsonDictsGist | |
| { | |
| public class JsonDictsContext : DbContext | |
| { | |
| //public DbSet<Blog> Blogs { get; set; } | |
| //public DbSet<Post> Posts { get; set; } | |
| protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) | |
| => optionsBuilder | |
| .ReplaceService<IRelationalSqlTranslatingExpressionVisitorFactory, CustomNpgsqlSqlTranslatingExpressionVisitorFactory>() | |
| .UseNpgsql("Host=my_host;Database=my_db;Username=my_user;Password=my_pw", | |
| options => options.AddJsonDictionarySupport()); | |
| } | |
| } |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| using System; | |
| using System.Collections.Generic; | |
| using System.Linq; | |
| using Microsoft.EntityFrameworkCore; | |
| using NpgsqlTypes; | |
| namespace JsonDictsGist | |
| { | |
| public static class PgsqlFunctions | |
| { | |
| public static object JsonGet(IDictionary<string, object> json, string key) => throw new NotSupportedException(); | |
| } | |
| } |
Author
Thanks! Yah I got it working, with modifications. Basically I can support any dictionary-like class (regardless of generics), as well as indexers and map it over json operators. I deviated a bit from your code but it got me 85%+ of the way there. So definitely appreciate it!
And yeah, I was actually dealing with a jsonb property that itself contained a subobject represented as a dictionary of Guid keys mapped to yet another object. Worked out great
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
@pinkfloydx33 Oh, it seems that I forgot to take that part out. From what I remember, it's not really needed there and you should be able to simply just pass the methodCall.Arguments[0] to the Expression.Call.
Just to make it more clear: In our use case, we store the extension attributes in the dictionary with GUIDs as their names (keys) and we need to query them by OData ASP.NET Core controllers. Because GUIDs contain hyphens while OData properties can not, we needed a work around - thus we pass the GUID property names to OData with hyphens replaced by underscores, and then replace them back to hyphens here in NormalizeGuidPropertyName, so they make up a proper GUID.