How to create an Expression predicate from this?

Question

Basic question: How do I "build" a expression predicate based on the following example

e => e.Data.RootElement.GetProperty("pnt_name").GetString()

The context

The following statement is taken from https://www.npgsql.org/efcore/mapping/json.html?tabs=data-annotations%2Cjsondocument

var joes = context.Customers
    .Where(e => e.Customer.RootElement.GetProperty("Name").GetString() == "Joe")
    .ToList();

It explains how to use a JsonDocument mapping and not strongly typed Poco objects.

I am looking for a way to create Expression predicate with the .net Expression API ? I am looking for a way to create a predicate that "looks" up properties on a JsonElement that resides in a Data column in a Postgresql db. I would like to be able to get rows from ObjectSource there the JsonElement has property pnt_name and value ABC123. See the following example of a "compiled" version:

var names = context.ObjectSources
    .Where(e => e.Data.RootElement.GetProperty("pnt_name").GetString() == "ABC123")
    .ToList();

My datamodel looks like this:

  public class ObjectSource
  {

      [Key]
      public string ObjectSourceEId { get; set; }
      public string ObjectSourceIId { get; set; }
      public JsonDocument Data { get; set; }
      public int ObjectSourceEnvironment { get; set; }
      public string ObjectSourceCatalog { get; set; }
      public string ObjectSourceTable { get; set; }
      public string State { get; set; }
      public DateTime CreatedAd { get; set; }
      public DateTime UpdatedAt { get; set; }
  }

The Data field contains JSON data

[
   {"name" : "ABC123"},
   {"name" : "asd123"}
]

This is what I have tried

I am able to get Data property from ObjectSource like this:

var property = Expression.Property(parm, typeof(T).GetProperty("Data"));

parm being the ObjectSource and Data being the field on ObjectSource.

As I understand it I should be able to get the pnt_name like this:

e.Data.RootElement.GetProperty("pnt_name").GetString() == "ABC123")

Note that the pnt_name is NOT a property on Data, but a field in the JSON data that resides as a value on Data.

Just dont know how to "build" the predicate? How to i build up the above predicate in a dynamic fashion?

Here is a picture of the JSON structure and data in the column Data based on the entity ObjectSource. The values are different from the above but it should not matter.

Answer 1

This simple extension method should allow to create such filters.

Usage sample:

var joes = context.Customers
    .FilterInJson(e => e.Customer.RootElement, "Name", "Joe")
    .ToList();

And realisation:

public static class JsonQueryableExtensions
{
    public static IQueryable<TEntity> FilterInJson<TEntity>(this IQueryable<TEntity> query,
        Expression<Func<TEntity, JsonElement>> elementProp, string propName, string value)
    {
        // Let the Compiler to do amount of work by himself
        Expression<Func<JsonElement, string, string, bool>> template = 
            (elementParam, propNameParam, valueParam) => elementParam.GetProperty(propNameParam).GetString() == valueParam;

        var replacements = new List<Expression>
        {
            elementProp.Body, 
            Expression.Constant(propName), 
            Expression.Constant(value),
        };

        // replacing template parameters
        var visitor = new ReplacingExpressionVisitor(template.Parameters, replacements);
        var predicateBody = visitor.Visit(template.Body);

        var predicate = Expression.Lambda<Func<TEntity, bool>>(predicateBody, elementProp.Parameters);

        return query.Where(predicate);
    }
}