如果使用null检查LINQ2SQL不返回行(LINQ2SQL doesn't return row if checking with null)

我有以下LINQ2SQL查询：

var map = dbContext.TCPDriverMappings.FirstOrDefault( c => c.DriverFacilityId == tcpDms.FacilityId && c.DriverControlledParameterId == controlledParamId && c.DriverValue == value);

所有类型都是字符串。

在我的数据库中，我有一行，必须通过查询返回。

当value="0", controlledParamId =null and FacilityId ="abc"此查询返回null，但是当我写下面的内容时：

var test = dbContext.TCPDriverMappings.FirstOrDefault( c => c.DriverFacilityId == "abc" && c.DriverControlledParameterId == null && c.DriverValue == "0");

测试不是null

我究竟做错了什么？

PS我也试过c.DriverControlledParameterId.Equals(controlledParamId)但它也不起作用。

I have following LINQ2SQL Query:

var map = dbContext.TCPDriverMappings.FirstOrDefault( c => c.DriverFacilityId == tcpDms.FacilityId && c.DriverControlledParameterId == controlledParamId && c.DriverValue == value);

All the types are string.

In my DB i have a row, which must be returned by query.

When value="0", controlledParamId =null and FacilityId ="abc" this query returns null, but when i wrote following:

var test = dbContext.TCPDriverMappings.FirstOrDefault( c => c.DriverFacilityId == "abc" && c.DriverControlledParameterId == null && c.DriverValue == "0");

test was not null

What am i doing wrong?

P.S. I also tried c.DriverControlledParameterId.Equals(controlledParamId) but it also doesn't work.

最满意答案

问题是，LINQ2SQL对表达式c.DriverControlledParameterId == null进行了特殊处理。它被转换为SQL DriverControlledParameterId IS NULL 。但是c.DriverControlledParameterId = controlledParamId被转换为SQL DriverControlledParameterId = :p1 ，即使controlledParamId为null 。并且在SQL DriverControlledParameterId = NULL是未定义的，因此永远不会为TRUE 。

如何修复 ：具体处理null案例：

TCPDriverMapping test; if(controlledParamId == null) test = dbContext.TCPDriverMappings.FirstOrDefault( c => c.DriverFacilityId == "abc" && c.DriverControlledParameterId == null && c.DriverValue == "0"); else test = dbContext.TCPDriverMappings.FirstOrDefault( c => c.DriverFacilityId == "abc" && c.DriverControlledParameterId == controlledParamId && c.DriverValue == "0");

或者像这样：

var test = dbContext.TCPDriverMappings.FirstOrDefault( c => c.DriverFacilityId == "abc" && ((controlledParamId == null && c.DriverControlledParameterId == null) || c.DriverControlledParameterId == controlledParamId) && c.DriverValue == "0");

或者像这样：

IQueryable<TCPDriverMapping> query = dbContext.TCPDriverMappings.Where(c => c.DriverFacilityId == "abc" && c.DriverValue == "0"); if(controlledParamId == null) query = query.Where(c => c.DriverControlledParameterId == null); else query = query.Where(c => c.DriverControlledParameterId == controlledParamId); var test = query.FirstOrDefault();

我会使用第三种选择。在我看来，这比选项2更具可读性，并且没有像第一个那样重复的代码。

The problem is, that LINQ2SQL has a special handling for the expression c.DriverControlledParameterId == null. It is translated to the SQL DriverControlledParameterId IS NULL. But c.DriverControlledParameterId = controlledParamId is translated to the SQL DriverControlledParameterId = :p1, even when controlledParamId is null. And in SQL DriverControlledParameterId = NULL is undefined and as such never TRUE.

How to fix: Handle the null case specifically:

TCPDriverMapping test; if(controlledParamId == null) test = dbContext.TCPDriverMappings.FirstOrDefault( c => c.DriverFacilityId == "abc" && c.DriverControlledParameterId == null && c.DriverValue == "0"); else test = dbContext.TCPDriverMappings.FirstOrDefault( c => c.DriverFacilityId == "abc" && c.DriverControlledParameterId == controlledParamId && c.DriverValue == "0");

Or like this:

var test = dbContext.TCPDriverMappings.FirstOrDefault( c => c.DriverFacilityId == "abc" && ((controlledParamId == null && c.DriverControlledParameterId == null) || c.DriverControlledParameterId == controlledParamId) && c.DriverValue == "0");

Or like this:

IQueryable<TCPDriverMapping> query = dbContext.TCPDriverMappings.Where(c => c.DriverFacilityId == "abc" && c.DriverValue == "0"); if(controlledParamId == null) query = query.Where(c => c.DriverControlledParameterId == null); else query = query.Where(c => c.DriverControlledParameterId == controlledParamId); var test = query.FirstOrDefault();

That third option is what I would use. In my opinion, this is the more readable than option 2 and has no repeated code like the first one.

更多推荐