Entity Framework joins null join columns

Question

The My Devart Entity Framework provider accepts the following linq for objects.

from p in context.BEAT_CONTACT join cl in context.COMPASS_LOCATIONS on p.GAZETEER_KEY equals cl.GAZETTEER_KEY //on new { bcdid = p.GAZETEER_KEY.Value } //equals new { bcdid = cl.GAZETTEER_KEY.Value } into myRandomlj from rr in myRandomlj.DefaultIfEmpty() 

Note. Are union columns null types in the database and therefore decimal? in c #

Generated SQL:

 FROM NP_TEST.BEAT_CONTACT "Extent1" LEFT OUTER JOIN NOTTS_DW_OWNER.COMPASS_LOCATIONS "Extent2" ON ("Extent1".GAZETEER_KEY = "Extent2".GAZETTEER_KEY) * OR (("Extent1".GAZETEER_KEY IS NULL) * AND ("Extent2".GAZETTEER_KEY IS NULL)) 

Favorite (*) OR and AND add extra seconds to my sql execution. When an expression is placed in a toad (oracle devart ef provider btw) with rem'd elements checked, sql obviously works much faster.

My question is: Are my linq entities due to fault or is something missing? Or is it a Devart EF provider bug?

Update to the question: Hi, as the original creator of this question, I would like to try to get some clarity on this issue, if possible. From LukLed’s comments, “Entity Framework providers work correctly by default and don’t create such SQL conditions. This is not only wrong, but also a huge performance insolence.” I am mostly concerned about the comment "performance hit", this hit is massive, especially as the number of rows rises on either side of the junction. I had to get around this behavior with ExecuteStoreQuery <> or Sproc. That meant no linq, and I had to put on my sql hat to get the job done.

Answer 1

which is fine, it works as expected, additional conditions allow you to join when both are null due to the fact that null = null not true in SQL

Answer 2

@ Ivanov, you're right. The case of two zeros should also be considered, and the highlighted articles do the trick.