Error converting LINQ query with TimeSpan? to the list <>?
I am running 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 to convert this output to list:
var qq = q.ToList();
I am getting the error:
An unhandled exception of type 'System.InvalidCastException' occurred in System.Data.dll Additional Information: The specified order is not valid.
I only get this error when I select an attribute O.TradeTime
and it maps to a property with a type TimeSpan?
, which I'm sure is the route of the problem. If I try to select any other attribute in my table, include those mapped to other NULL types like int?
or double?
I don't get an error.
Has anyone encountered this before or can recommend what is the correct way to deal with TimeSpan?
?
This is how it is determined 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; } } . . .
And in SQL-Server:
I've 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 am getting the same error yet
source share
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 beautify TimeSpan this attribute
[Column(CanBeNull = true, DbType = "TIME(7) NULL")] public TimeSpan? TradeTime { //...
Note that the SQL definition contains time(0)
instead TIME(7)
. It is worth checking to see if this will 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 is a list of operations supported and translated into SQL:
https://msdn.microsoft.com/en-us/library/vstudio/bb882662%28v=vs.100%29.aspx
Beware of adding and subtracting:
Although the CLR type System.TimeSpan supports addition and subtraction, the SQL TIME type does not. Because of this, your LINQ to SQL queries will generate errors if they try to compile and subtract when they map 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).
source share