问题描述:

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.

网友答案:

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)))
相关阅读:
Top