SQLite - an update on column with DateTimeOffset? throws exception

[djfoxer]

An update on column with DateTimeOffset? throws exception:

Exception message:
Stack trace:
Message: 
    System.ArgumentException : Property 'Int64 Ticks' is not defined for type 'System.Nullable`1[System.DateTimeOffset]' (Parameter 'property')
  Stack Trace: 
    Expression.Property(Expression expression, PropertyInfo property)
    Expression.MakeMemberAccess(Expression expression, MemberInfo member)
    Extensions.TransformX(MemberExpression e, Func`2 func)
    Extensions.Transform(Expression expr, Func`2 func)
    Extensions.TransformX(BinaryExpression e, Func`2 func)
    Extensions.Transform(Expression expr, Func`2 func)
    Extensions.TransformX(BinaryExpression e, Func`2 func)
    Extensions.Transform(Expression expr, Func`2 func)
    Extensions.TransformX(BinaryExpression e, Func`2 func)
    Extensions.Transform(Expression expr, Func`2 func)
    Extensions.GetBody(LambdaExpression lambda, Expression exprToReplaceParameter)
    InternalExtensions.ApplyLambdaToExpression(LambdaExpression convertLambda, Expression expression)
    ColumnDescriptor.ApplyConversions(MappingSchema mappingSchema, Expression getterExpr, DbDataType dbDataType, IValueConverter valueConverter, Boolean includingEnum)
    ColumnDescriptor.ApplyConversions(Expression getterExpr, DbDataType dbDataType, Boolean includingEnum)
    ExpressionBuilder.PrepareConvertersAndCreateParameter(ValueTypeExpression newExpr, Expression valueExpression, String name, ColumnDescriptor columnDescriptor, BuildParameterType buildParameterType)
    ExpressionBuilder.BuildParameterFromArgument(MethodCallExpression methodCall, Int32 argumentIndex, ColumnDescriptor columnDescriptor, BuildParameterType buildParameterType)
    UpdateBuilder.ParseSet(ExpressionBuilder builder, LambdaExpression extract, MethodCallExpression updateMethod, Int32 valueIndex, IBuildContext select, List`1 items)
    Set.BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo)
    MethodCallBuilder.BuildSequence(ExpressionBuilder builder, BuildInfo buildInfo)
    ExpressionBuilder.BuildSequence(BuildInfo buildInfo)
    UpdateBuilder.BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo)
    MethodCallBuilder.BuildSequence(ExpressionBuilder builder, BuildInfo buildInfo)
    ExpressionBuilder.BuildSequence(BuildInfo buildInfo)
    ExpressionBuilder.Build[T]()
    Query`1.CreateQuery(IDataContext dataContext, Expression expr)
    Query`1.GetQuery(IDataContext dataContext, Expression& expr)
    ExpressionQuery`1.GetQuery(Expression& expression, Boolean cache)
    IQueryProvider.Execute[TResult](Expression expression)
    LinqExtensions.Update[T](IUpdatable`1 source)

            dbContext
                .Tables.ToLinqToDBTable()
                .Where(x => x.Id== id)
                .Set(x => x.Date, DateTime.UtcNow)
                .Update();

Environment details

linq2db.EntityFrameworkCore version: 3.6.6 Database Server: Windows Database Provider: SQLite Operating system: W10 .NET Framework: Core 3

[sdanyliv]

Could you try DateTimeOffset.Now?

[djfoxer]

@sdanyliv

Could you try DateTimeOffset.Now?

I have checked with DateTimeOffset.UtcNow and still have same exception.

[romanimm]

Does it work without linq2db? (see the limitations for DateTimeOffset https://docs.microsoft.com/en-us/ef/core/providers/sqlite/limitations#query-limitations )

[sdanyliv]

@romanimm, with linq2db we can define any mapping. Something wrong here with model definition but I have to double check.

@djfoxer, prepare small sample. I do not see your model and conversions.

Also, try this variant:

  dbContext
                .Tables.ToLinqToDBTable()
                .Where(x => x.Id== id)
                .Set(x => x.Date, prev => DateTime.UtcNow)
                .Update();

[djfoxer]

I have more details, it's not working when I use DateTimeOffsetToBinaryConverter. So when I have this code in OnModelCreating EF Core Context:

if (Database.IsSqlite())
            {
                // SQLite does not have proper support for DateTimeOffset via Entity Framework Core, see the limitations
                // here: https://docs.microsoft.com/en-us/ef/core/providers/sqlite/limitations#query-limitations
                // To work around this, when the Sqlite database provider is used, all model properties of type DateTimeOffset
                // use the DateTimeOffsetToBinaryConverter
                // Based on: https://github.com/aspnet/EntityFrameworkCore/issues/10784#issuecomment-415769754
                // This only supports millisecond precision, but should be sufficient for most use cases.
                foreach (var entityType in modelBuilder.Model.GetEntityTypes())
                {
                    var properties = entityType.ClrType.GetProperties().Where(p => p.PropertyType == typeof(DateTimeOffset)
                                                                                || p.PropertyType == typeof(DateTimeOffset?));
                    foreach (var property in properties)
                    {
                        modelBuilder
                            .Entity(entityType.Name)
                            .Property(property.Name)
                            .HasConversion(new DateTimeOffsetToBinaryConverter());
                    }
                }
            }

it fails.