Exact match search does not work in a nested collection that is mapped as JSONB #32993
Comments
|
Note for EF team: this also repros with SqlServer and still fails on the latest daily build. It is not a regression from EF7, at least for SQL Server. Npgsql didn't support EF7 JSON in 7, so also not a regression there. |
|
In Instead, we should be using property-by-property comparison, just like we do for complex types. |
|
@oleg-varlamov on related note, currently we have a problem with JSON (collection) entities that define property named |
I don't necessarily disagree, but note that this starts to applies value equality semantics on owned entities, where we usually have identity/reference semantics (e.g. when assigning). It may start to become quite inconsistent if we start doing value semantics in some places and reference semantics in other places. This also goes to the general question of what we want to do with owned entities going forward. |
Yeah, I thought about it overnight as also realized it would be quite problematic to mix the two. Alternatively we could just block this scenario for JSON mapped owned entities (at least until we implement #28594). This comparison would make much more sense for complex types, when we have the support. |
Even if we had the ID persisted in the JSON, it's still not clear to me what we'd do - would we compare all the properties (including the ID)? If so, that's still value semantics (same discussion as above). Otherwise, if we compare only the ID, then that wouldn't be what the OP (IMHO very reasonably expects) - the Contains would basically ignore all properties except for the ID. IMHO this is quite counterintuitive - and not very useful - but it indeed corresponds to what we currently do elsewhere: // Entity equality to inline entity
_ = await ctx.Blogs.Where(b => b == new Blog { Id = 1, Name = "foo" }).ToListAsync();
// Entity equality to parameterized entity
var blog = new Blog { Id = 1, Name = "foo" };
_ = await ctx.Blogs.Where(b => b == blog).ToListAsync();Result: SELECT [b].[Id], [b].[Name]
FROM [Blogs] AS [b]
WHERE [b].[Id] = 1
Executed DbCommand (15ms) [Parameters=[@__entity_equality_blog_0_Id='1' (Nullable = true)], CommandType='Text', CommandTimeout='30']
SELECT [b].[Id], [b].[Name]
FROM [Blogs] AS [b]
WHERE [b].[Id] = @__entity_equality_blog_0_IdI personally think the above behavior is bad; comparing keys makes a lot of sense when an entity in the database is compared to another entity in the database, but much less so when it's compared to an entity that's not in the database (inline/parameterized). We should probably discuss all this in design... |
|
Re my last comment, the team thinks that this behavior may be a bit unintuitive, but the rule is simple and the behavior is consistent with other places. For example, when an untracked entity instance is passed to DbSet.Delete(), that's interpreted as meaning "delete the entity with that ID", disregarding the others. |
|
note: this also fails for non-json scenarios. We try to generate the following sql: query: var postToSearch = new Post() { Foo = 1, Title = "First" };
var result = ctx.Set<Blog>()
.Where(x => x.Posts.Contains(postToSearch))
.ToList();SELECT b.Id, b.Name, p0.BlogId, p0.Id, p0.Foo, p0.Title
FROM Blogs AS b
LEFT JOIN Post AS p0 ON b.Id == p0.BlogId
WHERE EXISTS (
SELECT 1
FROM Post AS p
WHERE (b.Id == p.BlogId) && ((p.BlogId == @__entity_equality_postToSearch_0_BlogId) && (p.Id == @__entity_equality_postToSearch_0_Id)))
ORDER BY b.Id ASC, p0.BlogId ASCexception: |
Searching for a complete match of the properties of an element in an array that is mapped as jsonb does not work. Expected behavior - an SQL query similar to this is generated:
Include your code
Include stack traces
Include provider and version information
EF Core version: 8.0.1
Database provider: Npgsql.EntityFrameworkCore.PostgreSQL 8.0.0
Target framework: .NET 8.0
Operating system: Windows 11
IDE: Jetbrains Rider 2023.3.3
The text was updated successfully, but these errors were encountered: