How to query against a column that has a JSON Array in Npgsql.EntityFrameworkCore

user_13731870 0
Viewed 8387

Notes:

  • Using Npsql.EntityFrameworkCore.PostgreSQL v3.1.4
  • Using Npgsql v4.1.3.1
  • Using Code-First approach

I have the following table (called Cars): enter image description here

It has two columns:

  • LicenseNumber (type text) (type string in Car.cs model)
  • KitchenIntegrations (type jsonb) (type List in Car.cs) Where Integrations is a List of Integration type.

The Car class looks this: 

public class Car
{
   public string LicenseNumber {get;set;}

   public List<Kitchen> KitchenIntegrations {get;set;}
}

Kitchen.cs looks like this: 

public class Kitchen
{
   public int Id {get;set;}

   public string KitchenName {get;set;}
}

And finally my CarContext.cs looks like this: 

public class CarContext : DbContext
{
   public DbSet<Car> Cars { get; set; }

   public CarContext()
   {
   }

   public CarContext(DbContextOptions<CarContext> options) : base(options)
   {
   }

   protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
   {
      optionsBuilder.UseNpgsql("ConnectionStringGoesHere");
   }

   protected override void OnModelCreating(ModelBuilder modelBuilder)
   {
      modelBuilder.HasDefaultSchema("public");
      
      modelBuilder.Entity<Car>(
                builder =>
                {
                    builder.HasKey(i => i.LicenseNumber);
                    builder.Property(i => i.KitchenIntegrations).HasColumnType("jsonb").IsRequired(false);
                }
            );
        }
    }

In the Cars table I need to grab only the KitchenIntegration that has Id = 1.

I can do this easily in PSQL, but I am having issues when trying to query against a JSON Array.

I tried:

var integrations = context.Cars.Select(i => i.KitchenIntegrations.First(o => o.Id == 1)).ToList();

But get an issue where it can't be translated to SQL/PSQL.

So my question is how does one traverse an array or list of JSON in EntityFrameworkCore? (And if possible to only do it as server-side rather than client-side).

Thank you! Any help is greatly appreciated!

npgsql jsonb entity-framework-core postgresql c#
3 Answers

My 2 cents about it. Let's assume you have a table fixtures with the following structure. (Id, JsonProperty). Let say you have a record in the db looking like this.

1, [{"Name": "Test", "Value": "123"}, {"Name": "Test2", "Value": "pesho"}]
2, [{"Name": "Test", "Value": "321"}, {"Name": "Test2", "Value": "pesho"}]
3, [{"Name": "Test", "Value": "1123"}, {"Name": "Test2", "Value": "pesho"}]

Then using EF Core 3.1 and Npgsql.EntityFrameworkCore.PostgreSQ 3.14 You can do:

    var search = "[{\"Value\": \"123\"}]";
    var result = dbContext.Fixtures
                    .FirstOrDefault(s => EF.Functions.JsonContains(s.JsonProperty, search));
    var search2 = "[{\"Name\": \"Test\"}]";
    var multipleResults = dbContext.Fixtures
                    .Where(s => EF.Functions.JsonContains(s.JsonProperty, search2));
user_41774480

I think it is because of Lazy Loading in Entity Framework Core.

That can be verified with var integrations = context.Cars.Select(c => c.KitchenIntegrations).Select(l => l.First(o => o.Id == 1)).ToList();. It will throw:

When called from 'VisitLambda', rewriting a node of type 'System.Linq.Expressions.ParameterExpression' must return a non-null value of the same type. Alternatively, override 'VisitLambda' and change it to not visit children of this type.

However, I tried Eager Loading with var integrations = context.Cars.Include(c => c.KitchenIntegrations).ToList(); (and with ThenInclude), but it throws error:

Lambda expression used inside Include is not valid.

I have to preload all Cars into memory to make it work:

var integrations = context.Cars
                       .ToList() // Eager load to memory
                       .Select(i => i.KitchenIntegrations.First(o => o.Id == 1))
                       .ToList();

I feel there should be a better (or correct) way to do eager loading.

user_89424140

Translating this to SQL isn't (currently) supported - operations on database JSON columns are limited, see the docs for the list of supported translations.

In this particular case, it's not clear how exactly this could be (efficiently) translated to SQL. See https://github.com/npgsql/efcore.pg/issues/1534 for a similar ask.

You can indeed perform the projection at the client, as suggested by @han-zhao. However, use AsEnumerable to trigger client evaluation rather than ToList:

var integrations = context.Cars
    .AsEnumerable()
    .Select(i => i.KitchenIntegrations.First(o => o.Id == 1))
    .ToList();

This indeed has the disadvantage of downloading lots of unneeded kitchen instances, only to filter them out at the client side. If perf-wise that's problematic, consider using raw SQL (although again, what you want to do isn't trivial).

user_6403250
Related