I have a table employing the TPH strategy which is mapping many derivative types to the same table (nothing unusual here).

I'm using an unmapped type (not declared in the EDM) in conjunction with raw SQL (via SqlQuery) to SELECT properties (columns) from both the base and derived types.

Entities:

public class Base 
{
    public Guid Id { get; set; }
    public DateTimeOffset Created { get; set; }
}

public class A : Base
{
    public string AProperty { get; set; }
}

public class B : Base
{
    public string BProperty { get; set; }
}

Unmapped type:

public class Model 
{
    public Guid Id { get; set; }
    public string AProperty { get; set; }
    public string BProperty { get; set; }
    public DateTimeOffset Created { get; set; }
}

Query:

var models = await context.Database.SqlQuery($"""
     SELECT Id, AProperty, BProperty, Created FROM MyTable
    """);

When I'm targeting SQL Server, everything works fine, no issues.

For our unit tests we're using SQLite; this is where my problems start.

First, SQLite does not support DateTimeOffset so in our ApplicationDbContext we have a workaround using a DateTimeOffsetToBinaryConverter when the Database.ProviderName is Microsoft.EntityFrameworkCore.Sqlite in the OnModelCreating.

This workaround works great for types defined in the EDM but the type I've defined for this particular query (Model) is not defined in the EDM which means that workaround is not used.

I tried switching the data type from DateTimeOffset to string in the Model DTO, which did in fact work for SQLite but fails with SQL Server with an invalid cast exception.

It also doesn't appear that I can specify a converter for a particular property via annotations; looks like only fluent configuration can accomplish this.

What can I do here? I feel a bit stuck..