2

Sanity check here...

Shouldn't these two linq queries return the same results?

Query #1:

int stateId = 2;

var streets = from s in ctx.tblStreet
              where s.State_Id == stateId
              && s.ParentStreet_Id == (int?)null
              select s;

Query #2:

int stateId = 2;
int? parentStreetId = (int?)null;

var streets = from s in ctx.tblStreet
              where s.State_Id == stateId
              && s.ParentStreet_Id == parentStreetId
              select s;

====== EDIT ==========

The first linq query yields the records that I expect it to, while the second linq query does not. I would expect both of them to yield the same results, but they don't.

====== EDIT #2 ==========

tblStreet definition...

int State_Id not null

int ParentStreet_id null

====== EDIT #3 ==========

The generated sql is different for the two queries, but I would expect it to not matter in this case, because @p1 (parentStreetId) equals (int?)null. Please note that in the queries below I removed all of the select fields and replaced them with an asterisk in order to shorten up the select and just show the where clause.

Query #1:

SELECT * WHERE ([t0].[State_Id] = @p0) AND ([t0].[ParentStreet_Id] IS NULL)

Query #2:

SELECT * WHERE ([t0].[State_Id] = @p0) AND ([t0].[ParentStreet_Id] = @p1)

Jagd
  • 7,169
  • 22
  • 74
  • 107
  • 1
    Can you elaborate? How do the results currently differ? Also shouldn't this `int parentStreetId = (int?)null;` be this `int? parentStreetId = (int?)null;` (notice the ? in the declaration). – Igor Aug 18 '16 at 16:09
  • I just made a mistake during copy & paste of the code. parentStreetId is a nullable int now. – Jagd Aug 18 '16 at 16:14
  • Ok but my other question is still a valid one I think. You did not tag anything else in this question so no idea 1) how the results differ and 2) what is the source to begin with (link to objects, linq to entities with EF, etc). If this is going to the database what might help you figure out what is going on/wrong is to profile the generated queries for each statement and compare them. If this is Linq to Objects then a [Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve) should be possible. – Igor Aug 18 '16 at 16:15
  • Added linq to sql tag.... – Jagd Aug 18 '16 at 16:18
  • How do the results currently differ? – Igor Aug 18 '16 at 16:19
  • Why do you feel the need to cast null to a nullable type? – Seano666 Aug 18 '16 at 16:19
  • 1
    Let me clarify: `How do the results currently differ?` <- I understand that you are observing that the 2 statements produce different results and also that you are expecting them to produce the same result. What I am asking is what is that difference? What is/is not being filtered by one statement that is/isn't being filtered in the other statement? Is the 2nd statement including non-null `ParentStreet_Id` values? Maybe the 1st statement is? An output of relevant/significant data in your question for each statement would be helpful. – Igor Aug 18 '16 at 16:23
  • If I had to hazard a guess, I'd speculate that the first query generates a proper check (parentStreet_ID is null), whereas the second might generate an incorrect comparison (parentStreet_ID = null). This is only a guess. You should be able to capture the generated SQL to confirm. (The method of doing so depends on your provider and version. For certain flavors of EF, it's a method call ToTraceString(). For others, it's a simple ToString()). – Anthony Pegram Aug 18 '16 at 16:29
  • @Igor - The first query returns records where ParentStreet_Id is indeed null in the database. The second query, where the variable parentStreetId is also null, returns no records. – Jagd Aug 18 '16 at 16:31
  • The first query is using constant `null`, so most likely it's translated to the correct `ParentStreet_Id IS NULL` condition. While the second introduces sql parameter and most probably generates something like `ParentStreet_Id = @p1` which doesn't work for `NULL`. EF is fixing that by generating something like `ParentStreet_Id = @p1 OR (ParentStreet_Id IS NULL AND @p1 IS NULL)`. LinqToSql is obsolete technology and might not correctly handle some cases. Check the generated SQL. – Ivan Stoev Aug 18 '16 at 16:43
  • @Ivan - that's what it's looking like to me too, but I'm baffled as to how I've never ran into this problem before over the years. Out of curiosity, why do you say that LinqtoSql is obsolete? I was under the impression that if anything was obsolete it was EF because of how slow it is compared to L2S. – Jagd Aug 18 '16 at 16:47
  • 1
    For why "x = null" and "x is null" produce different results in SQL, see this question: http://stackoverflow.com/questions/9581745/sql-is-null-and-null – Anthony Pegram Aug 18 '16 at 16:48
  • Well, mainly because it's not under development, so issues like this cannot be resolved. For instance, the above was fixed in some of the recent EF versions. – Ivan Stoev Aug 18 '16 at 16:49
  • @Anthony - thanks for the link! – Jagd Aug 18 '16 at 16:52
  • @Ivan - that would explain a little. I've almost always preferred the EF framework up until a few months ago when I ran across benchmarks showing how slow it is compared to nearly every other ORM and data access library out there. Since then I've been using linq to sql quite a bit more. *sigh* just can't win... – Jagd Aug 18 '16 at 16:53
  • @Jagd No problem mate, I understand your point. It's just annoying when you find a bug and you know it won't be fixed. Anyway, take a look at [SET ANSI_NULLS](https://msdn.microsoft.com/en-us/library/ms188048.aspx). May be OFF (thus correctly handling = null) was the default behavior before, now seems like they are taking the other direction. – Ivan Stoev Aug 18 '16 at 16:59
  • @Ivan - tedious... very tedious... makes one begin to think maybe the grass really is greener on the other side. All this has me thinking that maybe it is time to give Dapper a go. – Jagd Aug 18 '16 at 17:09
  • But in Dapper you'll have the same issue. Imagine you use parameter because you are receiving `int? parentStreetId` as argument of your method. In Dapper you should write SQL yourself, so you should take that into account and either use `if` and different SQL, or inside the SQL you write `ParentStreet_Id = @p1 OR (ParentStreet_Id IS NULL AND @p1 IS NULL)` **manually**. But you could do the same in your Linq-To-Sql query - `... && (s.ParentStreet_Id == parentStreetId || (s.ParentStreet_Id == null && parentStreetId == null))`. In both cases it will not be handled automatically for you. – Ivan Stoev Aug 18 '16 at 17:17
  • 1
    @Ivan - I see what you're saying. I guess in a way linq to entities has allowed me to be lazy! Thanks for your help on this. I appreciate it. – Jagd Aug 18 '16 at 17:23

0 Answers0