CODEWITHSUNDEEP
c#listlinq-to-sqlnullabletimespan
Dan
Dan

Reputation: 45752

Error converting a LINQ query with a TimeSpan? to a List<>?

I run the following LINQ to SQL query

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

but when I try and convert this output to a list:

var qq = q.ToList();

I get the error:

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

I only get this error when selecting the O.TradeTime attribute and this is mapped to a property with type TimeSpan? and I am sure that this is the route of the problem. If I try select any other attribute in my table, include those mapped to other nullable types such as int? or double? I do not get an error.

Has anyone encountered this before or can recommend what the correct way to deal with a TimeSpan? is?

This is how OptionsTraded is defined:

[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; }
        }
        .
        .
        .

And in SQL-Server:

enter image description here

I have also tried:

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();

but I get the same error still

Upvotes: 3

Views: 996

Answers (1)

Stefan
Stefan

Reputation: 17658

According to this article ( http://blogs.msdn.com/b/sbajaj/archive/2008/05/14/what-s-new-in-linq-to-sql-sp1.aspx ) you should decorate the TimeSpan with this attribute

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

Be advised that your SQL definition contains time(0) instead of TIME(7). It's worth to check if this doesn't lead to any complications.

For other readers: you'll also need:

  • .NET 3.5 SP1 or later
  • MS SQL 2008 Eninge (express or regular)

In addition, here's a list with the operations that are supported and translated to SQL:

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

Beware of the addition and substraction:

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).

Upvotes: 4

Related Questions