Entity Framework join nullable join columns

Question

My Devart Entity Framework provider takes the following linq to entities.

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: The join columns are nullable types in the DB and hence decimal? in c#

The generated SQL is:

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))

The starred (*) OR and AND are adding extra seconds onto the execution of my sql. When the statement is placed into toad (oracle devart ef provider btw) with the starred items rem'd out the sql obviously runs a lot quicker.

My question is: Is my linq to entities at fault or missing something? Or is the fault with the Devart EF provider?

Update to Question: Hello as the original creator of this question i would like to try and get some clarity on the issue if possible. From LukLed's comments - "Default Entity Framework providers work correctly and don't create such SQL conditions. It is not only wrong, it is also a huge performance hitter". I am mainly concerned about the "performance hitter" comment, this hit is massive especially as the number of rows climb in either side of the join. I have had to circumvent this behaviour with ExecuteStoreQuery<> or Sproc. This has meant no linq and i have had to put on my sql hat to get the job done.

@LukLed, this is not a bug. Take into account the situation of two null values in the joined columns. SQL treats two nulls as non-equal values. — Devart
– Devart, Commented Feb 15, 2011 at 12:08
@Devart: I don't agree. That is not expected behaviour. If someone uses this syntax, he expects it to be translated to SQL as it is. If programmist wanted to join by nulls, he would add this condition explicitely, but who really joins by NULL? Default Entity Framework providers work correctly and don't create such SQL conditions. It is not only wrong, it is also huge performance hitter. Someone doesn't understand, that NULL is special value in SQL and shouldn't be treated the same way. — LukLed
– LukLed, Commented Feb 15, 2011 at 12:54
@LukLed, we have just repeated the test for NorthwindEF SQL Server database, the join was performed on two nullable columns, and the same null check was performed. Here is the test LINQ query: var q = from a in db.Employees join b in db.Products on a.CreationDate equals b.CreationDate into n select n.DefaultIfEmpty(); — Devart
– Devart, Commented Feb 15, 2011 at 13:24
@Devart: Hmm, you are right. I checked it earlier, but must have made something wrong. I would argue that this is the best behaviour, but if standard provider does the same, there is no discussion. Thanks for answering. — LukLed
– LukLed, Commented Feb 15, 2011 at 14:34

Kris Ivanov · Accepted Answer · 2011-02-14 17:11:43Z

2

that is perfectly fine, works as expected, the extra conditions make sure join on when both are null due to the fact that null = null is not true in SQL

answered Feb 14, 2011 at 17:11

Kris Ivanov

10.6k2 gold badges26 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Dilbert Over a year ago

Ok Sorry i should have perhaps put on my list of questions at the end. That i do not want the slow response when the OR(( AND )) is cobbled onto the join. I get the same number of rows returned if i rem out the OR statement. I want sub second performance not allowing it to turn the query into two/three and a bit seconds.

Kris Ivanov Over a year ago

what is the select statement, perhaps we can re-write the LINQ statement without join

Dilbert Over a year ago

The purpose of the linq to entity query, is to populate fields from many tables using left joins to allow me to get a flat representation of data into my entity. So you can imagine my select to be select new MyThing { thingID = rr.thingID, thingText = ss.thingText }).toList(); etc the joins bring in the required data just like SQL but without having to have used .Include("everything") or .Load("when i want") i want to write my query almost sql like hence the left outer joins using DefaultIfEmpty. I cannot have linq to entities clip and miss unrelated data such as lookup values.

Kris Ivanov Over a year ago

if you don't have any records that have the join keys with null values, the best is to make those not nullable in the data store

Dilbert Over a year ago

This is in all likely hood the answer i want/need. However database design is fixed for now. As you might imagine i might have a person with a fixed address down to geolocation key or i might have a vague description free text account of where they might live. "If you don't have any records that have the join keys with null values, the best is to make those not nullable in the data store"

|

Devart · Accepted Answer · 2011-02-15 12:08:15Z

1

@K Ivanov, you are correct. The case of two nulls should be taken into account as well, and the starred clauses do the trick.

answered Feb 15, 2011 at 12:08

Devart

120k23 gold badges172 silver badges189 bronze badges

Collectives™ on Stack Overflow

Entity Framework join nullable join columns

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related