System.InvalidCastException: Can't cast database type timestamp without time zone to DateOnly

I get this error message when using EF Core to query data:

System.InvalidCastException: Can't cast database type timestamp without time zone to DateOnly

This is a sample code that causes an error when I use AddDay()

Then I found the Issue on github, but it was resolved in .NET 8. Is there any way to do it in .NET 6 ?

https://github.com/npgsql/efcore.pg/issues/2888

using Microsoft.EntityFrameworkCore;
using Npgsql;
using Xunit;

namespace DateOnlyNpgsql;

public class TestEntity
{
    public int Id { get; set; }
    public DateOnly Date { get; set; }
    public int Diff { get; set; }
}

public class MyTest
{
    [Fact]
    public async Task InvalidCastExample()
    {
        var x = new DbCtx();
        await x.Database.EnsureDeletedAsync();
        await x.Database.EnsureCreatedAsync();
        var e = new TestEntity()
        {
            Id = 1,
            Date = DateOnly.Parse("2023-09-26"),
            Diff = 1
        };
        x.TestEntities.Add(e);
        await x.SaveChangesAsync();

        var query = x.TestEntities
            .Select(x => new
            {
                x.Id,
                NextMonth = x.Date.AddDays(x.Diff)
            });

        // System.InvalidCastException: Can't cast database type timestamp without time zone to DateOnly
        var a1 = query.ToArray();
    }
}

public class DbCtx : DbContext
{
    public DbSet<TestEntity> TestEntities { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseNpgsql(
            new NpgsqlConnection("Host=localhost;Uid=postgres;Pwd=mysecretpassword;Database=DummyDatabase"));
}

As you have shared the known issue link you can see the issue has been fixed with the .net 8 version if you still using .net 6 i would like to suggest you to upgrade to .net to fix the issue or if you still would like to use .net 6 you could use this below work around which use the DateTime and handle the conversion to DateOnly in your application logic. This avoids the cast issue when interacting with the database.

You could try this below code:

Program.cs:

using System;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Npgsql;
using DateOnlyNpgsql.Models;
using DateOnlyNpgsql.Data;

namespace DateOnlyNpgsql
{
    class Program
    {
        static async Task Main(string[] args)
        {
            var db = new DbCtx();
            await db.Database.EnsureDeletedAsync();
            await db.Database.EnsureCreatedAsync();

            var entity = new TestEntity
            {
                Id = 1,
                Date = DateTime.SpecifyKind(DateTime.Parse("2023-09-26"), DateTimeKind.Utc),
                Diff = 1
            };

            db.TestEntities.Add(entity);
            await db.SaveChangesAsync();

            var query = db.TestEntities
                .Select(x => new
                {
                    x.Id,
                    NextMonth = DateOnly.FromDateTime(x.Date.AddDays(x.Diff))
                });

            var results = query.ToArray();
            foreach (var result in results)
            {
                Console.WriteLine($"Id: {result.Id}, NextMonth: {result.NextMonth}");
            }
        }
    }
}

MyTest.cs:

using Microsoft.EntityFrameworkCore;
using Npgsql;
using Xunit;
using System;
using System.Linq;
using System.Threading.Tasks;
using DateOnlyNpgsql.Models;
using DateOnlyNpgsql.Data;



namespace DateOnlyNpgsql.Tests
{
    public class MyTest
    {
        [Fact]
        public async Task InvalidCastExample()
        {
            var x = new DbCtx();
            await x.Database.EnsureDeletedAsync();
            await x.Database.EnsureCreatedAsync();
            var e = new TestEntity()
            {
                Id = 1,
                Date = DateTime.Parse("2023-09-26"),
                Diff = 1
            };
            x.TestEntities.Add(e);
            await x.SaveChangesAsync();

            var query = x.TestEntities
                .Select(x => new
                {
                    x.Id,
                    NextMonth = DateOnly.FromDateTime(x.Date.AddDays(x.Diff))
                });

            var a1 = query.ToArray();
            Assert.NotNull(a1);
            Assert.Single(a1);
            Assert.Equal(new DateOnly(2023, 09, 27), a1[0].NextMonth);
        }

    }
}

Result: