实体框架中的Nullable int（int？）问题 [英] Nullable int (int?) problem in entity framework

问题描述

让我用一个简单的例子来解释我的问题。





我在mssql中有一个名为Person的表。

我正在使用实体框架5.0



人

Id名称编号

1 Tom 5

2 Mary NULL

3亨利8



数字字段是可以为空的字段。



当我运行时下面的代码，它找到我想要的人玛丽，并向我们显示存在空号码的人！;

 < span class =code-keyword>使用（实体实体=  new 实体（））
 {
  if （entities.Person.Where（p = >  p.Number ==  null ）。Any（））
 {
 MessageBox.Show（ 存在空号码的人！）; 
} 
  else  
 {
 MessageBox.Show（ 空号码的人不存在！）; 
} 
} 

但是当我将代码改为以下时，它不起作用，它返回false并显示我们没有空号码的人不存在！

  INT ？ nullValue =  null ; 
 
 使用（实体实体= 新实体（））
 {
  if （entities.Person.Where（p = >  p.Number == nullValue）.Any（））
 {
 MessageBox.Show（ 存在空号的人！）; 
} 
  else  
 {
 MessageBox.Show（ 空号码的人不存在！）; 
} 
} 

他们之间的差异是什么？

你能来吗帮助我这个？



提前谢谢

解决方案

linq-to必定存在错误 - 版本5.0的实体。我已经用6.1版进行了测试，但我没有观察到这种情况。



这里是代码：

  class 计划
 {
  static   void  Main（ string  [] args）
 {
  int ？ i1 =  null ; 
 
 使用（TESTEntities ctx =  new  TESTEntities（））
 {
 ctx.Database.Log = Console.Write; 
  var  exists = ctx.TABLE_A.Where（e1 = >  e1.A_NUM ==  null ）。Any（）; 
 
  if （存在）
 Console.WriteLine（ < span class =code-string> exists）;  //  打印 
 其他 
 Console.WriteLine（  not exists）; 
 
 Console.ReadKey（）; 
} 
 
 使用（TESTEntities ctx =  new  TESTEntities（） ）
 {
 ctx.Database.Log = Console.Write; 
  var  exists = ctx.TABLE_A.Where（e1 = >  e1.A_NUM == I1）。任何（）; 
 
  if （存在）
 Console.WriteLine（ < span class =code-string> exists）;  //  打印 
 其他 
 Console.WriteLine（  not exists）; 
 
 Console.ReadKey（）; 
} 
 
 Console.ReadKey（）; 
} 
} 

和输出：

打开连接时间为18/06/2014 12:59:32 PM +03：00 
 SELECT 
 CASE WHEN（EXISTS（SELECT 
 1 AS [C1 ] 
 FROM [dbo]。[TABLE_A] AS [Extent1] 
 WHERE [Extent1]。[A_NUM] IS NULL 
））THEN cast（1 as bit）WHEN（NOT EXISTS（SELECT） 
 1 AS [C1] 
 FROM [dbo]。[TABLE_A] AS [Extent2] 
 WHERE [Extent2]。[A_NUM] IS NULL 
））THEN cast（0 as bit）END AS [C1] 
 FROM（SELECT 1 AS X）AS [SingleRowTable1] 
  - 执行时间为18/06/2014 12:59:33 PM +03：00 
  -  - 结果：16 ms完成结果：SqlDataReader 
 
关闭连接时间为18/06/2014 12:59:33 PM +03：00 
存在
在18/06开通连接/ 2014 1:00:37 PM +03：00 
 SELECT 
 CASE WHEN（EXISTS（SELECT 
 1 AS [C1] 
 FROM [dbo]。[TABLE_A] AS [ Extent1] 
 WHERE（[Extent1]。[A_NUM] = @ p__linq__0）OR（（[Ext ent1]。[A_NUM] IS NULL）
 AND（@ p__linq__0 IS NULL））
））然后施放（1作为位）WHEN（不是EXISTS（SELECT 
 1 AS [C1] 
 FROM [dbo]。[TABLE_A] AS [Extent2] 
 WHERE（[Extent2]。[A_NUM] = @ p__linq__0）或（（[Extent2]。[A_NUM] IS NULL）
 AND （@ p__linq__0 IS NULL））
））然后转换（0作为位）END AS [C1] 
 FROM（SELECT 1 AS X）AS [SingleRowTable1] 
  -  p__linq__0：'null '（Type = Int32）
  - 执行时间为18/06/2014 1:00:37 PM +03：00 
  - 在9 ms内完成，结果为：SqlDataReader 
 
关闭连接时间为18/06/2014 1:00:37 PM +03：00 
存在

你可以使用sql profiler或context.Database.Log查看实际查询。它会显示会发生什么。

如下文所述，将ObjectContextOptions.UseCSharpNullComparisonBehavior Property设置为true足以解决问题



http://www.seventy-3.com/entity-framework-and-null - 变量/

Hi,

Let me explain my question in a simple example.


I have a table named "Person" in mssql.
I am using entity framework 5.0

Person
Id Name Number
1 Tom 5
2 Mary NULL
3 Henry 8

"Number" field is a nullable field.

When I run the code below, it finds the person "Mary" as I want and shows us "Person with Null Number exists!";

using (Entities entities = new Entities())
{
	if (entities.Person.Where(p => p.Number == null).Any())
	{
		MessageBox.Show("Person with Null Number exists!");
	}
	else
	{
		MessageBox.Show("Person with Null Number doesn't exist!");
	}
}

but when I change code to below, it doesnot work same, it returns false and shows us "Person with Null Number doesn't exist!"

int? nullValue = null;

using (Entities entities = new Entities())
{
	if (entities.Person.Where(p => p.Number == nullValue).Any())
	{
		MessageBox.Show("Person with Null Number exists!");
	}
	else
	{
		MessageBox.Show("Person with Null Number doesn't exist!");
	}
}

What is the diffence between them?
Could you help me about this?

Thanks in advance

解决方案

There must be a bug in linq-to-entities for version 5.0. I have tested it with version 6.1 and I didn't observe such thing.

here is the code:

class Program
    {
        static void Main(string[] args)
        {
            int? i1 = null;

            using (TESTEntities ctx = new TESTEntities())
            {
                ctx.Database.Log = Console.Write;
                var exists = ctx.TABLE_A.Where(e1 => e1.A_NUM == null).Any();

                if(exists)
                    Console.WriteLine("exists"); // prints
                else
                    Console.WriteLine("not exists");

                Console.ReadKey();
            }

            using (TESTEntities ctx = new TESTEntities())
            {
                ctx.Database.Log = Console.Write;
                var exists = ctx.TABLE_A.Where(e1 => e1.A_NUM == i1).Any();

                if (exists)
                    Console.WriteLine("exists"); // prints
                else
                    Console.WriteLine("not exists");

                Console.ReadKey();
            }

            Console.ReadKey();
        }
    }

and the output:

Opened connection at 18/06/2014 12:59:32 PM +03:00
SELECT
    CASE WHEN ( EXISTS (SELECT
        1 AS [C1]
        FROM [dbo].[TABLE_A] AS [Extent1]
        WHERE [Extent1].[A_NUM] IS NULL
    )) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT
        1 AS [C1]
        FROM [dbo].[TABLE_A] AS [Extent2]
        WHERE [Extent2].[A_NUM] IS NULL
    )) THEN cast(0 as bit) END AS [C1]
    FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
-- Executing at 18/06/2014 12:59:33 PM +03:00
-- Completed in 16 ms with result: SqlDataReader

Closed connection at 18/06/2014 12:59:33 PM +03:00
exists
Opened connection at 18/06/2014 1:00:37 PM +03:00
SELECT
    CASE WHEN ( EXISTS (SELECT
        1 AS [C1]
        FROM [dbo].[TABLE_A] AS [Extent1]
        WHERE ([Extent1].[A_NUM] = @p__linq__0) OR (([Extent1].[A_NUM] IS NULL)
AND (@p__linq__0 IS NULL))
    )) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT
        1 AS [C1]
        FROM [dbo].[TABLE_A] AS [Extent2]
        WHERE ([Extent2].[A_NUM] = @p__linq__0) OR (([Extent2].[A_NUM] IS NULL)
AND (@p__linq__0 IS NULL))
    )) THEN cast(0 as bit) END AS [C1]
    FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
-- p__linq__0: 'null' (Type = Int32)
-- Executing at 18/06/2014 1:00:37 PM +03:00
-- Completed in 9 ms with result: SqlDataReader

Closed connection at 18/06/2014 1:00:37 PM +03:00
exists

you can either use sql profiler or context.Database.Log to see the actual query. it will show what happens.

as mention the article below, setting "ObjectContextOptions.UseCSharpNullComparisonBehavior Property" to true is enough for solution

http://www.seventy-3.com/entity-framework-and-null-variables/

这篇关于实体框架中的Nullable int（int？）问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！