Calculating with dates and times in query gives NullReferenceException (LS version: 4.0.1103.19500)

Hey Guys,

Lightspeed file version: 4.0.1103.19500

query:

var count = (from r in reservations where (r.DepartureDate - r.ArrivalDate).TotalDays <= i && (r.DepartureDate - r.ArrivalDate).TotalDays >= i - 1 select r).Count();

exception:

[NullReferenceException: Object reference not set to an instance of an object.] ..
( ) +14 ..
( ) +21 ..
( ) +34 ..ƒ( ) +103 Mindscape.LightSpeed.Sql.SelectStatement.ƒ( ) +41 ..ƒ( ) +21 ..() +117 ..ƒ(String , IdentifierExpression , Query ) +614 Mindscape.LightSpeed.UnitOfWork.Calculate(String calculation, IdentifierExpression attribute, Query query) +91 Mindscape.LightSpeed.UnitOfWork.Count(Query query) +90 Mindscape.LightSpeed.Linq.Plan.<>cDisplayClass9.b8() +43 Mindscape.LightSpeed.Linq.Plan.SingleQueryPlan.ChooseCountImplementation(Func1 countByFilteringList, Func2 countAttribute, Func1 countEntities) +332 Mindscape.LightSpeed.Linq.Plan.SingleQueryPlan.ExecuteCountCore(IUnitOfWork unitOfWork) +144 Mindscape.LightSpeed.Linq.Plan.SingleQueryPlan.ExecuteCount(IUnitOfWork unitOfWork) +14 Mindscape.LightSpeed.Linq.Plan.SingleQueryPlan.ExecuteImmediate(IUnitOfWork unitOfWork, Type returnType) +82 Mindscape.LightSpeed.Linq.LinqQueryProvider.Execute(Expression expression) +195 Mindscape.LightSpeed.Linq.LinqQueryProvider.System.Linq.IQueryProvider.Execute(Expression expression) +5 System.Linq.Queryable.Count(IQueryable1 source) +239

questions:

1. Is this something that should work? I can imagine DATEDIFF being used.
2. Is the case clear like this, or do I need to provide an isolated model / test case?
3. Any suggestion to work around this?
4. Any prognosis (if any at all) when this could be working?

As always, thanks a lot for your wise words!

Finest regards, Dennis

Extra information:

query:

var count = (from r in Repository.FindAll() where (r.DepartureDate - r.ArrivalDate) <= TimeSpan.FromDays(1) select r).Count();

exception:

[OverflowException: SqlDbType.Time overflow. Value '1.00:00:00' is out of range. Must be between 00:00:00.0000000 and 23:59:59.9999999.] System.Data.SqlClient.TdsParser.WriteTime(TimeSpan value, Byte scale, Int32 length, TdsParserStateObject stateObj) +940389 System.Data.SqlClient.TdsParser.WriteValue(Object value, MetaType type, Byte scale, Int32 actualLength, Int32 encodingByteSize, Int32 offset, TdsParserStateObject stateObj) +5172926 System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc) +5048 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout) +1012 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout) +162 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +48 System.Data.SqlClient.SqlCommand.ExecuteScalar() +149 .
.ƒ() +39 Mindscape.LightSpeed.Profiling.Interceptor.ExecuteCommand(Func1 baseExecutor, IUnitOfWork unitOfWork, IDbCommand command) +41 ..ƒ(IUnitOfWork , IDbCommand , Func1 ) +609 ..ƒ(IUnitOfWork , IDbCommand ) +139 ..() +294 ..ƒ(String , IdentifierExpression , Query ) +1186 Mindscape.LightSpeed.UnitOfWork.Calculate(String calculation, IdentifierExpression attribute, Query query) +127 Mindscape.LightSpeed.UnitOfWorkBase.Calculate(String calculation, String attribute, Query query) +77 Mindscape.LightSpeed.UnitOfWork.Count(Query query) +86 Mindscape.LightSpeed.Linq.Plan.<>c__DisplayClass9.<ExecuteCountCore>b__8() +71 Mindscape.LightSpeed.Linq.Plan.SingleQueryPlan.ChooseCountImplementation(Func1 countByFilteringList, Func2 countAttribute, Func1 countEntities) +498 Mindscape.LightSpeed.Linq.Plan.SingleQueryPlan.ExecuteCountCore(IUnitOfWork unitOfWork) +217 Mindscape.LightSpeed.Linq.Plan.SingleQueryPlan.ExecuteCount(IUnitOfWork unitOfWork) +60 Mindscape.LightSpeed.Linq.Plan.SingleQueryPlan.ExecuteImmediate(IUnitOfWork unitOfWork, Type returnType) +211 Mindscape.LightSpeed.Linq.LinqQueryProvider.Execute(Expression expression) +321 Mindscape.LightSpeed.Linq.LinqQueryProvider.System.Linq.IQueryProvider.Execute(Expression expression) +46

comments:

It seems we are getting somewhere now. I will try more variations.

Now using 23 hours as limit for the less or equal comparison.

query:

var count = (from r in Repository.FindAll() where (r.DepartureDate - r.ArrivalDate) <= TimeSpan.FromHours(23) select r).Count();

exception:

[SqlException (0x80131904): The data types datetime and time are incompatible in the less than or equal to operator.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1875018 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5196252 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +356 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +897 System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +69 System.Data.SqlClient.SqlDataReader.get_MetaData() +90 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +376 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout) +1012 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout) +162 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +48 System.Data.SqlClient.SqlCommand.ExecuteScalar() +149 .
.ƒ() +13 Mindscape.LightSpeed.Profiling.Interceptor.ExecuteCommand(Func1 baseExecutor, IUnitOfWork unitOfWork, IDbCommand command) +9 ..ƒ(IUnitOfWork , IDbCommand , Func1 ) +457 ..ƒ(IUnitOfWork , IDbCommand ) +71 ..() +202 ..ƒ(String , IdentifierExpression , Query ) +613 Mindscape.LightSpeed.UnitOfWork.Calculate(String calculation, IdentifierExpression attribute, Query query) +92 Mindscape.LightSpeed.UnitOfWork.Count(Query query) +90 Mindscape.LightSpeed.Linq.Plan.<>c__DisplayClass9.<ExecuteCountCore>b__8() +43 Mindscape.LightSpeed.Linq.Plan.SingleQueryPlan.ChooseCountImplementation(Func1 countByFilteringList, Func2 countAttribute, Func1 countEntities) +332 Mindscape.LightSpeed.Linq.Plan.SingleQueryPlan.ExecuteCountCore(IUnitOfWork unitOfWork) +144 Mindscape.LightSpeed.Linq.Plan.SingleQueryPlan.ExecuteCount(IUnitOfWork unitOfWork) +14 Mindscape.LightSpeed.Linq.Plan.SingleQueryPlan.ExecuteImmediate(IUnitOfWork unitOfWork, Type returnType) +82 Mindscape.LightSpeed.Linq.LinqQueryProvider.Execute(Expression expression) +195 Mindscape.LightSpeed.Linq.LinqQueryProvider.System.Linq.IQueryProvider.Execute(Expression expression) +6

query dispatched:

SELECT COUNT(*) FROM [Reservations] WHERE ([Reservations].[DepartureDate] - [Reservations].[ArrivalDate]) <= '23:00:00'

Clearly can't get it to work with LINQ, but here is some information that is hopefully helpful...

Thanks for the info. It looks like that SQL Server datetime/time issue makes the LINQ approach a nonstarter for now, but we will look into improving this. In the meantime, I think you will need to fall back to query objects, which allow you to bypass LINQ typechecking and give SQL Server the data types it needs. The following approach seemed to work for me:

var timeDiff = Entity.Attribute("DepartureDate").Function("-", Entity.Attribute("ArrivalDate"));
var comparand = new DateTime(1, 1, 1, 23, 59, 59);
var results = uow.Find<Reservation>(timeDiff < comparand);

Another approach which doesn't rely on ugly DateTimes but winds up rather messier and more SQL Server-specific is to convert the time difference expression to a time instead of leaving it a datetime, by invoking the SQL Server CONVERT function:

var timeType = new LiteralExpression("time") { EmitInline = true };
var timeDiff = Entity.Attribute("DepartureDate")
                     .Function("-", Entity.Attribute("ArrivalDate"))
                     .Function(1, "CONVERT", timeType);
var comparand = TimeSpan.FromHours(23.99);
var results = uow.Find<Reservation>(timeDiff < comparand);

(See the LightSpeed docs for information about the weird positional Function overload and for the EmitInline incantation.)