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
- 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 anEXPLAIN ANALYSE
confirms that this translation does not use the GIN index. - 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 then.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. - …with both
OwnsOne
andOwnsMany
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