我有以下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.
更多推荐
发布评论