CODEWITHSUNDEEP
c#entity-framework-core
dotnetdevcsharp
dotnetdevcsharp

Reputation: 3980

Date Only cannot be mapped SQL Server 2019

I am trying to use the new DateOnly aspects of c# but when I come to do my migrations I am having the following issue. I am using SQL Server 2019 the error is.

'Amenitie.StartDate could not be mapped because it is of type 'DateOnly', which is not a supported primitive type or a valid entity type. Either explicitly map this property, or ignore it using the'

My model is as follows

public class Amenitie
{
    public int? Id { get; set; }
    public Rooms? Rooms { get; set; }
    public string? Description { get; set; }
    public DateOnly StartDate { get; set; }
    public DateOnly EndDate { get; set; }
    public bool? isAvailable { get; set; }
    public bool? isDeleted { get; set; }
    public bool? isActive { get; set; }
    public DateTime? LastModifiedBy { get; set; }
    public DateTime? CreateDate { get; set; }
}

My Dal project and my web project are both set to

<PropertyGroup>
  <TargetFramework>net6.0</TargetFramework>
  <Nullable>enable</Nullable>
</PropertyGroup>

I am using the following package versions.

<ItemGroup>
 <PackageReference Include="Microsoft.AspNetCore.Identity" Version="2.2.0" />
 <PackageReference Include="Microsoft.AspNetCore.Identity.EntityFrameworkCore" Version="5.0.9" />
 <PackageReference Include="Microsoft.EntityFrameworkCore" Version="5.0.9" />
 <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="5.0.9">
 <PrivateAssets>all</PrivateAssets>
 <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
 </PackageReference>
 <PackageReference Include="Microsoft.EntityFrameworkCore.Relational.Design" Version="2.0.0-preview1-final" />
 <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="5.0.9" />
 <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer.Design" Version="2.0.0-preview1-final" />
 <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="5.0.9">
  <PrivateAssets>all</PrivateAssets>
  <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
 </PackageReference>
 <PackageReference Include="Microsoft.Extensions.Configuration" Version="5.0.0" />
</ItemGroup>

Is DateOnly available in EF Core 6 and above I presume?

Upvotes: 38

Views: 28274

Answers (3)

CleanCoder
CleanCoder

Reputation: 2869

Try this

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

    protected override void ConfigureConventions(ModelConfigurationBuilder builder)
    {
        builder.Properties<DateOnly>()
                .HaveConversion<DateOnlyConverter>()
                .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))
  { }
}

EDIT: with RC2 this does not work for nullable types.

EDIT 2: with NET6 release version this works for nullable types again, and more than that, you dont need to have a separate converter for "DateOnly?" anymore.

EDIT 3: with NET 8 built-in support has been added and the only thing you need to do is:

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

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

Upvotes: 46

Daniel Thorne
Daniel Thorne

Reputation: 131

The feature you are looking for in EF Core with net6.0 is probably unavailable because your project is the version 5.x.x Microsoft.EntityFrameworkCore.* packages.

However, DateOnly and TimeOnly were not scaffolded converted to Date and Time SQL types in NET6 but this is a new feature in NET8.

For anybody landing here, try upgrading your project to NET8 and installing the correct version of EF Core 8.x.x. i.e.:

<PackageReference Include="Microsoft.EntityFrameworkCore" Version="8.0.0" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="8.0.0">
  <PrivateAssets>all</PrivateAssets>
  <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
</PackageReference>
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="8.0.0" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="8.0.0">
  <PrivateAssets>all</PrivateAssets>
  <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
</PackageReference>

Upvotes: 0

boj
boj

Reputation: 11385

You have two choices:

  1. use DateTime instead of DateOnly.

  2. build a custom converter (see below).

As far I can see, the actual version of Entity Framework Core issue tracker states that model builder does not support it (find the issue here). Building a converter may solve your issue (cited from there):

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

Upvotes: 19

Related Questions