LINQ TO SQL Null 查詢

LINQ TO SQL   Null 查詢

 

在論壇裡不止一次看到有網友提問關於LINQ NULL查詢的問題了，現以微軟NorthWind 資料庫為例總結一下：

如查詢這樣一句SQL ，用LINQ如何?？

SELECT *FROM [Orders] AS [t0]WHERE ([t0].[ShippedDate]) IS NULL

 

v  方法一:

from o in Orderswhere o.ShippedDate==nullselect o

對應的Lamda運算式為:

Orders   .Where (o => (o.ShippedDate == (DateTime?)null))

對應的SQL語句為:

SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]FROM [Orders] AS [t0]WHERE [t0].[ShippedDate] IS NULL

 

v  方法二:

from o in Orderswhere Nullable<DateTime>.Equals(o.ShippedDate,null)select o

對應的Lamda運算式為:

Orders   .Where (o => Object.Equals (o.ShippedDate, null))

對應的SQL語句為:

SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]FROM [Orders] AS [t0]WHERE [t0].[ShippedDate] IS NULL

 

v  方法三:

from o in Orderswhere !o.ShippedDate.HasValueselect o

對應的Lamda運算式為:

Orders   .Where (o => !(o.ShippedDate.HasValue))

對應的SQL語句為:

SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]FROM [Orders] AS [t0]WHERE NOT ([t0].[ShippedDate] IS NOT NULL)

 

v  方法四:

from o in Orderswhere o.ShippedDate.Value==(DateTime?)nullselect o

對應的Lamda運算式為:

Orders   .Where (o => ((DateTime?)(o.ShippedDate.Value) == (DateTime?)null))

 對應的SQL語句為:

SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]FROM [Orders] AS [t0]WHERE ([t0].[ShippedDate]) IS NULL

 

v  方法五:

from o in Orderswhere  System.Data.Linq.SqlClient.SqlMethods.Equals(o.ShippedDate.Value,null)select o

對應的Lamda運算式為:

Orders    .Where (o => Object.Equals (o.ShippedDate.Value, null))

對應的SQL語句為:

SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]FROM [Orders] AS [t0]WHERE ([t0].[ShippedDate]) IS NULL

以上方法均只在LINQ TO SQL內驗證實現，LINQ TO EF未驗證。