InvalidCastException When Reading Property with Inconsistent Column Types Across Multiple Databases

I'm encountering an InvalidCastException in Entity Framework 8 when attempting to read the MessageStatus property from the DeviceEvent entity. This issue arises because the MessageStatus column type varies across different instances of my database. Specifically, some databases define MessageStatus as Int32, while others use Int16.

Database: MSSQL 2008

My current Entity Framework configuration expects MessageStatus to be an Int16, which leads to the following error when the column is actually an Int32: An error occurred while reading a database value for property 'DeviceEvent.MessageStatus'. The expected type was 'System.Int16' but the actual value was of type 'System.Int32'.

[!NOTE] This is an implementation over a legacy system and I can't change DB schemas.

public enum MessageStatus : short
{
    Pending = 1,
    Sent = 2,
    Failed = 3
}

public class DeviceEvent
{
    public int Id { get; set; }
    public MessageStatus MessageStatus { get; set; }
    // Other properties...
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<DeviceEvent>(config =>
    {
        config.Property(b => b.MessageStatus)
            .HasColumnName("MessageStatus")
            .HasConversion(
                v => v,
                v => (MessageStatus)Convert.ToInt16(v)
            );
    });
}

I've tried several solutions:

removing HasConversion and specifying .HasColumnType to smallint (the database with Int32 complains)
removing HasConversion and specifying .HasColumnType to int (the database with Int16 complains)
changing the enum type to int (default)

Nothing seems to work. I'm stuck and nothing seems to help fix the problem, what's the solution to this kind of issues?

Well, you have different schemas and you're trying to fit it into one model. SqlClient is not going to do implicit conversion from Int32/Int16 to Int16/Int32 for you. Given that you can't fix the schema (which is really the correct solution), a quick and dirty workaround would be to do the casting in SQL and use ToSqlQuery in your OnModelCreating.

modelBuilder.Entity<DeviceEvent>(config =>
{
    config.Property(b => b.MessageStatus)
        .HasColumnName("MessageStatus");
    config.ToSqlQuery("select Id, cast(MessageStatus as smallint) as MessageStatus from DeviceEvent");
});

Fair to say, now inserts/updates/deletes are "unknown", so you'd have to use SPs or similar workaround (another).

Another workaround would be to have conditional model.

public class MyContext : DbContext
{
    readonly Type _messageStatusType;

    public MyContext(Type messageStatusType)
    {
        _messageStatusType = messageStatusType;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.LogTo(Console.WriteLine, events: [RelationalEventId.CommandExecuting]);
        optionsBuilder.UseSqlServer("Data Source=2001:67c:d74:66:5cbb:f6ff:fe9e:eefa;Database=test;User=sa;Password=Pa$$w0rd;Connect Timeout=10;ConnectRetryCount=0;TrustServerCertificate=true");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<DeviceEvent>(config =>
        {
            config.Ignore(b => b.MessageStatus);
            config.IndexerProperty(_messageStatusType, "MessageStatusBacking")
                .HasColumnName("MessageStatus");
        });
    }

}
public enum MessageStatus
{
    Pending = 1,
    Sent = 2,
    Failed = 3
}
public class DeviceEvent
{
    public int Id { get; set; }
    public MessageStatus MessageStatus
    {
        get => (MessageStatus)this[nameof(MessageStatus)];
        set => this[nameof(MessageStatus)] = value;
    }

    Dictionary<string, object> properties = [];
    public object this[string name]
    {
        get => properties[name];
        set => properties[name] = value;
    }
}

What puzzles me is that the write is working (so EF is able to write a declared int property into a smallint (the value is always within smallint range) and viceversa can store a short into an int, but the read fails.

I've already applied a dirty fix in similar but "better" way because I relay on repository pattern like this

public override async Task<DeviceEvent?> GetAsync(DeviceEventId id)
{
   try
   {
      return await base.GetAsync(id).ConfigureAwait(false);
   }
   catch (Exception ex)
   {
      logger.LogWarning(ex, "Error getting device event with id {id} using EF Core, trying with raw SQL.", id);

      var @event = await _context.DeviceEvents
         .FromSql($"""
            SELECT
               ID, ID_MEZZO, ID_FLOTTA, DATALOG, N_PORTA, ID_VETTORE, ID_STATO, REAL_TIME, ID_UTENTE, ID_AUTISTA, DATAPOSIZIONE, LAT, LON, ALT, DIR, VEL, KM, ORELAVORO, DATAEVENTO, DATACONFERMA, ALLARMI, DIGITALI, RELE, ANALOG1, ANALOG2, ANALOG3, ANALOG4, ANALOG5, ANALOG6, ANALOG7, ANALOG8, ANALOG9, ANALOG10, DESCEVENTO, ID_EVENTO, COD_EVENTO, ID_LINK, STAMPA, ID_INDIRIZZO, ID_CFG, ID_TRATTORE, ID_RIMORCHIO, ANALOG11, ANALOG12, ANALOG13, ANALOG14, ANALOG15, ANALOG16, ANALOG17, ANALOG18, ANALOG19, ANALOG20, DATACHIUSURAEVENTO, PARSED, PARSERSTEPSMASK, ParsedData, IdMission, IdLanguage, [Text],
               CAST(MessageStatus AS smallint) AS MessageStatus
            FROM STORICO
            WHERE ID = {id.Value}
            """)
         .FirstOrDefaultAsync()
         .ConfigureAwait(false);

      return @event;
   }
}

it's a temporary solution and I can improve this, caching which connection string fails with that specific error, in order to not have to try first the base.GetAsync that i know will fail.

We have more than 100 instances of DBs and half of them are affected (legacy problems) and we are now investigating to fix the scheme, that's obviously the preferred solution, meanwhile I was hoping on a proper way to handle this in an EF way.

E.g. having a way to specifiy how to query a field would fix this, like having a way to specify CAST ... AS instead of having to use raw sql to fetch the entity and lose features. A property for such use case in the MetaData property would be enough but it seems it's not available

About conditional model example it's not good because we don't want to have our domain layer have to deal with efcore problems (no quirks there)

Thanks

What puzzles me is that the write is working (so EF is able to write a declared int property into a smallint (the value is always within smallint range) and viceversa can store a short into an int, but the read fails.

I'm not an expert on SqlClient (rather FirebirdClient), but I guess on write implicit type conversions happen, but that's not the case for GetInt32/GetInt16.

Yeah, the important thing to understand is that these questions aren't related to EF, but rather to the lower-level database driver it uses (SqlClient in this case).

dotnet / efcore

InvalidCastException When Reading Property with Inconsistent Column Types Across Multiple Databases #35161