LINQ Strange SQL Output

Question

So I with this LINQ query I am getting something pretty strange for the SQL output.

public string GetHeaders(string header,string lec, string state)
{
    string[] states = { "FL", "CA", "IN", "AL", "MI" };
    string[] updatedstateslist = states.Where(x => x != state).ToArray();
    var headers = (from h in db.Headers
                   where h.Description.Contains(header) & h.LEC == lec & !updatedstateslist.Contains(h.State)
                   select new
                   {
                       description = h.Description,
                       sic = h.SIC,
                       yphv = h.YPHV,
                       state = h.State
                   });

The SQL OutPut is coming out with

SELECT 
    1 AS [C1], 
    [Extent1].[Description] AS [Description], 
    [Extent1].[SIC] AS [SIC], 
    [Extent1].[YPHV] AS [YPHV], 
    [Extent1].[State] AS [State]
FROM [dbo].[Headers] AS [Extent1]
WHERE ([Extent1].[Description] LIKE @p__linq__0 ESCAPE N'~') 
  AND (([Extent1].[LEC] = @p__linq__1) OR (([Extent1].[LEC] IS NULL) AND (@p__linq__1 IS NULL))) 
  AND ( NOT (([Extent1].[State] IN (N'FL', N'CA', N'AL', N'MI')) AND ([Extent1].[State] IS NOT NULL)))

the specific section that is strange is (@p__linq__1 IS NULL)

I cannot figure out what part of the LINQ is causing that one little section to appear. If I knew that I could rewrite the Linq to avoid that from happening.

That sure looks like a bind variable, no? – clarkitect May 20 '14 at 23:08 — clarkitect, May 20 '14 at 23:08

score 3 · Accepted Answer · edited May 23 '17 at 11:50

3

There's no problem here, the framework is doing the right thing.

It must convert the C# predicate

h.LEC == lec

into an SQL equivalent.

One interesting quirk of SQL is that the predicate NULL = NULL evaluates to NULL, which, when cast to a boolean, becomes false. (see this question)

So the framework must convert this into an sql predicate that determines if they both have values that are equal, or are both null. If you had to write it by hand, you would end up writing the same thing:

([Extent1].[LEC] = @p__linq__1) 
             OR 
(([Extent1].[LEC] IS NULL) AND (@p__linq__1 IS NULL)))

edited May 23 '17 at 11:50

Community

1
1

answered May 20 '14 at 23:09

Andrew Shepherd

44,254
30
139
205

Adding a note: bind variables are _good_ for DB performance. They allow the engine to execute an already-compiled query because the engine knows the execution plan already; only those variables --of the same datatype-- are changing. If you _avoid_ bind variables you're shooting yourself pretty squarely in the foot as far as performance goes. – clarkitect May 20 '14 at 23:12
If I run this query on the database and remove this section OR (([Extent1].[LEC] IS NULL) AND (@p__linq__1 IS NULL)) I get the results I expect to get. So I am very confused by you saying it is doing things correctly when the results are not coming out the same. I have been working with SQL for quite sometime but admittedly I'm not an expert by any means. That being said could you explain your answer a bit more – scripter78 May 21 '14 at 00:11
@scripter78 - You say that you get the results you expect. But try setting the row value to null, and the query parameter to null, and see if you still get a match. – Andrew Shepherd May 21 '14 at 00:16
Maybe I am not reading your comment right (it's been a real long last 72 hours) but what it seems like your last comment is saying is to add this (([Extent1].[LEC] IS NULL) AND (@p__linq__1 IS NULL))) into the query, which if that is the case and you are suggesting I do so than the answer is no I get no results back. – scripter78 May 21 '14 at 00:25
@scripter78 - What I meant is, for one of your rows, `SET [Extent1].[LEC] = NULL`. Then `SET @p__linq___1 = NULL`. Then run your query with just the predicate `[Extent1].[LEC] = @p__linq__1'. It won't match. – Andrew Shepherd May 21 '14 at 00:28
Okay I think I understand where you're going with this, I am starting my work laptop up right now to test this out – scripter78 May 21 '14 at 00:30
Found my issue and it has nothing to do with the query at all. it has everything to do with what is going into the query. Damn it that royally sucks that I didn't see that before. I thank you for your time and for my own knowledge I am going to look into your answer in hopes I understand the reasoning better. – scripter78 May 21 '14 at 00:46

LINQ Strange SQL Output

1 Answers1