在Linq to Sql查询中获取默认值类型(DateTime)，结果为空 [英] Get default value type (DateTime) in Linq to Sql query with empty results
问题描述
我在从复杂的Linq-to-Sql查询返回默认DateTime值时遇到问题.
I am having a problem returning a default DateTime value from a complex Linq-to-Sql query.
希望下面的简化示例显示了该问题(尽管我没有运行此确切的代码):
Hopefully the following simplified example shows the problem (although I haven't run this exact code):
users.Select(u =>
new MyDomainObject(
u.Id,
u.Transactions
.Where(t => false) // empty results set
.Select(t => t.TransactionTime) // TransactionTime is DATETIME NOT NULL
.OrderByDescending(x => x)
.FirstOrDefault() // I want DateTime.MinValue (or SqlDateTime.MinValue)
)
);
所以我需要最后一个时间戳，如果没有结果，则需要一些MinValue时间戳.
So I want the last timestamp, or some MinValue timestamp if there are no results.
枚举以上查询会给出错误
Enumerating the above query gives the error
The null value cannot be assigned to a member with type System.DateTime
更新
好吧，我不确定上面的示例足以说明问题.我认为该错误可能与我尝试在第三个链接表上执行子查询有关.
Ok I'm not sure that my above example was sufficient to illustrate the problem. I believe the error may be something to do with how I'm trying to do a subquery on a third linked table.
下面的示例重新创建确切的错误:
The following example recreates the exact error:
所以我有一辆汽车，我可以带它去机械师那里，有时(但并非总是)由机械师提供服务.
So I have a car, which I can take to the mechanic, which sometimes (but not always) is serviced by the mechanic.
要求是通过查询车表来查找 ，自上次维修以来每辆车进行了多少次机械检查.问题是从未维修过汽车，因此数据如下:
The requirement is to find via a query to the car table how many mechanic visits each car has had since it was last serviced. The issue is when the car has never been serviced, so the data looks like:
Car
-------------
Id: 1
MechanicVisit
-------------
Id: 1
CarId: 1
ServiceRecordId: NULL
VisitDate: 1 Jan 2011
ServiceRecord
-------------
<empty>
一个显示错误的简单示例是查询以获取上次服务时间的列表:
So a simple example that shows the error is a query to get the list of last service times:
var test = _dataContext.GetTable<Car>
.Select(c =>
c.MechanicVisits
.Select(m => m.ServiceRecord)
.Select(s => s.ServiceDate)
.OrderByDescending(d => d)
.FirstOrDefault()
).ToList();
这给出了前面描述的尝试将null分配给非空类型的错误，我需要做的是在日期为null时返回DateTime.MinValue或SqlDateTime.MinValue(因此我可以执行实际的查询是自上次服务以来的机械师造访次数)
This gives the previously described error of trying to assign null to a non-nullable type, where what I need to do is return DateTime.MinValue or SqlDateTime.MinValue when the date is null (so I can do the actual query which is number of mechanic visits since the last service)
解决方案
我使用了乔恩·斯凯特(Jon Skeet)建议的变体，使用了对DateTime?的强制转换和空合并运算符:
I used a variation of what Jon Skeet suggested, using a cast to DateTime? and null coalescing operator:
var test = _dataContext.GetTable<Car>
.Select(c =>
c.MechanicVisits
.Select(m => m.ServiceRecord)
.Select(s => (DateTime?)s.ServiceDate)
.OrderByDescending(d => d)
.FirstOrDefault() ?? new DateTime(1900, 1, 1)
).ToList();
请注意在默认"日期使用了paramatered构造函数-在这里不能使用DateTime.MinValue，因为在转换为SQL时会引发超出范围的异常，并且不能使用SqlDateTime.MinValue.是不可为空的(因此合并运算符变为无效).
Note the use of the paramatered constructor for the "default" date - DateTime.MinValue can't be used here as it throws an out of range exception when converted to SQL, and SqlDateTime.MinValue can't be used as it is non-nullable (so the coalesce operator becomes invalid).
我仍然不太真正理解为什么会发生原始错误，并且此解决方案确实感觉有些棘手，但是我一直无法找到更整齐的修复方法.
I still don't really understand why the original error was occuring and this solution does feel a bit hacky, but I've been unable to find any neater way of fixing it.
推荐答案
我很想实际上为此使用可空的DateTime.例如，从您的汽车"示例中:
I'd be very tempted to actually use a nullable DateTime for this. For example, from your "Car" sample:
var test = _dataContext.GetTable<Car>
.Select(c =>
c.MechanicVisits
.Select(m => m.ServiceRecord)
.Select(s => (DateTime?) s.ServiceDate)
.OrderByDescending(d => d)
.FirstOrDefault()
).ToList();
那样，我怀疑您最终会得到有效的作用，并为您提供空的DateTime?值.如果需要，您以后随时可以进行转换:
That way I suspect you'll end up with it working and giving you null DateTime? values. You could always transform that later if you wanted:
var test = _dataContext.GetTable<Car>
.Select(c =>
c.MechanicVisits
.Select(m => m.ServiceRecord)
.Select(s => (DateTime?) s.ServiceDate)
.OrderByDescending(d => d)
.FirstOrDefault()
).AsEnumerable()
.Select(dt => dt ?? DateTime.MinValue)
.ToList();
原始答案(无效)
嗯.我不会声称完全理解其原因，但是这里有一个可能的解决方法:
Hmm. I won't claim to fully understand the reasons for this, but here's a potential workaround:
users.Select(u =>
new MyDomainObject(
u.Id,
u.Transactions
.Where(t => false)
.Select(t => t.TransactionTime)
.OrderByDescending(x => x)
.DefaultIfEmpty(DateTime.MinValue)
.First()
)
);
换句话说，如果结果集为空，则使用指定的默认值-然后获取现在为绝对非空序列的第一个结果.
In other words, if the result set is empty, use the specified default - and then take the first result of the now-definitely-not-empty sequence.
这篇关于在Linq to Sql查询中获取默认值类型(DateTime)，结果为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
