CODEWITHSUNDEEP
c#linq
scripter78
scripter78

Reputation: 1177

LINQ Strange SQL Output

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.

Upvotes: 1

Views: 304

Answers (1)

Andrew Shepherd
Andrew Shepherd

Reputation: 45222

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

Upvotes: 3

Related Questions