Entity Framework processing of null values

Today, I used Entity Framework to rewrite an earlier project. When it involved processing null values, I encountered a problem: how to query records with a null field in the database, I wrote several examples for testing and then used SQL Server Profiler to capture SQL.

Test 1: Use null directly

 
  
  


   
   
VaR clients = CTX. Clients. Where (C => C. parentguid = NULL );

  
  
 

The corresponding SQL statement is:

 
  
  


   
   
-- Normal execution

   
   
Select

   
   
[Extent1]. [guid] as [guid],

   
   
[Extent1]. [name] as [name],

   
   
[Extent1]. [parentguid] as [parentguid]

   
   
From [DBO]. [clients] as [extent1]

   
   
Where [extent1]. [parentguid] is null


  
  
 

Test 2: Use a variable equal to NULL:

 
  
  


   
   
String parentguid = NULL;

   
   
VaR clients = CTX. Clients. Where (C => C. parentguid = parentguid );

  
  
 

The corresponding SQL statement is:

  
  


   
   
-- A null variable is used, but an equal sign is used, so the value cannot be obtained.

   
   
Exec sp_executesql n' select

   
   
[Extent1]. [guid] as [guid],

   
   
[Extent1]. [name] as [name],

   
   
[Extent1]. [parentguid] as [parentguid]

   
   
From [DBO]. [clients] as [extent1]

   
   
Where [extent1]. [parentguid] = @ P _ LINQ _ 0', n' @ P _ LINQ _ 0 varchar (8000) ', @ P _ LINQ _ 0 = NULL

  
  
 

Test 3: Add judgment

 
  
  


   
   
String parentguid = NULL;

   
   
VaR clients = CTX. Clients. Where (C => C. parentguid = (string. isnullorempty (parentguid )? Null: parentguid ));

  
  
 

The corresponding SQL statement is:

  
  


   
   
-- Speechless. This is too complicated. Obviously, the correct result is not obtained.

   
   
Exec sp_executesql n' select

   
   
[Extent1]. [guid] as [guid],

   
   
[Extent1]. [name] as [name],

   
   
[Extent1]. [parentguid] as [parentguid]

   
   
From [DBO]. [clients] as [extent1]

   
   
Where [extent1]. [parentguid] = (

   
   
Case

   
   
When (@ P _ LINQ _ 0 is null) or (cast (LEN (@ P _ LINQ _ 0) as INT) = 0 ))

   
   
Then cast (null as varchar (1 ))

   
   
Else @ P _ LINQ _ 1

   
   
End

   
   
) ', N'@ P _ LINQ _ 0 nvarchar (4000), @ P _ LINQ _ 1 nvarchar (4000 )', @ P _ LINQ _ 0 = NULL, @ P _ LINQ _ 1 = NULL

  
  
 

Test 4: Add judgment in another way

  
  


   
   
String parentguid = NULL;

   
   
VaR clients = CTX. Clients. Where (C => string. isnullorempty (parentguid )? C. parentguid = NULL: C. parentguid = parentguid );

  
  
 

The corresponding SQL statement is:

 
 

  
  
 
  
-- Continue to be speechless, which is more complex than the previous one and cannot get the correct results.
 
  
  
 
  
Exec sp_executesql n' select
 
  
  
 
  
[Extent1]. [guid] as [guid],
 
  
  
 
  
[Extent1]. [name] as [name],
 
  
  
 
  
[Extent1]. [parentguid] as [parentguid]
 
  
  
 
  
From [DBO]. [clients] as [extent1]
 
  
  
 
  
Where (
 
  
  
 
  
Case
 
  
  
 
  
When (@ P _ LINQ _ 0 is null) or (cast (LEN (@ P _ LINQ _ 0) as INT) = 0 ))
 
  
  
 
  
Then cast (0 as bit)
 
  
  
 
  
 
  
  

  
When ([extent1]. [parentguid] = @ P _ LINQ _ 1)
 
  
  
 
  
Then cast (1 as bit)
 
  
  
 
  
 
  
  
 
  
When ([extent1]. [parentguid] <> @ P _ LINQ _ 1)
 
  
  
 
  
Then cast (0 as bit)
 
  
  
 
  
End
 
  
  
 
  
) = 1', n' @ P _ LINQ _ 0 nvarchar (4000), @ P _ LINQ _ 1 varchar (8000 )', @ P _ LINQ _ 0 = NULL, @ P _ LINQ _ 1 = NULL
 
 
 

Fainting, it seems that it is impossible to set the idea of LINQ directly to EF. In some cases, the difference is quite large.