Entity Framework对NULL值的处理
今天拿Entity Framework改写早期的一个项目,涉及到对NULL值处理的时候,遇到了点问题,就是如何查询数据库中某字段为NULL的记录,写了几个测试用的例子,然后用SQL Server Profiler抓SQL。
测试1:直接使用null
- var clients = ctx.Clients.Where(c => c.ParentGUID == null);
对应的SQL是:
- -- 执行正常
- SELECT
- [Extent1].[GUID] AS [GUID],
- [Extent1].[Name] AS [Name],
- [Extent1].[ParentGUID] AS [ParentGUID]
- FROM [dbo].[Clients] AS [Extent1]
- WHERE [Extent1].[ParentGUID] IS NULL
测试2:使用等于null的变量:
- string parentGuid = null;
- var clients = ctx.Clients.Where(c => c.ParentGUID == parentGuid);
对应的SQL是:
- -- 使用了值为NULL的变量,但是用了等号,所以取不到值了
- 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
测试3:加判断
- string parentGuid = null;
- var clients = ctx.Clients.Where(c => c.ParentGUID == (string.IsNullOrEmpty(parentGuid) ? null : parentGuid));
对应的SQL是:
- -- 无语了,这也整得太复杂了吧,显然得不到正确结果
- 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
测试4:另一种方式加判断
- string parentGuid = null;
- var clients = ctx.Clients.Where(c => string.IsNullOrEmpty(parentGuid) ? c.ParentGUID == null : c.ParentGUID == parentGuid);
对应的SQL是:
- --继续无语,比刚才的还复杂,同样得不到正确结果
- 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
晕倒,看来把Linq的思想直接往EF上套是行不通的,某些情况下差异还是挺大的。