Archive for February 14th, 2011

NULL check results differently in LINQ To SQL (L2S) and LINQ To EF (L2E)

February 14, 2011

Here are some interesting gotchas i would like to share that are all confirmed with @jimwooley and @julielerman

As i learnt that L2S & L2E uses different parser to generate the TSQL, thought of checking them mainly with “null” in SELECT & WHERE clause respectively. Surprisingly, i got great differences and listed them here.

LINQ To SQL

I have used Table “Orders” from DB NorthWind

Example: var result = from ord in Orders select ord.ShipRegion;

In SELECT statement

LINQ SQL Equivalent
select ord.ShipRegion ?? “Lara” SELECT COALESCE([t0].[ShipRegion],@p0) AS [value]
select ord.ShipName == null ? “Lara” : ord.ShipName SELECT (CASEWHEN [t0].[ShipName] IS NULL THEN CONVERT(NVarChar(40),@p0) ELSE [t0].[ShipName] END) AS [value]
select Object.Equals(ord.ShipRegion, “Lara”) SELECT (CASEWHEN ([t0].[ShipRegion] IS NOT NULL) AND ([t0].[ShipRegion] = @p0) THEN 1 WHEN NOT (([t0].[ShipRegion] IS NOT NULL) AND ([t0].[ShipRegion] = @p0)) THEN 0 ELSE NULL END) AS [value]
select Object.Equals(ord.ShipRegion, null) SELECT (CASEWHEN [t0].[ShipRegion] IS NULL THEN 1 ELSE 0 END) AS [value]

In WHERE condition

LINQ SQL Equivalent
where Object.Equals(ord.ShipRegion, null) WHERE [t0].[ShipRegion] IS NULL
where Object.Equals(ord.ShipRegion, “Lara”) WHERE ([t0].[ShipRegion] IS NOT NULL) AND ([t0].[ShipRegion] = @p0)
where String.Equals(or.ShipRegion, “Lara”) WHERE ([t0].[ShipRegion] IS NOT NULL) AND ([t0].[ShipRegion] = @p0)
where ord.ShipRegion == (ord.ShipRegion ?? “Lara”) WHERE [t0].[ShipRegion] = (COALESCE([t0].[ShipRegion],@p0))
where ord.ShipRegion == (ord.ShipRegion == null ? “Lara” : ord.ShipRegion) WHERE [t0].[ShipRegion] = ((CASE WHEN [t0].[ShipRegion] IS NULL THEN CONVERT(NVarChar(15),@p0) ELSE [t0].[ShipRegion] END))

Note: DECLARE @p0 NVarChar(1000) = ‘Lara’ Gotchas:

  • ?? => COALESCE()
  • ? => CASE WHEN….END

LINQ To Entity Framework

Used a custom table: Vendor

In SELECT statement

· select (re.VendorName ?? null);
· select (re.VendorName == null ? 
"N/A" : re.VendorName);
SELECT [Extent1].[VendorName] AS [VendorName]
select (re.VendorName.Length > 0 ? 
re.VendorName : "N/A");
SELECT CASE WHEN ((LEN([Extent1].[VendorName])) > 0) THEN [Extent1].[VendorName] ELSE N’N/A’ END AS [C1]

In WHERE condition

DateTime? dt = null; 

where DateTime.Equals(res.UpdatedDateTime, dt)

  • If dt is “null”

WHERE [Extent1].[UpdatedDateTime] IS NULL

  • If dt is “not null”, the following exception for all primitive types:

Unable to cast the type ‘System.Nullable`1’ to type ‘System.Object’. LINQ to Entities only supports casting Entity Data Model primitive types.

String defaultVal = null;

…….

where Object.Equals(re.VendorName, defaultVal)
WHERE [Extent1].[VendorName] = @p__linq__0
· from re in obj.Vendors
  where Object.Equals(re.VendorName, null)
      select re.VendorId;

· from re in obj.Vendors
  where (re.VendorName.Equals(null))
       select re. VendorId;
If we keep “null” directly, then the following weird TSQL for both the LINQ: SELECT CAST(NULL AS int) AS [C1] FROM  ( SELECT 1 AS X ) AS [SingleRowTable1] WHERE 1 = 0
where re.VendorName.Equals(null) || re.VendorId > 0
Consider only VendorId and ignore the other 😦 WHERE [Extent1].[VendorId] > 0
where (!re.VendorName.Equals(null))
Ignores WHERE keyword in TSQL generation 😦
· where (re.UpdatedDateTime == 
(re.UpdatedDateTime ?? dt))

· where (re.UpdatedDateTime == 
(re.UpdatedDateTime == null ? 
dt : re.UpdatedDateTime))
WHERE [Extent1].[UpdatedDateTime] = (CASE WHEN ([Extent1].[UpdatedDateTime] IS NULL) THEN @p__linq__0 ELSE [Extent1].[UpdatedDateTime] END)

Gotchas:

  • For both ?? and ? => CASE WHEN….END in “where” clause only, but ignored in “select” clause
  • Weird TSQL equivalent with .Equals(null)

If anybody has any special thoughts, please comment 🙂

Advertisements