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

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

5 Comments »

  1. 1
    Murali Thangaraj Says:

    very good article.
    i would like to see a complete article on LINQ

    • 2
      lakswin Says:

      Thanks Murali. As lot of tutorials/examples available in msdn for LINQ, thought of blogging about some twists or chaos over there 🙂 Lets see!

  2. 3
    facebook Says:

    i love it

  3. 4
    Arun Says:

    Awsome Lax, Also i notice with not nullable field, linq does not get converted into sql syntax.it translate as below : Any guess why

    SELECT CAST(NULL AS int) AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable1] WHERE 1 = 0

  4. Itˇ¦s really a nice and helpful piece of information. I am happy that you just shared this helpful information with us. Please stay us informed like this. Thanks for sharing.


RSS Feed for this entry

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: