dotnet
Query generated when filtering against a nullable owned type property isn't correct
File a bug
I'm seeing unexpected query generation when I try to find any existing matching "Foo" records before inserting one myself.
I have a property "Bar" that is an owned entity. The property is nullable (potentially my first problem, since that obv flattens into cols on the same table, but docs don't talk to any limitation here). I essentially want to confirm that if the "request" has no Bar, that I match a Foo with no Bar, else if I have a Bar, match the properties on that Bar.
What I get for the scenario where request.Bar is null is a weird query where is does an OR on the Bar_* columns, not an AND (to ensure they're all NULL). In practice, this means that if I have a record with Bar_X set, then "request" Foo with no Bar, it'll match because Bar_Y is NULL.
- Please check that the documentation does not explain the behavior you are seeing.
- Docs on owned types don't mention any limitations/bugs when the owned type's property is nullable.
- Please search in both open and closed issues to check that your bug has not already been filed.
- Couldn't see anything relevant.
Include your code
public class MyDbContext : DbContext
{
public DbSet<Foo> Foos { get; set; } = null!;
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<PrescribedServiceSchedule>(entity =>
{
entity.OwnsOne(e => e.Foo);
});
}
}
public class Foo
{
public int Id { get; set; }
public string Name { get; set; }
public Bar? Bar { get; set; }
}
public class Bar
{
public int? X { get; set; }
public int? Y { get; set; }
}
var request = new { Name = "Medicant Bias", Bar = null };
var foo = await _db.Foos
.Where(f=>
f.Name == request.Name &&
(
f.Bar == null && request.Bar == null
||
f.Bar != null && request.Bar != null &&
f.Bar.X == request.Bar.X &&
f.Bar.Y == request.Bar.Y
)
)
.SingleOrDefaultAsync();
Include stack traces
The following query (formatted to highlight the problem more easily) is generated.
SELECT TOP(2) [p].[Id], [p].[Name], [p].[Bar_X], [p].[Bar_Y]
FROM [Foo] AS [p]
WHERE [p].[Name] = @__request_Name_0 AND
(
([p].[Bar_X] IS NULL)
OR
([p].[Bar_Y] IS NULL)
)
Workaround attempts"
If I change the query to the following...
var fooQuery = _db.Foos
.Where(f=>
f.Name == request.Name
);
if (request.Foo == null)
{
fooQuery = fooQuery
.Where(f =>
f.Bar!.X == null &&
f.Bar.Y == null);
}
else
{
fooQuery= fooQuery
.Where(f=>
f.Bar!.X == request.Bar.X&&
f.Bar.Y == request.Bar.Y);
}
var foo = await fooQuery.SingleOrDefaultAsync();
When request.Foo is null I get the following, which does the job but obviously means a bit more code.
SELECT TOP(2) [p].[Id], [p].[Name], [p].[Bar_X], [p].[Bar_Y]
FROM [Foo] AS [p]
WHERE [p].[Name] = @__request_Name_0
AND ([p].[Bar_X] IS NULL)
AND ([p].[Bar_Y] IS NULL)
Include provider and version information
EF Core version: 7.0.0 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 7 Operating system: Win 11 IDE: VS 17.9.6
This has been fixed in 7.0.14. I recommend updating to at least this version, or better to currently latest (in 7.x branch) 7.0.18.
I've just noticed there's a warning message (now being flagged by our test suite which looks for EF Core warning messages) of the following:
The entity type 'Bar' is an optional dependent using table sharing without any required non shared property that could be used to identify whether the entity exists. If all nullable properties contain a null value in database then an object instance won't be created in the query. Add a required property to create instances with null values for other properties or mark the incoming navigation as required to always create an instance.. Exception thrown: .
Potentially that's the issue in that both properties on Bar are nullable? It does sound highly relevant.
I will upgrade EF Core though in any case, thanks for the heads up.
When I get a sec I'll also confirm it got fixed, just under the pump at the moment.
On further investigation, this message doesn't make any sense for our Bar property which is nullable, and which, if both columns it has are nullable, we want it to remain null (no instance created for it).
There's some docs here alluding to an exception for owner type navigation props, but it seems to be referring to required ones again. https://learn.microsoft.com/en-us/ef/core/modeling/relationships/navigations#required-navigations
This warning message is still appearing in 7.0.18. I assume we'll be OK to ignore it?
@benmccallum The warning message was added in 6.0, but updated and fixed for 7.0. Here's a detailed description of the situation: https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-6.0/whatsnew#changes-to-owned-optional-dependent-handling
Whether or not it is okay to ignore the message or not depends on whether or not you are okay with the behavior described, but in general, I would try to change you model if possible so as not to be in this situation.
@cincuranet Okay if we can't find a duplicate ID, but nice if we can.
Thanks @ajcvickers, we're definitely on 7.0.18 now, so it looks like this message is here to stay.
In this case, I can't see how we can avoid this situation in our model design if we want to use an owned entity here, since both X and Y are nullable (though one will always be non-null). We could just make this a true related entity, or just flatten X and Y onto the owner, but the owned entity is nice in this situation for the nesting.
I've covered our uses of this in sufficient tests (writing, querying) to be satisfied that the owned type not being instantiated is actually what we want in our case and everything works as we expect it to.
--
In terms of the query generated, I still haven't had a chance to test if that is correct in 7.0.18, but will next week.