4

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.

Dilbert
  • 53
  • 1
  • 8
  • I would say that this is a HUGE bug in provider. – LukLed Feb 14 '11 at 21:44
  • @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 Feb 15 '11 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 Feb 15 '11 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 Feb 15 '11 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 Feb 15 '11 at 14:34
  • 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. – Dilbert Feb 16 '11 at 14:29
  • @Dilbert, if SQL solution is completely inappropriate for you, you might try to add NOT NULL to both columns' definition the JOIN is performed on (of course, if your database data allows such constraining). The OR/AND clause will be removed. – Devart Feb 17 '11 at 12:58

2 Answers2

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

Kris Ivanov
  • 10,476
  • 1
  • 24
  • 35
  • 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. – Dilbert Feb 14 '11 at 17:21
  • what is the select statement, perhaps we can re-write the LINQ statement without join – Kris Ivanov Feb 14 '11 at 17:25
  • 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. – Dilbert Feb 14 '11 at 17:30
  • 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 – Kris Ivanov Feb 14 '11 at 17:34
  • 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" – Dilbert Feb 14 '11 at 17:36
  • I have a requirement that deems it necessary to join data where it might be possible for null values to exist. Is the only way for me to get to the sql: FROM NP_TEST.BEAT_CONTACT "Extent1" LEFT OUTER JOIN NOTTS_DW_OWNER.COMPASS_LOCATIONS "Extent2" ON ("Extent1".GAZETEER_KEY = "Extent2".GAZETTEER_KEY) to make the fields non-nullable? – Dilbert Feb 14 '11 at 17:50
  • if you show the whole linq statement there might be a way to do it without using the join, therefore excluding the extra checks, however that will exclude all matches where both keys are null – Kris Ivanov Feb 14 '11 at 17:55
  • Thank you for your time tonight, i will post a full statement tomorrow illustrating my point. – Dilbert Feb 14 '11 at 18:01
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.

Devart
  • 119,203
  • 23
  • 166
  • 186