Npgsql EF Core @> contains operator with a JSONB column modelled as an owned entity (.ToJson) - Stack Overflow

admin管理员组

文章数量:1389754

I have a DB entity with a JSONB column, modelled as an owned entity with .ToJson(), as recommended by the Npgsql EF Core documentation.

modelBuilder.Entity<Notification>()
  .OwnsMany<NotificationEvent>(e => e.Events, d => { d.ToJson(); });

This generates the correct DDL (jsonb columns) and semantically correct queries. Now I want to make sure my queries use an index. GIN indexes support the @> contains operator. To use the index, I need the generated SQL for a query to look like this:

select ... from "Notification" where "Events" @> '{"EventType":"r"}'::jsonb;

(with the RHS replaced by a parameter, of course)

How do I do this with an EF Core query?

I have tried

1. The naive .Where(n => n.Events.Any(e => e.EventType == "r")). This generates a semantically correct query, but it does not use the @> operator and an EXPLAIN ANALYSE confirms that this translation does not use the GIN index.
2. Defining a user defined function with a custom expression tree translation to generate the desired SQL. .Where(n => myContains(n.Events, new() {EventType = "r"})
   This did not work because my custom function mapping was never used. The LINQ translator would abort before that. As far as I can tell from debugging EF Core/Npgsql EF Core this is because the translator sees the n.Events is technically a relation (even if its an owned relation) which will cause the code to skip right to aggregate-function/subquery territory. From there, my custom function mapping never gets applied.
3. …with both OwnsOne and OwnsMany mappings.

Short of writing custom inline SQL, is there a way to get Npgsql EF Core to emit a @> operator on an owned entity mapping

本文标签： Npgsql EF Core gt contains operator with a JSONB column modelled as an owned entity (ToJson)Stack Overflow