linq2db.EntityFrameworkCore
linq2db.EntityFrameworkCore copied to clipboard
linq2db
Linq2Db + EF Core 6 + Npgsql 6 + Npgsql.NodaTime = DateTime kind gets lost... Sometimes?!
This is a weird issue which I am unable to reproduce on a smaller example (surprisingly, it seems to work just fine when attempting to create a one-file repro - hence the "Sometimes").
Sometimes, when using Linq2Db with EF Core 6, Npgsql 6 and Npgsql.NodaTime, but still using the good old DateTime types, the DateTime.Kind gets reset from Utc to Unspecified. Which unfortunately leads to an exception in Npgsql in a simple query like that:
MappingSchema.Default.SetConverter<NodaTime.Instant, DateTime>(timeStamp =>
timeStamp.ToDateTimeUtc());
public class Position
{
// whatever
public DateTime? EndDate {get; set;}
}
var result = await (from pos in _ctx.Positions
where pos.EndDate > DateTime.UtcNow
select pos).ToArrayAsyncLinqToDb();
'System.InvalidCastException: Cannot write DateTime with Kind=Unspecified to PostgreSQL type 'timestamp with time zone', only UTC is supported. Note that it's not possible to mix DateTimes with different Kinds in an array/range. See the Npgsql.EnableLegacyTimestampBehavior AppContext switch to enable legacy behavior.
at Npgsql.Internal.TypeHandlers.DateTimeHandlers.TimestampTzHandler.ValidateAndGetLength(DateTime value, NpgsqlParameter parameter)
at Npgsql.NodaTime.Internal.TimestampTzHandler.Npgsql.Internal.TypeHandling.INpgsqlSimpleTypeHandler<System.DateTime>.ValidateAndGetLength(DateTime value, NpgsqlParameter parameter)
at Npgsql.NodaTime.Internal.TimestampTzHandler.ValidateObjectAndGetLength(Object value, NpgsqlLengthCache& lengthCache, NpgsqlParameter parameter)
at Npgsql.NpgsqlParameter.ValidateAndGetLength()
at Npgsql.NpgsqlParameterCollection.ValidateAndBind(ConnectorTypeMapper typeMapper)
at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
at LinqToDB.Data.DataConnection.ExecuteReaderAsync(CommandBehavior commandBehavior, CancellationToken cancellationToken)
Now again, the weirdest thing is that on the repro it does work, but not on my main project with migrations and tons of stuff, and after experimenting (a lot) I just can't seem to find the cause!
This bug really made me crazy, and the actual query is a lot more complicated, so I can't just fallback to EF Core, but on MY context even this simple one fails - and inspecting the actual query parameters - I see that for some reason the DateTime.Kind changed from Utc to Unspecified, and I have no ideas why!
The problem is that from Npgsql 6 they changed the mappings and now enforce the Kind to be Utc, so that's a deal breaker, and I can't fallback to the legacy option too anymore.
And yes, I had to mix up NodaTime with DateTime and honestly, I regret it deeply, but I can't change it at this stage - it's way too late for that.
I doubt it is reset somewhere inside of linq2db, at least I cannot remember any place like that right now. Could you try to define DateTime to DataParameter conversion in mapping schema with Kind update to see if it helps?
Unfortunately, that:
MappingSchema.Default.SetConverter<DateTime, DataParameter>(dateTime =>
{
return new DataParameter
{
Value = DateTime.SpecifyKind(dateTime, DateTimeKind.Utc),
DataType = LinqToDB.DataType.DateTime
};
});
didn't help at all... I placed a breakpoint in there, it just didn't got called at all.
@sdanyliv looks to me that MappingSchema.Default conversions ignored due to conversions imported to actual mapping schema from ef.core here https://github.com/linq2db/linq2db.EntityFrameworkCore/blob/aeafee47e477329417d309b2af15246469d326ad/Source/LinqToDB.EntityFrameworkCore/LinqToDBForEFToolsImplDefault.cs#L426
@Evengard you probably need to subclass LinqToDBForEFToolsImplDefault to set your conversions in CreateMappingSchema override, but maybe @sdanyliv has better solution
After further testing the whatever is happening makes to me even less sense. See, importing the SAME project to my repro project and initializing a context manually - works well! No Kind is lost. It fails only if initialized from my AspNet Core application.
I really can't understand what is going on.
Actually, I might have an idea as what is going on. See, I'm actually using an EF Core based context for PostgreSql, and a T4 model based MySql one strictly in Linq2Db for reading purposes. It seems to actually somehow conflict with each other, because when using only the EF Core one I have no problem, but when both of them are injected, it seems to start to bug out. Probably some conversions going wrong.
Yep, I seem to have a kind of a repro:
Repro
using DataModel;
using LinqToDB.Configuration;
using LinqToDB.EntityFrameworkCore;
using LinqToDB.Mapping;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using System;
using System.ComponentModel.DataAnnotations;
using System.Threading.Tasks;
namespace L2DbBugRepro
{
class Program
{
static async Task Main(string[] args)
{
LinqToDBForEFTools.Initialize();
MappingSchema.Default.SetConverter<NodaTime.Instant, DateTime>(timeStamp =>
timeStamp.ToDateTimeUtc());
await using (var ctx = new BlogContext())
{
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();
ctx.Facts.Add(new Fact
{
FactId = Guid.NewGuid(),
Date = null,
});
await ctx.SaveChangesAsync();
}
// The MysqlDB is generated using the default CopyMe.Mysql.tt.txt template
var mysqlctx = new MysqlDB(new LinqToDbConnectionOptionsBuilder()
.UseMySql("Server=localhost;Port=3306;Database=mysqldb;Uid=mysql;Pwd=mysql;")
.Build());
await using (var ctx = new BlogContext())
{
var facts = await (from f in ctx.Facts.AsQueryable()
where f.Date == null || f.Date < DateTime.UtcNow
select f)
.ToArrayAsyncLinqToDB(); // Here we crash
}
}
}
public class BlogContext : DbContext
{
public DbSet<Fact> Facts { get; set; }
static ILoggerFactory ContextLoggerFactory
=> LoggerFactory.Create(b => b.AddConsole().AddFilter("", LogLevel.Information));
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder
.UseNpgsql(@"Host=localhost;Username=root;Password=rootpass;Database=Testing", n => n.UseNodaTime())
.EnableSensitiveDataLogging()
.UseLoggerFactory(ContextLoggerFactory);
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
}
}
public class Fact
{
[Key]
public Guid FactId { get; set; }
public DateTime? Date { get; set; }
// Other fields redacted
}
}
Actually, no need even for the T4 template, an empty DataConnection subclass works as well:
Slimmed-down repro to one file
using LinqToDB.Configuration;
using LinqToDB.EntityFrameworkCore;
using LinqToDB.Mapping;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using System.ComponentModel.DataAnnotations;
namespace L2DbBugRepro
{
class Program
{
static async Task Main(string[] args)
{
LinqToDBForEFTools.Initialize();
MappingSchema.Default.SetConverter<NodaTime.Instant, DateTime>(timeStamp =>
timeStamp.ToDateTimeUtc());
await using (var ctx = new BlogContext())
{
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();
ctx.Facts.Add(new Fact
{
FactId = Guid.NewGuid(),
Date = null,
});
await ctx.SaveChangesAsync();
}
var mysqlctx = new MysqlEmptyDB(new LinqToDbConnectionOptionsBuilder()
.UseMySql("Server=localhost;Port=3306;Database=mysqldb;Uid=mysql;Pwd=mysql;")
.Build());
await using (var ctx = new BlogContext())
{
var facts = await (from f in ctx.Facts.AsQueryable()
where f.Date == null || f.Date < DateTime.UtcNow
select f)
.ToArrayAsyncLinqToDB(); // Here we crash
}
}
}
public class MysqlEmptyDB : LinqToDB.Data.DataConnection
{
public MysqlEmptyDB(LinqToDbConnectionOptions options)
: base(options)
{
}
}
public class BlogContext : DbContext
{
public DbSet<Fact> Facts { get; set; }
static ILoggerFactory ContextLoggerFactory
=> LoggerFactory.Create(b => b.AddConsole().AddFilter("", LogLevel.Information));
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder
.UseNpgsql(@"Host=localhost;Username=root;Password=rootpass;Database=Testing", n => n.UseNodaTime())
.EnableSensitiveDataLogging()
.UseLoggerFactory(ContextLoggerFactory);
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
}
}
public class Fact
{
[Key]
public Guid FactId { get; set; }
public DateTime? Date { get; set; }
// Other fields redacted
}
}
You have to specify DateTime? converter also:
MappingSchema.Default.SetConverter<DateTime?, DataParameter>(dateTime =>
{
return new DataParameter
{
Value = dateTime == null ? dateTime : DateTime.SpecifyKind(dateTime.Value, DateTimeKind.Utc),
DataType = LinqToDB.DataType.DateTime
};
});
That actually did help, although I received a weird NullReferenceException somewhere deep in LinqToDb internals during enumeration of contents of a Grouping. Fortunately enough, that Grouping was easily converted into a out-of-db one, so I alleviated that, but still it was a bit weird.
Still, this problem mostly occured due to the MappingSchemas being set up globally instead per context/DataConnection. Probably this conflict between PostgreSQL and MySQL mappings wouldn't occur, if they would be defined separately. Are there plans on going away for such context-specific things from the static MappingSchema.Default space (falling back to it if it wouldn't be found elsewhere)?
don't close, we should investigate why it happens, could be a bug somewhere in linq2db
Sort of a follow-up on this grouping problem. It seems that this fix actually breaks something inside groups on left joins with DateTime parameters in the WHERE clauses.
Object reference not set to an instance of an object.
at LinqToDB.Linq.Builder.ParametersContext.CreateParameterAccessor(IDataContext dataContext, Expression accessorExpression, Expression originalAccessorExpression, Expression dbDataTypeAccessorExpression, Expression expression, Expression parametersExpression, String name)
at LinqToDB.Linq.Builder.ParametersContext.PrepareConvertersAndCreateParameter(ValueTypeExpression newExpr, Expression valueExpression, String name, ColumnDescriptor columnDescriptor, BuildParameterType buildParameterType)
at LinqToDB.Linq.Builder.ParametersContext.BuildParameter(Expression expr, ColumnDescriptor columnDescriptor, Boolean forceConstant, BuildParameterType buildParameterType)
at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertToSql(IBuildContext context, Expression expression, Boolean unwrap, ColumnDescriptor columnDescriptor, Boolean isPureExpression)
at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertCompare(IBuildContext context, ExpressionType nodeType, Expression left, Expression right)
at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertPredicate(IBuildContext context, Expression expression)
at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSearchCondition(IBuildContext context, Expression expression, List`1 conditions)
at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSearchCondition(IBuildContext context, Expression expression, List`1 conditions)
at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSearchCondition(IBuildContext context, Expression expression, List`1 conditions)
at LinqToDB.Linq.Builder.ExpressionBuilder.BuildWhere(IBuildContext parent, IBuildContext sequence, LambdaExpression condition, Boolean checkForSubQuery, Boolean enforceHaving)
at LinqToDB.Linq.Builder.WhereBuilder.BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo)
at LinqToDB.Linq.Builder.MethodCallBuilder.BuildSequence(ExpressionBuilder builder, BuildInfo buildInfo)
at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSequence(BuildInfo buildInfo)
at LinqToDB.Linq.Builder.WhereBuilder.BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo)
at LinqToDB.Linq.Builder.MethodCallBuilder.BuildSequence(ExpressionBuilder builder, BuildInfo buildInfo)
at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSequence(BuildInfo buildInfo)
at LinqToDB.Linq.Builder.SelectBuilder.BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo)
at LinqToDB.Linq.Builder.MethodCallBuilder.BuildSequence(ExpressionBuilder builder, BuildInfo buildInfo)
at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSequence(BuildInfo buildInfo)
at LinqToDB.Linq.Builder.ExpressionBuilder.Build[T]()
at LinqToDB.Linq.CompiledTable`1.<>c.<GetInfo>b__3_0(ICacheEntry`1 o, ValueTuple`5 key, ValueTuple`2 ctx)
at LinqToDB.Common.Internal.Cache.CacheExtensions.GetOrCreate[TItem,TKey,TDerivedKey,TContext](IMemoryCache`1 cache, TDerivedKey key, TContext context, Func`4 factory)
at LinqToDB.Linq.CompiledTable`1.GetInfo(IDataContext dataContext)
at LinqToDB.Linq.CompiledTable`1.Create(Object[] parameters, Object[] preambles)
at LinqToDB.CompiledQuery.ExecuteQuery[TResult](Object[] args)
at LinqToDB.CompiledQuery.Invoke[TDC,T1,T2,TResult](TDC dataContext, T1 arg1, T2 arg2)
at LinqToDB.Linq.Builder.GroupByBuilder.GroupByContext.Grouping`2.GetItems()
at LinqToDB.Linq.Builder.GroupByBuilder.GroupByContext.Grouping`2.GetEnumerator()
at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
at L2DbBugRepro.Program.<Main>d__0.MoveNext() in I:\dev\repro\repro\Program.cs:line 52
The repro
using LinqToDB.Configuration;
using LinqToDB.Data;
using LinqToDB.EntityFrameworkCore;
using LinqToDB.Mapping;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using System.ComponentModel.DataAnnotations;
namespace L2DbBugRepro
{
class Program
{
static async Task Main(string[] args)
{
LinqToDBForEFTools.Initialize();
MappingSchema.Default.SetConverter<NodaTime.Instant, DateTime>(timeStamp =>
timeStamp.ToDateTimeUtc());
MappingSchema.Default.SetConverter<DateTime?, DataParameter>(dateTime =>
new DataParameter
{
Value = dateTime == null ? dateTime : DateTime.SpecifyKind(dateTime.Value, DateTimeKind.Utc),
DataType = LinqToDB.DataType.DateTime
});
MappingSchema.Default.SetConverter<DateTime, DataParameter>(dateTime =>
new DataParameter
{
Value = DateTime.SpecifyKind(dateTime, DateTimeKind.Utc),
DataType = LinqToDB.DataType.DateTime
});
await using (var ctx = new BlogContext())
{
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();
ctx.Facts.Add(new Fact
{
FactId = Guid.NewGuid(),
Date = null,
});
await ctx.SaveChangesAsync();
}
var now = DateTime.UtcNow;
await using (var ctx = new BlogContext())
{
var facts = await (from f in ctx.Facts
join s in ctx.Somes on f.FactId equals s.SomeId into ss
from sss in ss.DefaultIfEmpty()
where sss == null || sss.SomeTime == null || sss.SomeTime < now
group sss by f into grp
select new { Fact = grp.Key, Somes = grp })
.FirstOrDefaultAsyncLinqToDB();
var somes = facts.Somes.ToArray();
}
var now2 = now;
}
}
public class BlogContext : DbContext
{
public DbSet<Fact> Facts { get; set; }
public DbSet<Some> Somes { get; set; }
static ILoggerFactory ContextLoggerFactory
=> LoggerFactory.Create(b => b.AddConsole().AddFilter("", LogLevel.Information));
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder
.UseNpgsql(@"Host=localhost;Username=root;Password=rootpass;Database=Testing", n => n.UseNodaTime())
.EnableSensitiveDataLogging()
.UseLoggerFactory(ContextLoggerFactory);
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
}
}
public class Fact
{
[Key]
public Guid FactId { get; set; }
public DateTime? Date { get; set; }
// Other fields redacted
}
public class Some
{
[Key]
public Guid SomeId { get; set; }
public DateTime SomeTime { get; set; }
}
}
It really seems that for some reason, the grouping aren't materialized on FirstOrDefaultAsyncLinqToDB/ToArratAsyncLinqToDb. And on iteration of the grouping it tries to materialize it, but fails...
Apparently, there's some kind of inherent incompatibility of DateTimes from Linq2Db (which apparently are all being processed as Kind.Unspecified) and Npgsql (with their new processing of DateTimes since 6.0). This actually happens even outside the NodaTime case. I've managed to avoid the issue by using theese mappings (inherently converting DateTimes to DateTimeOffsets):
MappingSchema.Default.SetConverter<DateTime?, DataParameter>(dateTime =>
{
if (dateTime == null)
{
return new DataParameter
{
DataType = DataType.DateTimeOffset,
Value = null,
};
}
return new DataParameter
{
Value = dateTime.Value.Kind == DateTimeKind.Utc
? new DateTimeOffset(dateTime.Value)
: dateTime,
DataType = dateTime.Value.Kind == DateTimeKind.Utc
? DataType.DateTimeOffset
: DataType.DateTime
};
});
MappingSchema.Default.SetConverter<DateTime, DataParameter>(dateTime =>
new DataParameter
{
Value = dateTime.Kind == DateTimeKind.Utc
? new DateTimeOffset(dateTime)
: dateTime,
DataType = dateTime.Kind == DateTimeKind.Utc
? DataType.DateTimeOffset
: DataType.DateTime
});
I have exactly same query in EF Core and linq2db, The latter fails and I'm not even using NodaTime.
In sql:
SELECT
m_1.map_id,
Avg(m_1.score),
Count(*)
FROM
map_reviews m_1
WHERE
m_1."timestamp" > :timestamp_1
GROUP BY
m_1.map_id
Speed is another issue, this query is somehow 5x faster using dapper, few % faster using EF Core: Dapper 93ms EF Core 550ms Linq2db 570ms
@kamyker It works for you with the same converters as above?
Yes but I'd expect it to work without them as I already have HasColumnType("timestamp with time zone")
Yeah, I guess this bug was introduced when Npgsql changed the DateTime processing.