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

Question

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):

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!

Boncho Valkov · Accepted Answer · 2021-01-05 13:19:08Z

16

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));

answered Jan 5, 2021 at 13:19

Boncho Valkov

1,6491 gold badge13 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shay Rojansky · Accepted Answer · 2020-10-16 09:01:58Z

1

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).

answered Oct 16, 2020 at 9:01

Shay Rojansky

17k3 gold badges44 silver badges73 bronze badges

2 Comments

JKL Over a year ago

Hi Shay, I don't see any support for using .Select() on array properties of an object stored in a JSON column. Does that mean I am forced to pull the entire column into client memory in order to map this or am I missing something? Thanks in advance.

Shay Rojansky Over a year ago

@JKL as written above, you also have the option of dropping down to SQL for expressing exactly what you want - while still using EF to materialize the results, and possibly to compose additional LINQ operators.

Han Zhao · Accepted Answer · 2020-10-12 03:24:08Z

0

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.

edited Oct 12, 2020 at 3:24

answered Oct 12, 2020 at 3:13

Han Zhao

2,06210 silver badges12 bronze badges

3 Comments

KuroeKnight Over a year ago

Hi! Thank you for the reply! Yeah I was getting that VisitLambda expression error as well. I can do a ToList() for client-side processing, but was wondering if there was some secret way to do it all server-side. I'll keep digging and looking into the eager loading concept! Thank you!

Shay Rojansky Over a year ago

Note that your first pattern has nothing really to do with lazy loading - it's just a projection (lazy loading is about the query returning, and user access to the returned data triggering further (lazy) fetches of data). You can't do Include on KitchenIntegrations because it's not an EF relationship (e.g. a related table) - it's a JSON array.

Shay Rojansky Over a year ago

Note also that the first ToList can be AsEnumerable, which streams the results instead of populating a list in memory (and projecting from that). This again has nothing to do with eager vs. lazy loading.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related