System.Data.SqlTypes.SqlNullValueException on fielld allowing Null-Values with complex joins

[ghost]

My EFCore model contains Offers (becoming Invoices on confirmation) and Invoices. And Invoice can have an Offer and both of them have several other relations.

Now I want to query all the Invoices not owning an Offer with their potential Offer joined by the OfferNumber (business case):

var query = (
	from i in context.OrderInvoices
		.Include(p => p.OfferEtikette).ThenInclude(p => p.Supports)
	from o in context.Offers.Where(subo =>
		subo.QuotationNumber == i.Order.OfferNumber
		&& i.OfferEtiketteId == null
	).DefaultIfEmpty()
	select new
	{
		Invoice = i,
		Offer = o
}).Take(1).ToArray();

Iterating this queryable causes an SqlNullValueException! If I do not include the already matched OfferEtikette (Offer 1:n Etikette) or do not include and children of it (.ThenInclude( ...) it works! If I totally remove the && i.OfferEtiketteId == null, it works in any case but increases the query result (actually the Offer has many includes, too. So it doesn't get only increased by one table). Guess that is a bug? Haven't found anything on this issue, yet.

Or is my query maybe malformed? Any optimizations on it?

Further technical details

EF Core version: 3.1.8
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.1
Operating system: Windows 10
IDE: Visual Studio 2019 16.7.3

[ajcvickers]

@tsproesser Can you try on EF Core 5.0 RC1 and if this still fails then please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

[ghost]

Seams to work! Thank you for your advice. Expect the "breaking chances", any reason not to go with RC1 now? Is it just waiting for .Net 5 to be released or are there still known critical bugs?

[ajcvickers]

@tsproesser We're fixing bugs as they are filed. The RC1 is feature complete and has a go-live license, so there's no reason not to use it.

[ghost]

Thank you very much - and great job, btw!

[ghost]

Since EFCore 5.0 seams to have some breaking changes not compatible with my project (https://github.com/dotnet/efcore/issues/22805), is it likely that this patch will make it to EFCore 3.1, too?

[ajcvickers]

@tsproesser Which specific breaking changes are you running into?

[ghost]

Created a running sample in this case:

https://github.com/dotnet/efcore/issues/22805

[ghost]

Sorry, why was this closed? Guess it means there won't be a fix for EF 3.0?

[AndriySvyryd]

@smitpatel How risky would it be to fix this in a patch? Can you think of any workarounds?

[smitpatel]

There is no repro code for this. We don't know what is the root cause of error. From LINQ side, nothing looks obvious which would be indicative of assigning wrong nullability which could cause the error. Further, the only thing which would bring in nullability issues would be DefaultIfEmpty but removal of Include also fixes the issue, means DefaultIfEmpty alone is not a cause.

From our test coverage & generally lack of user reports, basic patterns of this nature - from/from SelectMany or with includes or with DefaultIfEmpty indeed work fine. So this would be an edge case scenario.

There are several fixes made in 5.0 which would affect this

• Improvement to nav example since there is a Take after complex query
• Improvement to DIE
• Improvement to condition identification to convert cross apply to left join if suitable join condition is found
• Better nullability assignments to results being read
  None of above are low risk to be ported to 3.1. Likely the fix for this issue would require one of the complex fix from 5.0 or would be in a place where it would cause it to be high risk to other queries.

Fix may end up being simple and low risk or a work-around can be written if there is original repro code and we can identify what exactly is causing the error.

[ghost]

Sorry it took me a while to answer and thank you for your effort! If you would like to reproduce the error / find a bugfix for EFCore 3 I could try to provide a repo. But since it's that complicated I decided to go with 5.0 and work with a workaround.

You could help me by investigation the issue with 5.0, tough: dotnet/EntityFramework.Docs#2731

Anyway: thank you so much for your help and kind support!