I'm having trouble creating a working query with a predicate that compares the minute-parts of a DateTimeOffset column. I'm getting an exception when I use a custom function mapping.

Here's the situation and what I've tried so far: I have an Article entity with a PostedDate (datetimeoffset) column. The first approach I tried is to use AddMinutes. The documentation states it works with DateTime, so perhaps it does as well with DateTimeOffset. Unfortunately, it fails:

var data = new LinqMetaData( adapter );
var now = DateTimeOffset.UtcNow;
var query = from a in data.Article
                    where a.PostedDate.AddMinutes( 2 ) <= now
                    select a;
var list = query.ToList(); // Doesn't work, AddMinutes is not a recognized function.

This should be no problem, since I can always add a custom function mapping:

public static class DbFunctions
{
    public static int DateDiffInMinutes( DateTimeOffset start, DateTimeOffset end )
    {
        return 0;
    }
}

public class DbFunctionMaps : FunctionMappingStore
{
    public DbFunctionMaps()
        : base()
    {
        this.Add( new FunctionMapping( typeof( DbFunctions ), "DateDiffInMinutes", 2, "DATEDIFF( minute, CAST( {0} AS DateTime ), CAST( {1} AS DateTime ) )" ) );
    }
}

var data = new LinqMetaData( adapter, new DbFunctionMaps() );
var now = DateTimeOffset.UtcNow;
var query = from a in data.Article
                    where DbFunctions.DateDiffInMinutes( a.PostedDate, now ) <= 2
                    select a;
var list = query.ToList();

However, this code throws an exception:

[InvalidCastException: Object must implement IConvertible.]
   System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) +10592293
   System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming) +5286207

[InvalidCastException: Failed to convert parameter value from a DateTimeOffset to a String.]
   System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming) +5285751
   System.Data.SqlClient.SqlParameter.GetCoercedValue() +188
   System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc) +102
   System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters) +201
   System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc) +234
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite) +1325
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +175
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +53
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +134
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +41
   System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +10
   SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) +314

[ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: Failed to convert parameter value from a DateTimeOffset to a String.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.]
   SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) +512
   SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, Boolean allowDuplicates, IEntityFields2 fieldsUsedForQuery) +296
   SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollectionInternal(IEntityCollection2 collectionToFill, IRelationPredicateBucket& filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) +1591
   SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) +164
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteEntityProjection(QueryExpression toExecute) +476
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression) +662
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) +71
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) +62
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute() +50
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() +54
   System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +369
   System.Linq.Enumerable.ToList(IEnumerable`1 source) +58
   LLBLGenCustomMapping.Controllers.HomeController.Index() in D:\Development\Projects\LLBLGenCustomMapping\LLBLGenCustomMapping\Controllers\HomeController.cs:23
   lambda_method(Closure , ControllerBase , Object[] ) +101
   System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +14
   System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters) +211
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +27
   System.Web.Mvc.Async.<>c__DisplayClass42.<BeginInvokeSynchronousActionMethod>b__41() +28
   System.Web.Mvc.Async.<>c__DisplayClass8`1.<BeginSynchronous>b__7(IAsyncResult _) +10
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +57
   System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult) +48
   System.Web.Mvc.Async.<>c__DisplayClass39.<BeginInvokeActionMethodWithFilters>b__33() +57
   System.Web.Mvc.Async.<>c__DisplayClass4f.<InvokeActionMethodFilterAsynchronously>b__49() +223
   System.Web.Mvc.Async.<>c__DisplayClass37.<BeginInvokeActionMethodWithFilters>b__36(IAsyncResult asyncResult) +10
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +57
   System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult) +48
   System.Web.Mvc.Async.<>c__DisplayClass2a.<BeginInvokeAction>b__20() +24
   System.Web.Mvc.Async.<>c__DisplayClass25.<BeginInvokeAction>b__22(IAsyncResult asyncResult) +102
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +57
   System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult asyncResult) +43
   System.Web.Mvc.<>c__DisplayClass1d.<BeginExecuteCore>b__18(IAsyncResult asyncResult) +14
   System.Web.Mvc.Async.<>c__DisplayClass4.<MakeVoidDelegate>b__3(IAsyncResult ar) +23
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +62
   System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) +57
   System.Web.Mvc.Async.<>c__DisplayClass4.<MakeVoidDelegate>b__3(IAsyncResult ar) +23
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +62
   System.Web.Mvc.Controller.EndExecute(IAsyncResult asyncResult) +47
   System.Web.Mvc.Controller.System.Web.Mvc.Async.IAsyncController.EndExecute(IAsyncResult asyncResult) +10
   System.Web.Mvc.<>c__DisplayClass8.<BeginProcessRequest>b__3(IAsyncResult asyncResult) +25
   System.Web.Mvc.Async.<>c__DisplayClass4.<MakeVoidDelegate>b__3(IAsyncResult ar) +23
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +62
   System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +47
   System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +9
   System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +9629708
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +155

The generated query according to the DQE trace:

Query: 
    SELECT 
        [LPLA_1].[Id], 
        [LPLA_1].[Title], 
        [LPLA_1].[PostedDate] 
    FROM 
        [LLBLGenCustomMapping].[dbo].[Article] [LPLA_1]  
    WHERE 
        ( ( ( ( DATEDIFF( minute, CAST( [LPLA_1].[PostedDate] AS DateTime ), CAST( @LO2b3b9352 AS DateTime ) ) <= @LPFA_11))))
Parameter: 
    @LO2b3b9352 : AnsiString. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: "9-10-2012 20:51:50 +00:00".
Parameter: 
    @LPFA_11 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2.

The LINQ expression tree:

Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[LLBLGenCustomMapping.Data.EntityClasses.ArticleEntity]).Where(a => (DateDiffInMinutes(a.PostedDate, value(LLBLGenCustomMapping.Controllers.HomeController+<>c__DisplayClass2).now) <= 2))

The DDL SQL to create the schema (very simple, I've created a test project to isolate the issue):

CREATE TABLE [dbo].[Article](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](max) NOT NULL,
    [PostedDate] [datetimeoffset](7) NOT NULL
    
    PRIMARY KEY ([Id])
)

Details about my environment:

• LLBLGen Pro 2.6 Final (SQL Server Driver build 09132012)
• The webapp is running on .NET 4.0 with the latest LLBLGen Runtime Libraries (build 09132012 )

The exception is logical, because DateTimeOffset does indeed not implement IConvertible, but it's still a pain, and not what I'd expect. Is there a solution for this problem? If you need more information, I'm happy to provide it.

The problem is with the parameter, not with the field. SqlClient tries to convert it to string before send it. I think this happens because the way the DBFunctionCall is evaluated. If you use it directly, it's treated like DateTimeOffset parameter:

from c in metaData.Customer
where c.SomeDateWithOffset < now 

I wonder if you could just call GetDate() in your DBFunction, or pass a normal DateTime to it.

daelmo wrote:

I wonder if you could just call GetDate() in your DBFunction, or pass a normal DateTime to it.

It works if I change the now variable to a normal DateTime; I hadn't occured to me to try that.

So passing a DateTime works. That's fine for now, but in the end I'd rather pass a DateTimeOffset, so if there's a way to do that, that would be great. Otherwise, I'll stick with your solution. (Thanks, by the way )

The problem is inside the DQE, namely in SqlServerSpecificCreator, line 177, the CreateParameter method. This method is called when an expression contains a value, and it's not clear what the type has to be. In v2, we actively tried to find the SqlDbType value this way, and as DateTimeOffset is not a recognized type it defaults to string.

This method could be more clever in that it should simply rely on SqlParameter.Value to determine the DbType to set instead of using the detour with a default type of varchar which might fail. It's however unclear whether that always works, the list of types sqlparameter supports as value is not limitless. Falling back to varchar is then the better option as it then converts to string back/forth which in most cases works (e.g. in the case of UDT's)

In v3 we added DateTimeOffset, but in v2 it's absent from the list of types the method checks for. The main reason is that the code has to work on .NET 2.0 which doesn't contain SqlDbType.DateTimeOffset (that was added later in a service pack). In v3 we could add it because we use a different way to set the parameter's type (via a generic enum retriever/setter which uses a dynamic (generated IL) method, and which works with the type name in a string)

So TL;DR: I can't add it to the v2 code. You can add it to the code however, it requires you to recompile the DQE sourcecode after you added the type manually to the switch/case in the method at line 177.

case "System.DateTimeOffset":
       dbTypeToUse = SqlDbType.DateTimeOffset;
       break;

Load the SQL Server DQE project in vs.net, change the strong key in assembly info, and recompile after you've added the line above. Then reference the DQE dll you created instead of the vanilla one. I don't expect any change in this code in the future as it's in a version which doesn't get many bugfixes anymore.