在用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
[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; }
}
}
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.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.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();
}
}
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]
[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'
[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的两个字段的不为空的条件没有了，第二个方法是本人针对第一种方法不足提供的一种解决方案，不知大家有什么看法，怎么解决这一问题的，请不吝赐教！