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