Skip to content

Instantly share code, notes, and snippets.

@martinzima

martinzima/CustomNpgsqlSqlTranslatingExpressionVisitor.cs

Last active May 21, 2025 07:39
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save martinzima/40b61ef3c3a6aa8512017344a713589c to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save martinzima/40b61ef3c3a6aa8512017344a713589c to your computer and use it in GitHub Desktop.
Download ZIP
Extension for IDictionary<string, object> translation support for Npgsql.EntityFrameworkCore.PostgreSQL 5.0.0 (proof of concept). Register by calling AddJsonDictionarySupport().
Raw
CustomNpgsqlSqlTranslatingExpressionVisitor.cs
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);
}
}
}
Raw
CustomNpgsqlSqlTranslatingExpressionVisitorFactory.cs
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);
}
}
Raw
JsonDictionaryDbContextOptionsBuilderExtensions.cs
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;
}
}
}
Raw
JsonDictionaryDbContextOptionsExtension.cs
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 ";
}
}
}
Raw
JsonDictionaryMethodCallTranslator.cs
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;
}
}
}
Raw
JsonDictionaryMethodCallTranslatorPlugin.cs
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; }
}
}
Raw
JsonDictsContext.cs
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());
}
}
Raw
PgsqlFunctions.cs
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();
}
}
@pinkfloydx33
Copy link

pinkfloydx33 commented Dec 26, 2022

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