DateOnly query on jsonb property throwing InvalidCastException

[mendoncaftw]

Hi,

Recently I added System.Text.Json as the de/serializer and also DateOnly and TimeOnly columns to entities in my project.
Everything is working as expected except the LINQ queries on DateOnly / TimeOnly props inside a jsonb column.

When performing the LINQ query it throws:
System.InvalidCastException: 'Unable to cast object of type 'System.DateOnly' to type 'System.String'.'

In my project it throws a similar error (maybe a small config change not in the repro project):
System.InvalidCastException: 'Can't write CLR type System.DateOnly with handler type TextHandler'

Here is a small repro. I'm using the ValueConverters from the EFCore github issue and your JsonOverrideTypeHandlerResolverFactory.

Am I missing something? Is there an attribute missing on the DateOnly prop? Hope it's not an obvious problem, thanks!

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage.ValueConversion;
using Npgsql;
using Npgsql.Internal;
using Npgsql.Internal.TypeHandlers;
using Npgsql.Internal.TypeHandling;
using System.ComponentModel.DataAnnotations.Schema;
using System.Text.Json;
using System.Text.Json.Serialization;

var builder = new DbContextOptionsBuilder<MyDbContext>();
builder.UseNpgsql($"host=localhost;database=dateonly_linq_test;username=postgres;password=123456;Trust Server Certificate=true;")
	   .EnableSensitiveDataLogging()
	   .EnableDetailedErrors();

var dbContext = new MyDbContext(builder.Options);
await dbContext.Database.EnsureDeletedAsync();
await dbContext.Database.EnsureCreatedAsync();

dbContext.Sales.Add(new Sale
{
	Ticket = new Ticket
	{
		Date = new DateOnly(2021, 12, 13)
	}
});

await dbContext.SaveChangesAsync();

_ = await dbContext.Sales.FirstOrDefaultAsync(s => s.Ticket.Date == new DateOnly(2021, 12, 13));


class MyDbContext : DbContext
{
	public DbSet<Sale> Sales { get; set; }

	static MyDbContext()
	{
		var options = new JsonSerializerOptions
		{
			DefaultIgnoreCondition = JsonIgnoreCondition.WhenWritingNull,
			PropertyNamingPolicy = JsonNamingPolicy.CamelCase
		};
		options.AddDateOnlyTimeOnlyConverters();
		NpgsqlConnection.GlobalTypeMapper.AddTypeResolverFactory(new JsonOverrideTypeHandlerResolverFactory(options));
	}

	public MyDbContext(DbContextOptions options) : base(options)
	{
	}
}

class Sale
{
	public int Id { get; set; }
	[Column(TypeName = "jsonb")]
	public Ticket Ticket { get; set; }
}

class Ticket
{
	public DateOnly? Date { get; set; }
}

/// <summary>
/// https://github.com/npgsql/efcore.pg/issues/1107#issuecomment-945126627
/// </summary>
public class JsonOverrideTypeHandlerResolverFactory : TypeHandlerResolverFactory
{
	private readonly JsonSerializerOptions _options;

	public JsonOverrideTypeHandlerResolverFactory(JsonSerializerOptions options)
		=> _options = options;

	public override TypeHandlerResolver Create(NpgsqlConnector connector)
		=> new JsonOverrideTypeHandlerResolver(connector, _options);

	public override string? GetDataTypeNameByClrType(Type clrType)
		=> null;

	public override TypeMappingInfo? GetMappingByDataTypeName(string dataTypeName)
		=> null;

	public class JsonOverrideTypeHandlerResolver : TypeHandlerResolver
	{
		readonly JsonHandler _jsonbHandler;

		internal JsonOverrideTypeHandlerResolver(NpgsqlConnector connector, JsonSerializerOptions options)
			=> _jsonbHandler ??= new JsonHandler(
				connector.DatabaseInfo.GetPostgresTypeByName("jsonb"),
				connector.TextEncoding,
				isJsonb: true,
				options);

		public override NpgsqlTypeHandler? ResolveByDataTypeName(string typeName)
			=> typeName == "jsonb" ? _jsonbHandler : null;

		public override NpgsqlTypeHandler? ResolveByClrType(Type type)
			// You can add any user-defined CLR types which you want mapped to jsonb
			=> type == typeof(JsonDocument)
				? _jsonbHandler
				: null;

		public override TypeMappingInfo? GetMappingByDataTypeName(string dataTypeName)
			=> null; // Let the built-in resolver do this
	}
}

public class DateOnlyJsonConverter : JsonConverter<DateOnly>
{
	public override DateOnly Read(ref Utf8JsonReader reader, Type typeToConvert, JsonSerializerOptions options)
	{
		var value = reader.GetString();
		if (value == null)
		{
			return default;
		}

		if (DateOnly.TryParseExact(value, "yyyy-MM-dd", out var dateOnly))
		{
			return dateOnly;
		}

		throw new ArgumentException("Invalid Date format. Expected 'yyyy-MM-dd'. Ex: 2021-07-31");
	}

	public override void Write(Utf8JsonWriter writer, DateOnly value, JsonSerializerOptions options)
		=> writer.WriteStringValue(value.ToString("yyyy-MM-dd"));
}

public class TimeOnlyJsonConverter : JsonConverter<TimeOnly>
{
	public override TimeOnly Read(ref Utf8JsonReader reader, Type typeToConvert, JsonSerializerOptions options)
	{
		var value = reader.GetString();
		if (value == null)
		{
			return default;
		}

		if (TimeOnly.TryParseExact(value, "HH:mm", out var timeOnly))
		{
			return timeOnly;
		}

		throw new ArgumentException("Invalid Time format. Expected 'HH:mm'. Ex: 09:30");
	}

	public override void Write(Utf8JsonWriter writer, TimeOnly value, JsonSerializerOptions options)
		=> writer.WriteStringValue(value.ToString("HH:mm"));
}

public static class JsonConverterExtensions
{
	public static void AddDateOnlyTimeOnlyConverters(this JsonSerializerOptions options)
	{
		options.Converters.Add(new DateOnlyJsonConverter());
		options.Converters.Add(new TimeOnlyJsonConverter());
	}
}

[roji]

Yeah, I'm unblocking this for 6.0.2 with #2149. Note that until System.Text.Json supports DateOnly and TimeOnly (dotnet/runtime#53539) you'll still need to manually wire up converters as you're doing above.

[mendoncaftw]

Thanks Shay, awesome work!

[roji]

Backported to 6.0.2 via a926c14