EF Core中非映射类型在SQL Server与SQLite间的DateTimeOffset如何处理？

针对非映射类型Model在SQLite下无法应用DateTimeOffset转换器的问题，提供以下几种实用解决办法：

方案一：根据数据库类型动态生成查询并手动转换 #

通过判断当前数据库提供者，针对SQLite和SQL Server生成不同的查询语句，再手动处理Created字段的类型转换：

1. 定义SQLite专用的临时模型类：

public class SqliteModel
{
    public Guid Id { get; set; }
    public string AProperty { get; set; }
    public string BProperty { get; set; }
    public byte[] CreatedBinary { get; set; }
}

2. 编写分支查询逻辑：

List<Model> models;
var isSqlite = context.Database.ProviderName == "Microsoft.EntityFrameworkCore.Sqlite";

if (isSqlite)
{
    // SQLite下读取二进制存储的DateTimeOffset
    var sqliteModels = await context.Database.SqlQuery<SqliteModel>(
        "SELECT Id, AProperty, BProperty, Created AS CreatedBinary FROM MyTable"
    ).ToListAsync();
    
    models = sqliteModels.Select(m => new Model
    {
        Id = m.Id,
        AProperty = m.AProperty,
        BProperty = m.BProperty,
        // 复用DateTimeOffsetToBinaryConverter的转换逻辑
        Created = DateTimeOffset.FromBinary(BitConverter.ToInt64(m.CreatedBinary, 0))
    }).ToList();
}
else
{
    // SQL Server下直接查询
    models = await context.Database.SqlQuery<Model>(
        "SELECT Id, AProperty, BProperty, Created FROM MyTable"
    ).ToListAsync();
}

方案二：直接使用DbDataReader手动处理转换 #

绕开EF Core的SqlQuery，用原生ADO.NET方式执行查询，完全控制每个字段的转换逻辑：

using (var connection = context.Database.GetDbConnection())
{
    await connection.OpenAsync();
    using (var command = connection.CreateCommand())
    {
        command.CommandText = "SELECT Id, AProperty, BProperty, Created FROM MyTable";
        using (var reader = await command.ExecuteReaderAsync())
        {
            var models = new List<Model>();
            var isSqlite = connection is SqliteConnection;
            
            while (await reader.ReadAsync())
            {
                var model = new Model
                {
                    Id = reader.GetGuid(reader.GetOrdinal("Id")),
                    AProperty = reader.IsDBNull(reader.GetOrdinal("AProperty")) 
                        ? null 
                        : reader.GetString(reader.GetOrdinal("AProperty")),
                    BProperty = reader.IsDBNull(reader.GetOrdinal("BProperty")) 
                        ? null 
                        : reader.GetString(reader.GetOrdinal("BProperty"))
                };

                if (isSqlite)
                {
                    // 读取二进制并转换为DateTimeOffset
                    var bytes = (byte[])reader.GetValue(reader.GetOrdinal("Created"));
                    var ticks = BitConverter.ToInt64(bytes, 0);
                    model.Created = DateTimeOffset.FromBinary(ticks);
                }
                else
                {
                    // SQL Server直接读取DateTimeOffset
                    model.Created = reader.GetDateTimeOffset(reader.GetOrdinal("Created"));
                }

                models.Add(model);
            }
            return models;
        }
    }
}

方案三：注册全局类型转换器（EF Core 5+） #

为DateTimeOffset类型注册全局转换器，让非映射类型也能自动应用转换逻辑，仅在SQLite环境下生效：

1. 创建自定义类型映射源：

public class SqliteCustomTypeMappingSource : RelationalTypeMappingSource
{
    public SqliteCustomTypeMappingSource(
        TypeMappingSourceDependencies dependencies, 
        RelationalTypeMappingSourceDependencies relationalDependencies)
        : base(dependencies, relationalDependencies)
    {
    }

    protected override RelationalTypeMapping FindMapping(in RelationalTypeMappingInfo mappingInfo)
    {
        var mapping = base.FindMapping(mappingInfo);
        // 为DateTimeOffset添加二进制转换器
        if (mappingInfo.ClrType == typeof(DateTimeOffset))
        {
            return new RelationalTypeMapping(
                "BLOB",
                typeof(DateTimeOffset),
                new DateTimeOffsetToBinaryConverter());
        }
        return mapping;
    }
}

2. 在DbContext中替换服务（仅针对SQLite）：

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    base.OnConfiguring(optionsBuilder);
    
    // 判断是否为SQLite配置
    if (optionsBuilder.IsConfigured && optionsBuilder.Options.FindExtension<SqliteOptionsExtension>() != null)
    {
        optionsBuilder.ReplaceService<ITypeMappingSource, SqliteCustomTypeMappingSource>();
    }
}

配置完成后，原有的SqlQuery代码无需修改，SQLite会自动对非映射类型的DateTimeOffset属性应用转换器。

内容的提问来源于stack exchange，提问作者WBuck