EF对应null的处理
原来的代码是
if (string.IsNullOrWhiteSpace(seal)) seal = null;
ctx.Terminal.FirstOrDefault(ent=>ent.Seal==seal)
当seal是null时成长的sql语句如下，采用=null执行结果不是需要的
exec sp_executesql N'SELECT TOP (1) [Extent1].[id] AS [id], [Extent1].[Terminal] AS [Terminal], [Extent1].[Die] AS [Die], [Extent1].[WireGauge] AS [WireGauge], [Extent1].[NormCCH] AS [NormCCH], [Extent1].[ActualCCH] AS [ActualCCH], [Extent1].[NormCCW] AS [NormCCW], [Extent1].[ActualCCW] AS [ActualCCW], [Extent1].[NormICH] AS [NormICH], [Extent1].[ActualICH] AS [ActualICH], [Extent1].[NormICW] AS [NormICW], [Extent1].[ActualICW] AS [ActualICW], [Extent1].[NormPullForce] AS [NormPullForce], [Extent1].[UpPullForce] AS [UpPullForce], [Extent1].[DownPullForce] AS [DownPullForce], [Extent1].[ActualPullForce] AS [ActualPullForce], [Extent1].[WireType] AS [WireType], [Extent1].[Seal] AS [Seal], [Extent1].[Supplier] AS [Supplier], [Extent1].[Project] AS [Project], [Extent1].[StripLength] AS [StripLength], [Extent1].[coreup] AS [coreup], [Extent1].[coredown] AS [coredown], [Extent1].[platicup] AS [platicup], [Extent1].[platicdown] AS [platicdown], [Extent1].[sample] AS [sample], [Extent1].[Tester] AS [Tester], [Extent1].[picture] AS [picture], [Extent1].[testdate] AS [testdate], [Extent1].[date] AS [date], [Extent1].[yj] AS [yj], [Extent1].[standardY1] AS [standardY1], [Extent1].[standardY2] AS [standardY2], [Extent1].[standardY3] AS [standardY3], [Extent1].[standardY4] AS [standardY4], [Extent1].[standardY5] AS [standardY5], [Extent1].[standardY6] AS [standardY6], [Extent1].[standardY7] AS [standardY7], [Extent1].[standardY8] AS [standardY8], [Extent1].[standardY9] AS [standardY9], [Extent1].[standardY10] AS [standardY10], [Extent1].[standardN1] AS [standardN1], [Extent1].[standardN2] AS [standardN2], [Extent1].[standardN3] AS [standardN3], [Extent1].[standardN4] AS [standardN4], [Extent1].[standardN5] AS [standardN5], [Extent1].[standardN6] AS [standardN6], [Extent1].[standardN7] AS [standardN7], [Extent1].[standardN8] AS [standardN8], [Extent1].[standardN9] AS [standardN9], [Extent1].[standardN10] AS [standardN10], [Extent1].[plug] AS [plug], [Extent1].[socket] AS [socket], [Extent1].[remark1] AS [remark1], [Extent1].[remark] AS [remark], [Extent1].[CCHUp] AS [CCHUp], [Extent1].[CCWUp] AS [CCWUp], [Extent1].[ICHUp] AS [ICHUp], [Extent1].[ICWUp] AS [ICWUp], [Extent1].[CCHDown] AS [CCHDown], [Extent1].[CCWDown] AS [CCWDown], [Extent1].[ICHDown] AS [ICHDown], [Extent1].[ICWDown] AS [ICWDown], [Extent1].[CCH] AS [CCH], [Extent1].[CCW] AS [CCW], [Extent1].[ICH] AS [ICH], [Extent1].[ICW] AS [ICW] FROM (SELECT [Terminal].[id] AS [id], [Terminal].[Terminal] AS [Terminal], [Terminal].[Die] AS [Die], [Terminal].[WireGauge] AS [WireGauge], [Terminal].[NormCCH] AS [NormCCH], [Terminal].[ActualCCH] AS [ActualCCH], [Terminal].[NormCCW] AS [NormCCW], [Terminal].[ActualCCW] AS [ActualCCW], [Terminal].[NormICH] AS [NormICH], [Terminal].[ActualICH] AS [ActualICH], [Terminal].[NormICW] AS [NormICW], [Terminal].[ActualICW] AS [ActualICW], [Terminal].[NormPullForce] AS [NormPullForce], [Terminal].[UpPullForce] AS [UpPullForce], [Terminal].[DownPullForce] AS [DownPullForce], [Terminal].[ActualPullForce] AS [ActualPullForce], [Terminal].[WireType] AS [WireType], [Terminal].[Seal] AS [Seal], [Terminal].[Supplier] AS [Supplier], [Terminal].[Project] AS [Project], [Terminal].[StripLength] AS [StripLength], [Terminal].[coreup] AS [coreup], [Terminal].[coredown] AS [coredown], [Terminal].[platicup] AS [platicup], [Terminal].[platicdown] AS [platicdown], [Terminal].[sample] AS [sample], [Terminal].[Tester] AS [Tester], [Terminal].[picture] AS [picture], [Terminal].[testdate] AS [testdate], [Terminal].[date] AS [date], [Terminal].[yj] AS [yj], [Terminal].[standardY1] AS [standardY1], [Terminal].[standardY2] AS [standardY2], [Terminal].[standardY3] AS [standardY3], [Terminal].[standardY4] AS [standardY4], [Terminal].[standardY5] AS [standardY5], [Terminal].[standardY6] AS [standardY6], [Terminal].[standardY7] AS [standardY7], [Terminal].[standardY8] AS [standardY8], [Terminal].[standardY9] AS [standardY9], [Terminal].[standardY10] AS [standardY10], [Terminal].[standardN1] AS [standardN1], [Terminal].[standardN2] AS [standardN2], [Terminal].[standardN3] AS [standardN3], [Terminal].[standardN4] AS [standardN4], [Terminal].[standardN5] AS [standardN5], [Terminal].[standardN6] AS [standardN6], [Terminal].[standardN7] AS [standardN7], [Terminal].[standardN8] AS [standardN8], [Terminal].[standardN9] AS [standardN9], [Terminal].[standardN10] AS [standardN10], [Terminal].[plug] AS [plug], [Terminal].[socket] AS [socket], [Terminal].[remark1] AS [remark1], [Terminal].[remark] AS [remark], [Terminal].[CCHUp] AS [CCHUp], [Terminal].[CCWUp] AS [CCWUp], [Terminal].[ICHUp] AS [ICHUp], [Terminal].[ICWUp] AS [ICWUp], [Terminal].[CCHDown] AS [CCHDown], [Terminal].[CCWDown] AS [CCWDown], [Terminal].[ICHDown] AS [ICHDown], [Terminal].[ICWDown] AS [ICWDown], [Terminal].[CCH] AS [CCH], [Terminal].[CCW] AS [CCW], [Terminal].[ICH] AS [ICH], [Terminal].[ICW] AS [ICW] FROM [dbo].[Terminal] AS [Terminal]) AS [Extent1] WHERE ([Extent1].[Terminal] = @p__linq__0) AND ([Extent1].[WireGauge] = @p__linq__1) AND ([Extent1].[Die] LIKE N''Q%'') AND ([Extent1].[WireType] = @p__linq__2) AND ([Extent1].[Seal] = @p__linq__3)',N'@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000),@p__linq__2 nvarchar(4000),@p__linq__3 nvarchar(4000)',@p__linq__0=N'968221-1',@p__linq__1=N'0.50',@p__linq__2=N'GHB2',@p__linq__3=N'NULL'
换成下面代码后生成的语句变成了Is null 正确匹配了结果
exec sp_executesql N'SELECT TOP (1) [Extent1].[id] AS [id], [Extent1].[Terminal] AS [Terminal], [Extent1].[Die] AS [Die], [Extent1].[WireGauge] AS [WireGauge], [Extent1].[NormCCH] AS [NormCCH], [Extent1].[ActualCCH] AS [ActualCCH], [Extent1].[NormCCW] AS [NormCCW], [Extent1].[ActualCCW] AS [ActualCCW], [Extent1].[NormICH] AS [NormICH], [Extent1].[ActualICH] AS [ActualICH], [Extent1].[NormICW] AS [NormICW], [Extent1].[ActualICW] AS [ActualICW], [Extent1].[NormPullForce] AS [NormPullForce], [Extent1].[UpPullForce] AS [UpPullForce], [Extent1].[DownPullForce] AS [DownPullForce], [Extent1].[ActualPullForce] AS [ActualPullForce], [Extent1].[WireType] AS [WireType], [Extent1].[Seal] AS [Seal], [Extent1].[Supplier] AS [Supplier], [Extent1].[Project] AS [Project], [Extent1].[StripLength] AS [StripLength], [Extent1].[coreup] AS [coreup], [Extent1].[coredown] AS [coredown], [Extent1].[platicup] AS [platicup], [Extent1].[platicdown] AS [platicdown], [Extent1].[sample] AS [sample], [Extent1].[Tester] AS [Tester], [Extent1].[picture] AS [picture], [Extent1].[testdate] AS [testdate], [Extent1].[date] AS [date], [Extent1].[yj] AS [yj], [Extent1].[standardY1] AS [standardY1], [Extent1].[standardY2] AS [standardY2], [Extent1].[standardY3] AS [standardY3], [Extent1].[standardY4] AS [standardY4], [Extent1].[standardY5] AS [standardY5], [Extent1].[standardY6] AS [standardY6], [Extent1].[standardY7] AS [standardY7], [Extent1].[standardY8] AS [standardY8], [Extent1].[standardY9] AS [standardY9], [Extent1].[standardY10] AS [standardY10], [Extent1].[standardN1] AS [standardN1], [Extent1].[standardN2] AS [standardN2], [Extent1].[standardN3] AS [standardN3], [Extent1].[standardN4] AS [standardN4], [Extent1].[standardN5] AS [standardN5], [Extent1].[standardN6] AS [standardN6], [Extent1].[standardN7] AS [standardN7], [Extent1].[standardN8] AS [standardN8], [Extent1].[standardN9] AS [standardN9], [Extent1].[standardN10] AS [standardN10], [Extent1].[plug] AS [plug], [Extent1].[socket] AS [socket], [Extent1].[remark1] AS [remark1], [Extent1].[remark] AS [remark], [Extent1].[CCHUp] AS [CCHUp], [Extent1].[CCWUp] AS [CCWUp], [Extent1].[ICHUp] AS [ICHUp], [Extent1].[ICWUp] AS [ICWUp], [Extent1].[CCHDown] AS [CCHDown], [Extent1].[CCWDown] AS [CCWDown], [Extent1].[ICHDown] AS [ICHDown], [Extent1].[ICWDown] AS [ICWDown], [Extent1].[CCH] AS [CCH], [Extent1].[CCW] AS [CCW], [Extent1].[ICH] AS [ICH], [Extent1].[ICW] AS [ICW] FROM (SELECT [Terminal].[id] AS [id], [Terminal].[Terminal] AS [Terminal], [Terminal].[Die] AS [Die], [Terminal].[WireGauge] AS [WireGauge], [Terminal].[NormCCH] AS [NormCCH], [Terminal].[ActualCCH] AS [ActualCCH], [Terminal].[NormCCW] AS [NormCCW], [Terminal].[ActualCCW] AS [ActualCCW], [Terminal].[NormICH] AS [NormICH], [Terminal].[ActualICH] AS [ActualICH], [Terminal].[NormICW] AS [NormICW], [Terminal].[ActualICW] AS [ActualICW], [Terminal].[NormPullForce] AS [NormPullForce], [Terminal].[UpPullForce] AS [UpPullForce], [Terminal].[DownPullForce] AS [DownPullForce], [Terminal].[ActualPullForce] AS [ActualPullForce], [Terminal].[WireType] AS [WireType], [Terminal].[Seal] AS [Seal], [Terminal].[Supplier] AS [Supplier], [Terminal].[Project] AS [Project], [Terminal].[StripLength] AS [StripLength], [Terminal].[coreup] AS [coreup], [Terminal].[coredown] AS [coredown], [Terminal].[platicup] AS [platicup], [Terminal].[platicdown] AS [platicdown], [Terminal].[sample] AS [sample], [Terminal].[Tester] AS [Tester], [Terminal].[picture] AS [picture], [Terminal].[testdate] AS [testdate], [Terminal].[date] AS [date], [Terminal].[yj] AS [yj], [Terminal].[standardY1] AS [standardY1], [Terminal].[standardY2] AS [standardY2], [Terminal].[standardY3] AS [standardY3], [Terminal].[standardY4] AS [standardY4], [Terminal].[standardY5] AS [standardY5], [Terminal].[standardY6] AS [standardY6], [Terminal].[standardY7] AS [standardY7], [Terminal].[standardY8] AS [standardY8], [Terminal].[standardY9] AS [standardY9], [Terminal].[standardY10] AS [standardY10], [Terminal].[standardN1] AS [standardN1], [Terminal].[standardN2] AS [standardN2], [Terminal].[standardN3] AS [standardN3], [Terminal].[standardN4] AS [standardN4], [Terminal].[standardN5] AS [standardN5], [Terminal].[standardN6] AS [standardN6], [Terminal].[standardN7] AS [standardN7], [Terminal].[standardN8] AS [standardN8], [Terminal].[standardN9] AS [standardN9], [Terminal].[standardN10] AS [standardN10], [Terminal].[plug] AS [plug], [Terminal].[socket] AS [socket], [Terminal].[remark1] AS [remark1], [Terminal].[remark] AS [remark], [Terminal].[CCHUp] AS [CCHUp], [Terminal].[CCWUp] AS [CCWUp], [Terminal].[ICHUp] AS [ICHUp], [Terminal].[ICWUp] AS [ICWUp], [Terminal].[CCHDown] AS [CCHDown], [Terminal].[CCWDown] AS [CCWDown], [Terminal].[ICHDown] AS [ICHDown], [Terminal].[ICWDown] AS [ICWDown], [Terminal].[CCH] AS [CCH], [Terminal].[CCW] AS [CCW], [Terminal].[ICH] AS [ICH], [Terminal].[ICW] AS [ICW] FROM [dbo].[Terminal] AS [Terminal]) AS [Extent1] WHERE ([Extent1].[Terminal] = @p__linq__0) AND ([Extent1].[WireGauge] = @p__linq__1) AND ([Extent1].[WireType] = @p__linq__2) AND ([Extent1].[Die] LIKE N''Q%'') AND ([Extent1].[Seal] IS NULL)',N'@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000),@p__linq__2 nvarchar(4000)',@p__linq__0=N'968221-1',@p__linq__1=N'0.50',@p__linq__2=N'GHB2'
C#代码 需要调用时显式的使用 ctx.Terminal.where(net=>ent.Seal==null) ,才能生成 is null的SQL语句
using (var mfgCtx = com.geelyhd.MFG.EFModel.DBCtx.GetCtx()) { var q = mfgCtx.Terminal.Where(ent => ent.Terminal1 == ter && ent.WireGauge == card.Guage && ent.WireType == card.Wire_kinds); if (terArr[i].CCHType == "30高度") { q = q.Where(ent=> !ent.Die.StartsWith("Q") ); } else //40高度带Q { q = q.Where(ent => ent.Die.StartsWith("Q")); } if (seal == null) { q = q.Where(ent => ent.Seal == null); } else { q = q.Where(ent => ent.Seal == seal); } it = q.FirstOrDefault(); }
