c# - 使用 TimeSpan 转换 LINQ 查询时出错？到列表<>？

Question

我运行以下 LINQ to SQL 查询

var q =
    from O in db.GetTable<OptionsTraded>()
    where O.TradeDate.Date == dtpVolReport.Value.Date
    select new { O.TradeTime };

但是当我尝试将此输出转换为列表时:

var qq = q.ToList();

我得到错误:

An unhandled exception of type 'System.InvalidCastException' occurred in System.Data.dll Additional information: Specified cast is not valid.

我只在选择 O.TradeTime 属性时遇到此错误，并且它映射到类型为 TimeSpan? 的属性，我确信这是问题。如果我尝试在我的表中选择任何其他属性，包括那些映射到其他可空类型的属性，例如 int? 或 double?，我不会收到错误。

有没有人以前遇到过这个或者可以推荐处理 TimeSpan? 的正确方法是什么？

OptionsTraded 是这样定义的:

[Table(Name = "OptionsTraded")]
    public class OptionsTraded
    {
        private DateTime _TradeDate;
        [Column(Storage = "_TradeDate")]
        public DateTime TradeDate
        {
            get { return this._TradeDate; }
            set { this._TradeDate = value; }
        }

        private TimeSpan? _TradeTime;
        [Column(Storage = "_TradeTime")]
        public TimeSpan? TradeTime
        {
            get { return this._TradeTime; }
            set { this._TradeTime = value; }
        }
        .
        .
        .

在 SQL-Server 中:

我也试过:

public class TradeViewModel
{
    public TimeSpan? TradeTime { get; set; }
}

var q =
    from O in db.GetTable<OptionsTraded>()
    where O.TradeDate.Date == dtpVolReport.Value.Date
    select new TradeViewModel {TradeTime = O.TradeTime};

var qq = q.ToList();

但还是报同样的错

Answer 1

根据这篇文章(http://blogs.msdn.com/b/sbajaj/archive/2008/05/14/what-s-new-in-linq-to-sql-sp1.aspx)你应该用这个属性装饰 TimeSpan

[Column(CanBeNull = true, DbType = "TIME(7) NULL")]
public TimeSpan? TradeTime 
{
     //...

请注意，您的 SQL 定义包含 time(0) 而不是 TIME(7)。值得检查一下这是否会导致任何并发症。

对于其他读者:您还需要:

• .NET 3.5 SP1 或更高版本
• MS SQL 2008 Eninge(快速或常规)

此外，这是一个列表，其中包含受支持并已转换为 SQL 的操作:

https://msdn.microsoft.com/en-us/library/vstudio/bb882662%28v=vs.100%29.aspx

注意加法和减法:

Although the CLR System.TimeSpan type does support addition and subtraction, the SQL TIME type does not. Because of this, your LINQ to SQL queries will generate errors if they attempt addition and subtraction when they are mapped to the SQL TIME type. You can find other considerations for working with SQL date and time types in SQL-CLR Type Mapping (LINQ to SQL).