Support the new BCL DateOnly and TimeOnly structs for SQL Server

[roji]

New DateOnly and TimeOnly structs are being introduced to .NET 6 as alternatives to DateTime (dotnet/runtime#49036). This issue tracks supporting them in SQL Server.

I've opened a separate SqlClient issue for support (https://github.com/dotnet/SqlClient/issues/1009).

[mattjohnsonpint]

Please note that the final names for these types are DateOnly and TimeOnly and that they are now merged into the main branch for the next version of .NET 6 (preview 4 likely). Please rename the title of this issue accordingly. Thanks.

[roji]

Clearing milestone to discuss doing this in 6.0.0.

[mattjohnsonpint]

DateOnly and TimeOnly should map cleanly to SQL Server's date and time types. The tricky part will be to decide what to do with existing behavior that maps them to DateTime and TimeSpan. They should continue to function, but the new types should be preferred for things like scaffolding.

[CleanCodeX]

As of NET 6 Preview 6 I get an error using EF Core mapping DateOnly properties stating that the DB Provider (SQL Server) does not support this type. Is it supported or not? If not, can HaveConversion be a workaround? so far nothing worked (except not using DateOnly).

[roji]

@CleanCodeX EF Core support for DateOnly/TimeOnly depends on lower-level support in SqlClient; that's tracked in https://github.com/dotnet/SqlClient/issues/1009.

[CleanCodeX]

As already discussed there, it's not supported. What's the common workaround? builder.Properties<DateOnly>().HaveConversion(typeof(DateTime)); does not do the trick. If possible at all, do I need to write a custom Converter/Comparer to make it work or is anything built-in available that works as a workaround to keep using DateOnly?

[roji]

@CleanCodeX there isn't anything built-in for SQL Server, you'll have to implement your own value converter.

[CleanCodeX]

@CleanCodeX there isn't anything built-in for SQL Server, you'll have to implement your own value converter.

So I did. In case anyone has the same problem: This seems to do the trick. (at least I get no errors anymore...) If you find any error within, feel free to correct it.

Edit: missing call to HaveColumnType added Edit 2: as of RC2 the Converter for nullable DateOnly isn't recognized anymore.

      public class YourDbContext : DbContext
      {
            Ctors() {...}

        protected override void ConfigureConventions(ModelConfigurationBuilder builder)
        {
            builder.Properties<DateOnly>()
                .HaveConversion<DateOnlyConverter>()
                .HaveColumnType("date");

            builder.Properties<DateOnly?>()
                .HaveConversion<NullableDateOnlyConverter>()
                .HaveColumnType("date");
        }
      }
              
      /// <summary>
      /// Converts <see cref="DateOnly" /> to <see cref="DateTime"/> and vice versa.
      /// </summary>
      public class DateOnlyConverter : ValueConverter<DateOnly, DateTime>
      {
          /// <summary>
          /// Creates a new instance of this converter.
          /// </summary>
          public DateOnlyConverter() : base(
                  d => d.ToDateTime(TimeOnly.MinValue),
                  d => DateOnly.FromDateTime(d))
          { }
      }
   
      /// <summary>
      /// Converts <see cref="DateOnly?" /> to <see cref="DateTime?"/> and vice versa.
      /// </summary>
      public class NullableDateOnlyConverter : ValueConverter<DateOnly?, DateTime?>
      {
        /// <summary>
        /// Creates a new instance of this converter.
        /// </summary>
        public NullableDateOnlyConverter() : base(
            d => d == null 
                ? null 
                : new DateTime?(d.Value.ToDateTime(TimeOnly.MinValue)),
            d => d == null 
                ? null 
                : new DateOnly?(DateOnly.FromDateTime(d.Value)))
        { }
    }

I hope the dev community must not wait until NET 7, that a basic converter like this is added internally so users can use the short version of it:

    protected override void ConfigureConventions(ModelConfigurationBuilder builder)
    {
        builder.Properties<DateOnly>().HaveConversion<DateTime>();
        // OR
        builder.Properties<DateOnly>().HaveConversion<DateOnlyToDateTimeConverter>();
    }

Thanks for your time. Keep up the good work.

[akamud]

I hope the dev community must not wait until NET 7, that a basic converter like this is added internally so users can use the short version of it:

It isn't just "a basic converter" because you are still using datetime in the database. It is a great workaround for anyone who want to just map to DateOnly/TimeOnly types, but I believe your solution won't easily understand querying on DateOnly/TimeOnly types, mapping from/to migrations, do scaffolding, etc. The official support for SQL Server should probably use date and time on the database when appropriate.

Just being fair with the team working on this ;)

[roji]

It isn't just "a basic converter" because you are still using datetime in the database.

That's true, though it's possible to map DateTime to SQL Server date or time columns (using the Fluent API or Data Annotations). This would be a bit convoluted - use EF Core value converters to go from DateOnly to DateTime, and then tell SqlClient to write it to SQL Server date - but it would work.

Another thing missing from the above is translations from properties/methods on DateOnly/TimeOnly; so this would only take care of reading and writing the values.

[CleanCodeX]

Yeah of course the the properties needed to be marked as date / time sql type (or with fluent api). I thought that would be clear then. My mistake if it wasn't.

Edit:

protected override void ConfigureConventions(ModelConfigurationBuilder builder)
        {
            builder.Properties<DateOnly>()
                .HaveConversion<DateOnlyConverter, DateOnlyComparer>()
                .HaveColumnType("date");
        }

[davidhenley]

What is the status on this with RC1? Do we still need to write custom converters?

[ErikEJ]

DateOnly and TimeOnly support for SQL Server is not ready yet, earliest EF Core 7 is my guess.

[CleanCodeX]

sadly the above solution does not work anymore with RC2 for Nullable DateOnlys/TimeOnlys

protected sealed override void ConfigureConventions(ModelConfigurationBuilder builder)
        {
            builder.Properties<DateOnly>()
                .HaveConversion<DateOnlyConverter, DateOnlyComparer>()
                .HaveColumnType("date");

            builder.Properties<DateOnly?>()
                .HaveConversion<NullableDateOnlyConverter, NullableDateOnlyComparer>()
                .HaveColumnType("date");
        }

I get the error message, that the "DateOnlyComparer" cannot be used for a nullable DateOnly property. It seems that the Nullable DateOnly conversion is completely ignored.

Because of that, I reverted my whole solution back to RC1.

Are there any changes known regarding this and how to solve that?

[ajcvickers]

@AndriySvyryd The converters shown here are failing on the latest daily build.

Unhandled exception. System.InvalidOperationException: The comparer for type 'DateOnly' cannot be used for 'Post.DeletedOn' because its type is 'DateOnly?'.
   at Microsoft.EntityFrameworkCore.Metadata.Internal.Property.SetValueComparer(ValueComparer comparer, ConfigurationSource configurationSource)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.Property.SetValueComparer(Type comparerType, ConfigurationSource configurationSource)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.Property.Microsoft.EntityFrameworkCore.Metadata.IMutableProperty.SetValueComparer(Type comparerType)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.PropertyConfiguration.Apply(IMutableProperty property)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.ModelConfiguration.ConfigureProperty(IMutableProperty property)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.EntityType.AddProperty(String name, Type propertyType, MemberInfo memberInfo, Nullable`1 typeConfigurationSource, ConfigurationSource configurationSource)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.InternalEntityTypeBuilder.Property(Type propertyType, String propertyName, MemberInfo memberInfo, Nullable`1 typeConfigurationSource, Nullable`1 configurationSource)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.InternalEntityTypeBuilder.Property(MemberInfo memberInfo, Nullable`1 configurationSource)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.InternalEntityTypeBuilder.Microsoft.EntityFrameworkCore.Metadata.Builders.IConventionEntityTypeBuilder.Property(MemberInfo memberInfo, Boolean fromDataAnnotation)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.PropertyDiscoveryConvention.Process(IConventionEntityTypeBuilder entityTypeBuilder)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.PropertyDiscoveryConvention.ProcessEntityTypeAdded(IConventionEntityTypeBuilder entityTypeBuilder, IConventionContext`1 context)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ImmediateConventionScope.OnEntityTypeAdded(IConventionEntityTypeBuilder entityTypeBuilder)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.OnEntityTypeAddedNode.Run(ConventionDispatcher dispatcher)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.DelayedConventionScope.Run(ConventionDispatcher dispatcher)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ConventionBatch.Run()
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ConventionBatch.Dispose()
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ImmediateConventionScope.OnModelInitialized(IConventionModelBuilder modelBuilder)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.OnModelInitialized(IConventionModelBuilder modelBuilder)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.Model..ctor(ConventionSet conventions, ModelDependencies modelDependencies, ModelConfiguration modelConfiguration)
   at Microsoft.EntityFrameworkCore.ModelBuilder..ctor(ConventionSet conventions, ModelDependencies modelDependencies, ModelConfiguration modelConfiguration)
   at Microsoft.EntityFrameworkCore.ModelConfigurationBuilder.CreateModelBuilder(ModelDependencies modelDependencies)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.CreateModel(DbContext context, IConventionSetBuilder conventionSetBuilder, ModelDependencies modelDependencies)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.GetModel(DbContext context, ModelCreationDependencies modelCreationDependencies, Boolean designTime)
   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.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.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.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_ContextServices()
   at Microsoft.EntityFrameworkCore.DbContext.get_InternalServiceProvider()
   at Microsoft.EntityFrameworkCore.DbContext.Microsoft.EntityFrameworkCore.Infrastructure.IInfrastructure<System.IServiceProvider>.get_Instance()
   at Microsoft.EntityFrameworkCore.Infrastructure.Internal.InfrastructureExtensions.GetService[TService](IInfrastructure`1 accessor)
   at Microsoft.EntityFrameworkCore.Infrastructure.AccessorExtensions.GetService[TService](IInfrastructure`1 accessor)
   at Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.get_Dependencies()
   at Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.EnsureDeleted()
   at Program.Main() in C:\local\code\AllTogetherNow\Daily\Daily.cs:line 112

public class Blog
{
    public int Id { get; set; }
    public string Name { get; set; }

    public ICollection<Post> Posts { get; set; }
}

public class Post
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public DateOnly CreatedOn { get; set; }
    public DateOnly? DeletedOn { get; set; }

    public Blog Blog { get; set; }
}

public class SomeDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(Your.ConnectionString)
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    protected sealed override void ConfigureConventions(ModelConfigurationBuilder builder)
    {
        builder.Properties<DateOnly>()
            .HaveConversion<DateOnlyConverter, DateOnlyComparer>()
            .HaveColumnType("date");

        builder.Properties<DateOnly?>()
            .HaveConversion<NullableDateOnlyConverter, NullableDateOnlyComparer>()
            .HaveColumnType("date");
    }
}

public class DateOnlyConverter : ValueConverter<DateOnly, DateTime>
{
    public DateOnlyConverter() : base(
        d => d.ToDateTime(TimeOnly.MinValue),
        d => DateOnly.FromDateTime(d))
    {
    }
}

public class DateOnlyComparer : ValueComparer<DateOnly>
{
    public DateOnlyComparer() : base(
        (d1, d2) => d1 == d2 && d1.DayNumber == d2.DayNumber,
        d => d.GetHashCode())
    {
    }
}

public class NullableDateOnlyConverter : ValueConverter<DateOnly?, DateTime?>
{
    public NullableDateOnlyConverter() : base(
        d => d == null
            ? null
            : new DateTime?(d.Value.ToDateTime(TimeOnly.MinValue)),
        d => d == null
            ? null
            : new DateOnly?(DateOnly.FromDateTime(d.Value)))
    {
    }
}

public class NullableDateOnlyComparer : ValueComparer<DateOnly?>
{
    public NullableDateOnlyComparer() : base(
        (d1, d2) => d1 == d2 && d1.GetValueOrDefault().DayNumber == d2.GetValueOrDefault().DayNumber,
        d => d.GetHashCode())
    {
    }
}

public class Program
{
    public static void Main()
    {
        using (var context = new SomeDbContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            context.Add(new Post
            {
                CreatedOn = DateOnly.FromDateTime(DateTime.Now),
                DeletedOn = DateOnly.FromDateTime(DateTime.Now)
            });

            context.SaveChanges();
        }

        using(var context = new SomeDbContext())
        {
            var post = context.Posts.Single();
        }
    }
}

[AndriySvyryd]

Moved to https://github.com/dotnet/efcore/issues/26429

[jlorek]

@CleanCodeX there isn't anything built-in for SQL Server, you'll have to implement your own value converter.

So I did. In case anyone has the same problem: This seems to do the trick. (at least I get no errors anymore...) If you find any error within, feel free to correct it.

Edit: missing call to HaveColumnType added Edit 2: as of RC2 the Converter for nullable DateOnly isn't recognized anymore.

      public class YourDbContext : DbContext
      {
            Ctors() {...}

        protected override void ConfigureConventions(ModelConfigurationBuilder builder)
        {
            builder.Properties<DateOnly>()
                .HaveConversion<DateOnlyConverter, DateOnlyComparer>()
                .HaveColumnType("date");

            builder.Properties<DateOnly?>()
                .HaveConversion<NullableDateOnlyConverter, NullableDateOnlyComparer>()
                .HaveColumnType("date");
        }
      }
              
      /// <summary>
      /// Converts <see cref="DateOnly" /> to <see cref="DateTime"/> and vice versa.
      /// </summary>
      public class DateOnlyConverter : ValueConverter<DateOnly, DateTime>
      {
          /// <summary>
          /// Creates a new instance of this converter.
          /// </summary>
          public DateOnlyConverter() : base(
                  d => d.ToDateTime(TimeOnly.MinValue),
                  d => DateOnly.FromDateTime(d))
          { }
      }
      
      /// <summary>
      /// Compares <see cref="DateOnly" />.
      /// </summary>
      public class DateOnlyComparer : ValueComparer<DateOnly>
      {
          /// <summary>
          /// Creates a new instance of this converter.
          /// </summary>
          public DateOnlyComparer() : base(
              (d1, d2) => d1 == d2 && d1.DayNumber == d2.DayNumber, 
              d => d.GetHashCode())
          {
          }
      }

      /// <summary>
      /// Converts <see cref="DateOnly?" /> to <see cref="DateTime?"/> and vice versa.
      /// </summary>
      public class NullableDateOnlyConverter : ValueConverter<DateOnly?, DateTime?>
      {
        /// <summary>
        /// Creates a new instance of this converter.
        /// </summary>
        public NullableDateOnlyConverter() : base(
            d => d == null 
                ? null 
                : new DateTime?(d.Value.ToDateTime(TimeOnly.MinValue)),
            d => d == null 
                ? null 
                : new DateOnly?(DateOnly.FromDateTime(d.Value)))
        { }
    }

    /// <summary>
    /// Compares <see cref="DateOnly?" />.
    /// </summary>
    public class NullableDateOnlyComparer : ValueComparer<DateOnly?>
    {
        /// <summary>
        /// Creates a new instance of this converter.
        /// </summary>
        public NullableDateOnlyComparer() : base(
            (d1, d2) => d1 == d2 && d1.GetValueOrDefault().DayNumber == d2.GetValueOrDefault().DayNumber,
            d => d.GetHashCode())
        {
        }
    }

I hope the dev community must not wait until NET 7, that a basic converter like this is added internally so users can use the short version of it:

    protected override void ConfigureConventions(ModelConfigurationBuilder builder)
    {
        builder.Properties<DateOnly>().HaveConversion<DateTime>();
        // OR
        builder.Properties<DateOnly>().HaveConversion<DateOnlyToDateTimeConverter>();
    }

Thanks for your time. Keep up the good work.

Thanks for the Code, it's really helping me out! But are these ValueComparer Implementations really necessary, since DateOnly already implements IComparable itself?

[CleanCodeX]

"But are these ValueComparer Implementations really necessary, since DateOnly already implements IComparable itself?"

No, you can completely leave them out as the following code shows. I did not see in the first place, that the second type parameter isn't enforced. The custom comparer is ony useful for non-implementers of IComparable

protected override void ConfigureConventions(ModelConfigurationBuilder builder)
        {
            builder.Properties<DateOnly>()
                .HaveConversion<DateOnlyConverter>()
                .HaveColumnType("date");

            builder.Properties<DateOnly?>()
                .HaveConversion<NullableDateOnlyConverter>()
                .HaveColumnType("date");
        }

[mikelyncheski]

Thanks for the sample.

But seriously what good are these types without out-of-the-box JSON and EF SQL Server support?

[roji]

@mikelyncheski it takes time for new features to propagate throughout the entire stack - that's just the way technology works. Support for DateOnly and TimeOnly did get in for many other database providers (PostgreSQL, SQLite, MySQL) so that's definitely a positive thing; but in other cases there's simply not enough resources to do everything right away.

[mikelyncheski]

Thank you for the explanation @roji and for all of the other great changes.

[RamType0]

Why is this still marked as blocking? #26429 is solved, and it was already implemented for Npgsql . Is there any SQL Server specific problems?

[AndriySvyryd]

@RamType0 https://github.com/dotnet/SqlClient/issues/1009

[rofenix2]

Will this be on EFCore 7 seeing that this is already completed in SqlClient?

https://github.com/dotnet/SqlClient/pull/1813

[roji]

No - a SqlClient version hasn't even been released with this functionality. I plan to work on this for 8.0.

[ErikEJ]

@roji Could it potentially be retrofitted for EF Core6/7 via a third party package?

[roji]

@ErikEJ yeah, that's possible - the same type mapping/query translation code that would go into the SQL Server provider for 8.0 could go into a plugin for older versions, where the plugin would take a dependency on the new SqlClient. Let's revisit this once SqlClient is released with this support.

[davidbuckleyni]

Have these been removed in .net 7 and 2019 I get

[]( at Microsoft.EntityFrameworkCore.Design.OperationExecutor.AddMigration.<>c__DisplayClass0_0.<.ctor>b__0() at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0() at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action) The 'DateOnly?' property 'Supplier.LastModifiedDate' could not be mapped to the database type 'LastModifiedDate' because the database provider does not support mapping 'DateOnly?' properties to 'LastModifiedDate' columns. Consider mapping to a different database type or converting the property value to a type supported by the database using a value converter. See https://aka.ms/efcore-docs-value-converters for more information. Alternately, exclude the property from the model using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.)

[roji]

@davidbuckleyni DateOnly and TimeOnly aren't yet supported when using the SQL Server provider, that's what this issue tracks.

[ErikEJ]

I have just published a preview package that adds support for DateOnly and TimeOnly with EF Core 6 and 7.

Feel free to give it a try and provide feedback in the github repo.

https://www.nuget.org/packages/ErikEJ.EntityFrameworkCore.SqlServer.DateOnlyTimeOnly/6.0.0-preview.1#readme-body-tab