0

Is there a way for Entity Framework to respect the "UseDatabaseNullSemantics" option in a left join?

Basically, I'd like to prevent the addition of "OR (([ExtentX].[Property] IS NULL) AND ([ExtentY].[Property] Is Null))" that gets added to the generated SQL query. I've seen the following discussions, but none of them provide a solution that will work for my query:

UseDatabaseNullSemantics still generating NULL checks

Why is EF generating SQL queries with unnecessary null-checks?

Prevent NULL checks in LINQ to Entity Joins

It has to be a Left Join... I cannot put the join condition in a where clause because it does not produce the same results, and I need to allow null data if there are no matches (hence the left join).

Basically, I have a query similar to:

 (from c in dbContext.CPUs 
 join u in dbContext.UData on c.User equals u.User into ud 
 from uds in ud.DefaultIfEmpty()
 select new ResultOjbect{});

And it generates this nasty SQL:

SELECT BLAH FROM CpuTable LEFT OUTER JOIN UserTable (ON CPUTable.User = UserTable.User) 
OR ((CpuTable.User IS NULL) AND (UserTable.User IS NULL))

The addition of OR ((CpuTable.User IS NULL) AND (UserTable.User IS NULL)) causes horrible performance and makes the query take 2 minutes for 17000 records. If you remove this from the generated SQL, it takes 23 seconds to load 17000+ records.

Why is there no option to control this? This is incredibly unnecessary and slows my query to a crawl. Anyone know a work-around?

OwN
  • 1,248
  • 12
  • 17
  • *"Why is there no option to control this?"* - rhere is no need of option, it's a bug. *"Anyone know a work-around?"* - yes, the accepted answer of the duplicate, e.g. `from u in dbContext.UData.Where(u => c.User == u.User).DefaultIfEmpty()` translates to `LEFT JOIN`. – Ivan Stoev Dec 04 '18 at 20:52
  • You didn't understand the question, so how can you mark it as a duplicate? It's a left join, but the query it produces leads to horrible performance because of those unnecessary nulls in the join clause. Please re-read. This should not be marked as a duplicate – OwN Dec 04 '18 at 20:54
  • I did read it and it is a duplicate. The accepted answer uses the alternative `left join` syntax (also shown in my previous comment adjusted for your scenario) which does not have unnecessary null checks (of course when `UseDatabaseNullSemantics` is set to `true`). No need offense, just try it. – Ivan Stoev Dec 04 '18 at 20:58
  • Yeah, so how do I select those properties in my result object? – OwN Dec 04 '18 at 20:59
  • The same way you do with your current query. To be crystal clear, *replace* `join u in dbContext.UData on c.User equals u.User into ud from uds in ud.DefaultIfEmpty()` with `from uds in dbContext.UData.Where(u => c.User == u.User).DefaultIfEmpty()`. Ok? ` – Ivan Stoev Dec 04 '18 at 21:02
  • You're right. That appears to work. Thanks for your help! So what I was doing before was incorrect and did produce the correct SQL for the way I had written it in query syntax, or is it a bug? – OwN Dec 04 '18 at 21:09
  • You are welcome. And you are doing nothing wrong, it's a EF bug :( and this is the workaround. – Ivan Stoev Dec 04 '18 at 21:10

0 Answers0