指出所有Entity Framework < 6.0的建议都会生成一些笨拙的SQL。请参见第二个示例以获得“干净”的解决方法。
荒谬的解决办法
// comparing against this...
Foo item = ...
return DataModel.Foos.FirstOrDefault(o =>
o.ProductID == item.ProductID
// ridiculous < EF 4.5 nullable comparison workaround https://dev59.com/F3RB5IYBdhLWcg3wQFLu
&& item.ProductStyleID.HasValue ? o.ProductStyleID == item.ProductStyleID : o.ProductStyleID == null
&& item.MountingID.HasValue ? o.MountingID == item.MountingID : o.MountingID == null
&& item.FrameID.HasValue ? o.FrameID == item.FrameID : o.FrameID == null
&& o.Width == w
&& o.Height == h
);
在SQL中的结果如下:
SELECT TOP (1) [Extent1].[ID] AS [ID],
[Extent1].[Name] AS [Name],
[Extent1].[DisplayName] AS [DisplayName],
[Extent1].[ProductID] AS [ProductID],
[Extent1].[ProductStyleID] AS [ProductStyleID],
[Extent1].[MountingID] AS [MountingID],
[Extent1].[Width] AS [Width],
[Extent1].[Height] AS [Height],
[Extent1].[FrameID] AS [FrameID],
FROM [dbo].[Foos] AS [Extent1]
WHERE (CASE
WHEN (([Extent1].[ProductID] = 1 )
AND (NULL IS NOT NULL)) THEN
CASE
WHEN ([Extent1].[ProductStyleID] = NULL ) THEN cast(1 as bit)
WHEN ([Extent1].[ProductStyleID] <> NULL ) THEN cast(0 as bit)
END
WHEN (([Extent1].[ProductStyleID] IS NULL)
AND (2 IS NOT NULL)) THEN
CASE
WHEN ([Extent1].[MountingID] = 2 ) THEN cast(1 as bit)
WHEN ([Extent1].[MountingID] <> 2 ) THEN cast(0 as bit)
END
WHEN (([Extent1].[MountingID] IS NULL)
AND (NULL IS NOT NULL)) THEN
CASE
WHEN ([Extent1].[FrameID] = NULL ) THEN cast(1 as bit)
WHEN ([Extent1].[FrameID] <> NULL ) THEN cast(0 as bit)
END
WHEN (([Extent1].[FrameID] IS NULL)
AND ([Extent1].[Width] = 20 )
AND ([Extent1].[Height] = 16 )) THEN cast(1 as bit)
WHEN (NOT (([Extent1].[FrameID] IS NULL)
AND ([Extent1].[Width] = 20 )
AND ([Extent1].[Height] = 16 ))) THEN cast(0 as bit)
END) = 1
不可思议的解决方案
如果你想生成更清晰的SQL,可以使用以下方法:
// outrageous < EF 4.5 nullable comparison workaround https://dev59.com/F3RB5IYBdhLWcg3wQFLu#2541042
Expression<Func<Foo, bool>> filterProductStyle, filterMounting, filterFrame;
if(item.ProductStyleID.HasValue) filterProductStyle = o => o.ProductStyleID == item.ProductStyleID;
else filterProductStyle = o => o.ProductStyleID == null;
if (item.MountingID.HasValue) filterMounting = o => o.MountingID == item.MountingID;
else filterMounting = o => o.MountingID == null;
if (item.FrameID.HasValue) filterFrame = o => o.FrameID == item.FrameID;
else filterFrame = o => o.FrameID == null;
return DataModel.Foos.Where(o =>
o.ProductID == item.ProductID
&& o.Width == w
&& o.Height == h
)
// continue the outrageous workaround for proper sql
.Where(filterProductStyle)
.Where(filterMounting)
.Where(filterFrame)
.FirstOrDefault()
;
能够得到你最初想要的结果:
SELECT TOP (1) [Extent1].[ID] AS [ID],
[Extent1].[Name] AS [Name],
[Extent1].[DisplayName] AS [DisplayName],
[Extent1].[ProductID] AS [ProductID],
[Extent1].[ProductStyleID] AS [ProductStyleID],
[Extent1].[MountingID] AS [MountingID],
[Extent1].[Width] AS [Width],
[Extent1].[Height] AS [Height],
[Extent1].[FrameID] AS [FrameID],
FROM [dbo].[Foos] AS [Extent1]
WHERE ([Extent1].[ProductID] = 1 )
AND ([Extent1].[Width] = 16 )
AND ([Extent1].[Height] = 20 )
AND ([Extent1].[ProductStyleID] IS NULL)
AND ([Extent1].[MountingID] = 2 )
AND ([Extent1].[FrameID] IS NULL)