stevenrasmussen / efcore.sqlserver.nodatime Goto Github PK

View Code? Open in Web Editor NEW

100.0 7.0 17.0 193 KB

NodaTime support for EF Core

License: MIT License

C# 100.00%

nodatime efcore entityframeworkcore

efcore.sqlserver.nodatime's People

Contributors

Stargazers

Watchers

Forkers

franklin89 josh-degraw snebjorn nawalgupta klanglie spring1975 webseb akselkvitberg berkleyinsurancecompany-apac bangush benmccallum spocweb sucrose0413 ajcvickers nirzaf minhdv6 evolvedlight

efcore.sqlserver.nodatime's Issues

Support for Period

From the NodaTime docs

A duration represents a fixed length of elapsed time along the time line that occupies the same amount of time regardless of when it is applied. In contrast, Period represents a period of time in calendrical terms (years, months, days, and so on) that may vary in elapsed time when applied.

In general, use Duration to represent durations applied to global types like Instant and ZonedDateTime; use Period to represent a period applied to local types like LocalDateTime.

I understand that as: Period should be used with the Local* types and Duration with the Zoned* types.

So Period support would be nice :)

Any plans to support ZonedDateTime?

https://nodatime.org/3.0.x/userguide/zoneddatetime-patterns

Support net standard 2.0

Can you change the project from net standard 2.1 to 2.0?

This would greatly increase the usage of the library, especially for the unfortunate souls that have to support NET Framework projects a little while longer.

You have 3 dependencies to this nuget, and all dependencies supports net standard 2.0 so there is no reason for this nuget to require 2.1.

I cloned the repo and changed it to 2.0 and it builds fine and the tests runs ok.

Please change the target framework in the main csproj to netstandard2.0 and release a new version :)

The unit tests reference a database that is assumed to already exist. Can you publish a script to create those tables so I can ensure the column types are the expected ones when I run the tests myself? Thanks!

Support for compiled models

Looks like EF Core needs a few extra internal fields now - would you accept a PR that adds them?
Very similar to npgsql/efcore.pg#2871

Register valueconverters for EFCore.BulkExtensions

Hi!

The x => x.UseNodaTime(); works great for normal EF inserts/updates and queries. But I got into trouble when trying to use LocalDate with dbContext.BulkInsert(entities) from EFCore.BulkExtensions

I got this exception:

System.InvalidOperationException: The given value of type LocalDate from the data source cannot be converted to type datetime2 of the specified target column. ---> System.InvalidCastException: Failed to convert parameter value from a LocalDate to a DateTime. ---> System.InvalidCastException: Object must implement IConvertible.
  Stack Trace: 
    Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
    SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)
    --- End of inner exception stack trace ---
    SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)
    SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed)
    --- End of inner exception stack trace ---
    SqlBulkOperation.InsertAsync[T](DbContext context, IList`1 entities, TableInfo tableInfo, Action`1 progress, CancellationToken cancellationToken)

That library uses
foreach (var property in properties.Where(p => p.GetValueConverter() != null)) src to obtain the valueconverter.

When I manually registered a valueconverter the bulkinsert worked fine:

builder.Property(x => x.Date)
   .HasConversion(new Microsoft.EntityFrameworkCore.SqlServer.Storage.LocalDateValueConverter());

Could this library perhaps register the valueconverters on x => x.UseNodaTime()?

Thanks in adavance!

Updated to Latest Release and UseNodaTime is Not Working

I just updated to the latest release and now I'm getting an error. Is there an update to DbContextOptionsBuilder that I need to install?

SqlServerDbContextOptionsBuilder does not contain a definition for 'UseNodaTime'.

My connection string, which was working before is:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.
//optionsBuilder.UseSqlServer("Data Source=KS-PC2\MSSQLSERVER01;Initial Catalog=kennetix5;Integrated Security=True", x => x.UseNodaTime());
optionsBuilder.UseSqlServer("Data Source=tcp:76.90.61.172,1433;Initial Catalog=kennetix5;Persist Security Info=True;User ID=ABCD;Password=123456", x => x.UseNodaTime());

Fix example with Year() in README

The example for the DATEPART Support in the README is using the extension method Year() but this is actually a native Property.

LocalDateTime being stored as UTC Date in DB

All my dates that are assigned as a LocalDateTime are being stored in the database as 2023-10-27T19:10:18.867Z.

I would expect these to be a local date without any time zone information.

Thank you for the package, any help would be appreciated.

Enable design time support

So this can be used in Scaffolding and with EF Core Power Tools.

Requires implementation of design time services/extensions, like the Npgsql extension:

https://github.com/npgsql/efcore.pg/blob/main/src/EFCore.PG.NodaTime/Scaffolding/Internal/NpgsqlNodaTimeCodeGeneratorPlugin.cs

https://github.com/npgsql/efcore.pg/blob/main/src/EFCore.PG.NodaTime/Design/Internal/NpgsqlNodaTimeDesignTimeServices.cs

https://github.com/npgsql/efcore.pg/blob/main/src/EFCore.PG.NodaTime/build/netstandard2.0/Npgsql.EntityFrameworkCore.PostgreSQL.NodaTime.targets

Support for .NET 6

Hi Steven,

I just upgraded my project to .NET 6 RC1 and now get some errors from this package.

System.TypeLoadException: Method 'GetServiceProviderHashCode' in type 'ExtensionInfo' from assembly 'SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime, Version=5.0.2.0, Culture=neutral, PublicKeyToken=null' does not have an implementation.
   at Microsoft.EntityFrameworkCore.SqlServer.Infrastructure.NodaTimeOptionsExtension.get_Info()
   at Microsoft.EntityFrameworkCore.DbContextOptions.GetHashCode()
   at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd[TArg](TKey key, Func`3 valueFactory, TArg factoryArgument)
   at Microsoft.EntityFrameworkCore.Internal.ServiceProviderCache.GetOrAdd(IDbContextOptions options, Boolean providerRequired)
   at Microsoft.EntityFrameworkCore.DbContext..ctor(DbContextOptions options)

This seems to be due to some breaking changes they talked about here dotnet/efcore#26022.

Do you plan to support .NET 6 soon?

Queries using (DateTime).Date fail to translate

Hi,

Thanks a lot for your work on this library.

We are in the process of migrating an existing DbContext where DateTime is still used, and noticed that a specific existing query on an entity that has no NodaTime types fails to translate if the context is configured with NodaTime support, otherwise it translates fine.

It seems the issue is related to the use of DateTime's Date property in the LINQ expression.

Here is a simple repro: https://github.com/fordisonharry/EFCore.SqlServer.NodaTime/commit/849667bf4a388cad6f6a34c80138245b4d53ac43

It repros on the .net 6 branch as well.

I will have some more time next weekend to look into it, if it's not immediately obvious to you what the issue is.

Thanks again for the library.

Duration with values >= 24h

Hello

Since your DataType for a Duration is time Values >= 24h are not supported.
Do you plan on changing it or at least hint at it in the documentation?

ArgumentNullException with DbContextOptionsBuilder with UseInternalServiceProvider

I just upgraded our application to .NET6 and it uses this package for NodaTime support with EF Core 6. For our automated tests, we create a DBContext like so:

var serviceProvider = new ServiceCollection()
    .AddLogging(b =>
    {
        b.AddConsole();
        b.AddDebug();
    })
    .AddEntityFrameworkSqlServer()
    .AddNodaTime()
    .BuildServiceProvider();

var builder = new DbContextOptionsBuilder<MyDbContext>();
builder
    .UseInternalServiceProvider(serviceProvider)
    .UseSqlServer(database.ConnectionString, opt =>
    {
        opt.UseNodaTime();
    });

return new MyDbContext(builder.Options);

However after the upgrade, the MyDbContext.ctor throws an ArgumentNullException; 'Value cannot be null. (Parameter 'provider')'. The stack trace seems to indicate that it is somewhere in the NodaTimeOptionsExtension.Validate class, but I can't really figure out why. Any idea? Am I missing something?

Stack trace;

at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider)
at Microsoft.EntityFrameworkCore.Internal.DbContextServices.CreateModel(Boolean designTime)
at Microsoft.EntityFrameworkCore.Internal.DbContextServices.get_Model()
at Microsoft.EntityFrameworkCore.Infrastructure.EntityFrameworkServicesBuilder.<>c.<TryAddCoreServices>b__8_4(IServiceProvider p)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitFactory(FactoryCallSite factoryCallSite, RuntimeResolverContext context)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite callSite, RuntimeResolverContext context)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite callSite, RuntimeResolverContext context)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite callSite, RuntimeResolverContext context)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite callSite, RuntimeResolverContext context)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitIEnumerable(IEnumerableCallSite enumerableCallSite, RuntimeResolverContext context)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite callSite, RuntimeResolverContext context)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.Resolve(ServiceCallSite callSite, ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.DynamicServiceProviderEngine.<>c__DisplayClass2_0.<RealizeService>b__0(ServiceProviderEngineScope scope)
at Microsoft.Extensions.DependencyInjection.ServiceProvider.GetService(Type serviceType, ServiceProviderEngineScope serviceProviderEngineScope)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngineScope.GetService(Type serviceType)
at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetService[T](IServiceProvider provider)
at Microsoft.EntityFrameworkCore.SqlServer.Infrastructure.NodaTimeOptionsExtension.Validate(IDbContextOptions options)
at Microsoft.EntityFrameworkCore.Internal.ServiceProviderCache.ValidateOptions(IDbContextOptions options)
at Microsoft.EntityFrameworkCore.Internal.ServiceProviderCache.GetOrAdd(IDbContextOptions options, Boolean providerRequired)
at Microsoft.EntityFrameworkCore.DbContext..ctor(DbContextOptions options)
at MyProject.MyDbContext..ctor(DbContextOptions`1 options)

Support for EFCore 7

Do you plan to support EFCore 7 soon?

I'm spinning up a new project using RC1 and I get the following error when running the initial migration.

System.MissingMethodException: Method not found: 'Void CoreTypeMappingParameters..ctor(System.Type, Microsoft.EntityFrameworkCore.Storage.ValueConversion.ValueConverter, Microsoft.EntityFrameworkCore.ChangeTracking.ValueComparer, Microsoft.EntityFrameworkCore.ChangeTracking.ValueComparer, System.Func`3<Microsoft.EntityFrameworkCore.Metadata.IProperty,Microsoft.EntityFrameworkCore.Metadata.IEntityType,Microsoft.EntityFrameworkCore.ValueGeneration.ValueGenerator>)'.
   at SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime.Storage.DateTimeTypeMapping.CreateRelationalTypeMappingParameters(String storeType, Type clrType, ValueConverter valueConverter)

When I roll back to EFCore 6 it works perfectly.

Thank you for all the work on this library.

SqlDateTime overflow exception persisting default values

This would apply to both v3 and v5 branches

Column type for localdate gets generated as date
Column type for localdatetime gets generated as datetime2

Parameter type for localdate gets generated as datetime
Parameter type for localdatetime gets generated as datetime

Default values for localdate and localdatetime is 1/1/0001 can therefor not be persisted due to SqlDateTime overflow exception

PR raised #12 to apply correct types to parameter

Instant saved with low pression

If I save a NodaTime Instant in the database then there is only 2 decimals on nanoseconds.

The test below fails with:

  Message: 
    Assert.Equal() Failure
                                     ↓ (pos 21)
    Expected: ···020-09-06T13:07:42.8196022
    Actual:   ···020-09-06T13:07:42.8200000
                                     ↑ (pos 21)

It seems to be the insert part that does not work.

    public class Test
    {
       [Fact]
        public void ShowBug()
        {
            var databaseContext = new DatabaseContext();
            databaseContext.Database.EnsureDeleted();
            databaseContext.Database.EnsureCreated();
            var customer = new Customer()
            {
                Created = SystemClock.Instance.GetCurrentInstant(),
            };

            databaseContext.Add(customer);
            databaseContext.SaveChanges();

            databaseContext.Dispose();

            //Create a new context to be sure there is no cache involved.
            databaseContext = new DatabaseContext();
            Customer customerFromDatabase = databaseContext.Customers.First();
            Assert.Equal(customer.Created.ToString("yyyy-MM-ddTHH:mm:ss.fffffff", CultureInfo.InvariantCulture), customerFromDatabase.Created.ToString("yyyy-MM-ddTHH:mm:ss.fffffff", CultureInfo.InvariantCulture));

            databaseContext.Dispose();
        }
    }


    public class DatabaseContext : DbContext
    {
        public DbSet<Customer> Customers { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer(@"Data Source=.;Initial Catalog=SampleDatabase;Integrated Security=True;Pooling=False"
                    , optionsBuilder => optionsBuilder.UseNodaTime()
                    );
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
        }
    }

    public class Customer
    {
        public int Id { get; set; }

        public Instant Created { get; set; }
    }

Ps. Could you in the README consider to write something about your thoughts on ZonedDateTime. I know that SQLServer does not have a data type that can hold it. Properly the reason why you did not include it.

Database Migration

I am trying to use your Nuget package to convert a NodaTime Instant to DateTimeOffset in SQL. However, the migration seems to be converting to a DateTime2 type. I am using the latest version of EF Core. Please could you suggest how to change the mapping if this is possible? So far I am manually creating a ValueConverter in the DbContext to achieve this i.e.

var instantToDateTimeOffsetConverter = new ValueConverter<Instant, DateTimeOffset>(v =>
v.ToDateTimeOffset(),
v => Instant.FromDateTimeOffset(v));

builder.Entity().Property(p => p.DecisionInstant).HasConversion(instantToDateTimeOffsetConverter).IsRequired(false);

.NET6 tests with WebApplicationFactory don't respect "UseNoda()" extension

My tests which utilize WebApplicationFactory are not benefiting from the 'UseNoda()' extension. There are not compile errors, and at runtime, things work as expected.
During the tests, however, I receive ...is of type 'LocalDate' which is not supported by the current database provider.

I'm using <PackageReference Include="SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime" Version="6.0.0" /> with services.AddDbContext<MyDBContext>(options => options.UseSqlServer(connectionString, options => options.UseNodaTime()) );

I'm not sure why the use of WebApplicationFactory would cause a difference in behavior. Perhaps there's a simple approach to resolve this?

Missing Licence File

I am happy seeing this project - nice work! 🚀
Could you please add a licence file so that its clear what we are dealing with (MIT, GPL?, ..)

Filtering using Contains throws UnreachableException

Hello,

First of all, great library. Thanks for all your hard work!

After updating EF Core from 7.x to 8.04. and this library from 7.x to 8.0.1, I've encountered an error when trying to filter on LocalDate properties using Contains:

var dates = new[]
{
    new LocalDate(2024, 04, 22),
    new LocalDate(2024, 04, 23)
};

var results = dbContext.SomeEntities
    .Where(someEntity => dates.Contains(someEntity.LocalDate))
    .ToArrayAsync();

The above code will throw the following exception:

System.Diagnostics.UnreachableException : A SqlServerStringTypeMapping collection type mapping could not be found
   at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerQueryableMethodTranslatingExpressionVisitor.SqlServerInferredTypeMappingApplier.ApplyTypeMappingsOnOpenJsonExpression(SqlServerOpenJsonExpression openJsonExpression, IReadOnlyList`1 typeMappings)
   at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerQueryableMethodTranslatingExpressionVisitor.SqlServerInferredTypeMappingApplier.VisitExtension(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.<VisitChildren>g__VisitList|128_0[T](List`1 list, Boolean inPlace, Boolean& changed, <>c__DisplayClass128_0&)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.VisitChildren(ExpressionVisitor visitor, Boolean updateColumns)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.VisitChildren(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.RelationalInferredTypeMappingApplier.VisitExtension(Expression expression)
   at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerQueryableMethodTranslatingExpressionVisitor.SqlServerInferredTypeMappingApplier.VisitExtension(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.InExpression.VisitChildren(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.RelationalInferredTypeMappingApplier.VisitExtension(Expression expression)
   at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerQueryableMethodTranslatingExpressionVisitor.SqlServerInferredTypeMappingApplier.VisitExtension(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.VisitChildren(ExpressionVisitor visitor, Boolean updateColumns)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.VisitChildren(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.RelationalInferredTypeMappingApplier.VisitExtension(Expression expression)
   at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerQueryableMethodTranslatingExpressionVisitor.SqlServerInferredTypeMappingApplier.VisitExtension(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.RelationalInferredTypeMappingApplier.VisitExtension(Expression expression)
   at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerQueryableMethodTranslatingExpressionVisitor.SqlServerInferredTypeMappingApplier.VisitExtension(Expression expression)
   at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerQueryableMethodTranslatingExpressionVisitor.ApplyInferredTypeMappings(Expression expression, IReadOnlyDictionary`2 inferredTypeMappings)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToArrayAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)

The same happens for other NodaTime types, e.g. LocalTime.

Since the method throwing the exception is called ApplyTypeMappingsOnOpenJsonExpression, my guess is that's related to how EF Core 8 has changed generating SQL for Contains with parameter collections (it's now using OPENJSON instead of IN). More information can be found here.

This error doesn't occur when instead of using this library a custom converter for LocalDate is used:

class LocalDateCustomConverter : ValueConverter<LocalDate, DateTime>
{
    public LocalDateCustomConverter() : base(
        localDate => localDate.ToDateTimeUnspecified(),
        dateTime => LocalDate.FromDateTime(dateTime))
    {
    }
}

I've prepared a POC showing both cases:
https://github.com/rt-tidaro/EfCoreNodaTimeContainsBugPoc

Thanks for help!

CLR type for Noda Time types

When generating EF Core migrations with the Npgsql.EntityFrameworkCore.PostgreSQL.NodaTime library, the CLR type is set to the corresponding Noda Time type, e.g., Instant or LocalDate in the model snapshot:

b.Property<Instant>("Created")
    .HasColumnType("timestamp");

b.Property<LocalDate?>("Birthdate")
    .HasColumnType("date");

In this library, it is always set to DateTime:

b.Property<DateTime>("Created")
    .HasColumnType("datetime2");

b.Property<DateTime?>("Birthdate")
    .HasColumnType("date");

Is there a reason for this? It seems more natural to use the real type instead of always putting DateTime.

Support for Entity Framework Core 5

With the release of Entity Framework Core 5, the IMemberTranslator interface got a new parameter on the Translate method (IDiagnosticsLogger<DbLoggerCategory.Query> logger), which means that this package does no longer work.

The error message I get is:

System.TypeLoadException: Method 'Translate' in type 'Microsoft.EntityFrameworkCore.SqlServer.Query.ExpressionTranslators.LocalDateMemberTranslator' from assembly 'SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime, Version=1.3.3.0, Culture=neutral, PublicKeyToken=null' does not have an implementation.
   at Microsoft.EntityFrameworkCore.SqlServer.Query.ExpressionTranslators.NodaTimeTypesMemberTranslatorPlugin..ctor(RelationalMemberTranslatorProviderDependencies dependencies)
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)
   at System.Reflection.RuntimeConstructorInfo.Invoke(BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitRootCache(ServiceCallSite singletonCallSite, RuntimeResolverContext context)
    (
    .
    .
    .
    )
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.Resolve(ServiceCallSite callSite, ServiceProviderEngineScope scope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.DynamicServiceProviderEngine.<>c__DisplayClass1_0.<RealizeService>b__0(ServiceProviderEngineScope scope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngine.GetService(Type serviceType, ServiceProviderEngineScope serviceProviderEngineScope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngineScope.GetService(Type serviceType)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider)
   at Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies()
   at Microsoft.EntityFrameworkCore.DbContext.get_InternalServiceProvider()
   at Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies()
   at Microsoft.EntityFrameworkCore.DbContext.get_Model()
   at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.get_EntityType()
   at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.CheckState()
   at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.get_EntityQueryable()
   at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.System.Collections.Generic.IAsyncEnumerable<TEntity>.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at School.Services.StudentService.GetAll() in C:\git\AspVue\Backend\School\Services\StudentService.cs:line 69
   at Web.Api.Controllers.StudentController.Get() in C:\git\AspVue\Backend\Web.Api\Controllers\StudentController.cs:line 30
   at lambda_method496(Closure , Object )
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at NSwag.AspNetCore.Middlewares.SwaggerUiIndexMiddleware.Invoke(HttpContext context)
   at NSwag.AspNetCore.Middlewares.RedirectToIndexMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

Problem with InstantConverter .ToUniversalTime

I am using an Instant for my Created and Modified date and times. If I save the object to the SQL Server the UTC Time is saved as DateTime. Which is all correct. But when I load the object and check the Instant it is at the wrong UTC Time. I suppose this is because of the following line.

EFCore.SqlServer.NodaTime/src/SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime/Storage/InstantValueConverter.cs

Line 24 in 51a68a3

return Instant.FromDateTimeUtc(dateTime.ToUniversalTime());

If EF Core Maps to a DateTime the Kind will most likely be Unspecified. Calling ToUniversalTime will change the offset depending on the machines timezone. So I think there is no need for the ToUniversalTime method call.

Are standard operators supported?

This is probably a stupid question, but trying to do due diligence before I jump all in on this. Awesome library and will make life so much better!

I can see that SQL functions are support like DateAdd, etc. but I couldn't find anything stating explicitly whether standard operators like <, >, == are translated.

e.g.

public class Posts 
{
   public Instant PublishedAt { get; set; }
}

var now = _clock.GetCurrentInstant();
var publishedPosts = db.Posts.Where(p => p.PublishedAt <= now).ToList();

Thought I'd ask in case anyone else has the same question and then I can PR a little tweak to the README to state yes/no.

Perhaps the ValueConverters just takes care of all of this, but I've never really used them so not sure.

Exception due to invalid culture name in assembly

I get an exception when instantiating our DbContext class due to an invalid culture name "English (United States)".

Looking at the source code I found this specified in the csproj-file. If this should be a culture name I think you should change it to "en-US", but maybe you should consider just removing that row from the csproj instead?

Not sure if libraries should specify this attribute, it should probably be specified in the application itself - but I am not sure, I usually try to avoid localization at all cost.

More info about this attribute: https://docs.microsoft.com/en-us/dotnet/api/system.resources.neutralresourceslanguageattribute?view=netcore-3.1

Exception:

System.ArgumentException
The NeutralResourcesLanguageAttribute on the assembly "SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime, Version=1.3.2.0, Culture=neutral, PublicKeyToken=null" specifies an invalid culture name: "English (United States)".
at System.Resources.ManifestBasedResourceGroveler.GetNeutralResourcesLanguage(Assembly a, UltimateResourceFallbackLocation& fallbackLocation)
at System.Resources.ResourceManager.CommonAssemblyInit()
at System.Resources.ResourceManager..ctor(String baseName, Assembly assembly)
at SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime.Properties.Resources.get_ResourceManager()
at SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime.Properties.Resources.get_ServicesMissing()
at Microsoft.EntityFrameworkCore.SqlServer.Infrastructure.NodaTimeOptionsExtension.Validate(IDbContextOptions options)
at Microsoft.EntityFrameworkCore.Internal.ServiceProviderCache.ValidateOptions(IDbContextOptions options)
at Microsoft.EntityFrameworkCore.Internal.ServiceProviderCache.GetOrAdd(IDbContextOptions options, Boolean providerRequired)
at Microsoft.EntityFrameworkCore.DbContext..ctor(DbContextOptions options)

Support fot LocalDateTime.Date part

Would it be possible to support LocalDateTime.Date part? The behavior should be the same as DateTime.Date.

Usage example:

// Created property is of LocalDateTime type
Items.Where(x => x.Created.Date == ...)
Items.Where(x => x.Created.Date <= ...)

For now (v7.0.0) this fails with: "The LINQ expression '...' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information."