EF4.1中诡异的GUID为空问题

最新推荐文章于 2017-09-04 11:11:00 发布

weixin_33752045

最新推荐文章于 2017-09-04 11:11:00 发布

阅读量162

点赞数

文章标签：数据库 runtime

使用EF4.1时遇到类型为GUID的字段不为空条件丢失的问题。通过两种不同方法对比，探讨了如何正确处理GUID类型的空值检查。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在用EF4.1过程中发现了一个非常诡异的GUID为空问题，不说废话，直接看问题吧：

测试表UserInfo（SQL SERVER 2008 R2）：

这里是建表SQL语句，供有兴趣朋友测试：

CREATE TABLE [dbo].[UserInfo](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserID] [uniqueidentifier] NOT NULL,
    [UserName] [nvarchar](50) NULL,
    [Email] [varchar](50) NOT NULL,
    [PicID] [uniqueidentifier] NULL,
    PRIMARY KEY([ID])
)
GO

UserInfo实体类：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.Serialization;
using System.Data.Entity;
using System.ComponentModel.DataAnnotations;

namespace ConsoleTest
{
    [Serializable]
    [DataContract]
    [Table("UserInfo")]
    public class UserInfo
    {
        [DataMember]
        [Key]
        public int ID { get; set; }

        [DataMember]
        public Guid UserID { get; set; }

        [DataMember]
        public string UserName { get; set; }

        [DataMember]
        public string Email { get; set; }

        [DataMember]
        public Guid PicID { get; set; }
    }
}

EF配置代码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using System.Configuration;

namespace ConsoleTest
{
    public class TestDBContext : DbContext
    {
        public TestDBContext()
            : base("Test")
        {
        }

        public DbSet<UserInfo> UserInfos { get; set; }
    }

}

测试方法：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace ConsoleTest
{
    public class NullTest
    {
        public List<UserInfo> GetUserInfo()
        {
            using (TestDBContext context = new TestDBContext())
            {
                List<UserInfo> users = context.UserInfos
                    .Where(m=>m.UserID!=null)
                    .Where(n=>n.UserName!=null)
                    .Where(p=>p.Email!=null)
                    .Where(q=>q.PicID!=null)
                    .ToList();
                return users;
            }
        }

        public List<UserInfo> GetUserInfoNew()
        {
            using (TestDBContext context = new TestDBContext())
            {
                List<UserInfo> users = context.UserInfos
                    .Where(m => m.UserID != Guid.Empty)
                    .Where(n => n.UserName != null)
                    .Where(p => p.Email != null)
                    .Where(q => q.PicID != new Guid("00000000-0000-0000-0000-000000000000"))
                    .ToList();
                return users;
            }
        }
    }

}

调用：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace ConsoleTest
{
    class Program
    {
        static void Main(string[] args)
        {
            NullTest nullTest = new NullTest();
            nullTest.GetUserInfo();
            nullTest.GetUserInfoNew();
            Console.Read();
        }
    }

}

用SQL SERVER Profiler工具检测到的第一个方法SQL语句：

SELECT
[Extent1].[ID] AS [ID],
[Extent1].[UserID] AS [UserID],
[Extent1].[UserName] AS [UserName],
[Extent1].[Email] AS [Email],
[Extent1].[PicID] AS [PicID]
FROM [dbo].[UserInfo] AS [Extent1]

WHERE ([Extent1].[UserName] IS NOT NULL) AND ([Extent1].[Email] IS NOT NULL)

第二个方法SQL语句：

exec sp_executesql N'SELECT

[Extent1].[ID] AS [ID],
[Extent1].[UserID] AS [UserID],
[Extent1].[UserName] AS [UserName],
[Extent1].[Email] AS [Email],
[Extent1].[PicID] AS [PicID]
FROM [dbo].[UserInfo] AS [Extent1]
WHERE ([Extent1].[UserID] <> @p__linq__0) AND ([Extent1].[UserName] IS NOT NULL) AND ([Extent1].[Email] IS NOT NULL) AND (cast(''00000000-0000-0000-0000-000000000000'' as uniqueidentifier) <> [Extent1].[PicID])',N'@p__linq__0 uniqueidentifier',@p__linq__0='00000000-0000-0000-0000-000000000000'

诡异在第一个方法生成的SQL语句中类型为GUID的两个字段的不为空的条件没有了，第二个方法是本人针对第一种方法不足提供的一种解决方案，不知大家有什么看法，怎么解决这一问题的，请不吝赐教！