EF mapping for tables with variable name

Question

I have several tables with the same structure, but with different names.

Ex.:

CREATE TABLE `log_historico_25072016` (
`lhis_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`lhis_cvei_id` INT(10) UNSIGNED NOT NULL,
`lhis_sequencia` SMALLINT(5) UNSIGNED NOT NULL,
`lhis_tapl_id` TINYINT(3) UNSIGNED NOT NULL,
`lhis_cevt_id` SMALLINT(5) UNSIGNED NOT NULL,
`lhis_data_gps` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
`lhis_data_ins` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`lhis_latitude` FLOAT NOT NULL,
`lhis_longitude` FLOAT NOT NULL,
`lhis_cpnt_id` INT(10) UNSIGNED NOT NULL,
`lhis_ignicao` CHAR(1) NOT NULL,
`lhis_velocidade` TINYINT(3) UNSIGNED NOT NULL,
`lhis_nsat` TINYINT(3) UNSIGNED NOT NULL,
`lhis_curso` SMALLINT(5) UNSIGNED NOT NULL,
`lhis_info` SMALLINT(5) UNSIGNED NOT NULL,
`lhis_dop` TINYINT(3) UNSIGNED NOT NULL,
`lhis_input` BIGINT(20) NOT NULL,
`lhis_output` SMALLINT(5) UNSIGNED NOT NULL,
`lhis_tmco_id` TINYINT(3) UNSIGNED NOT NULL,
`lhis_inAlarme` CHAR(1) NOT NULL,
`lhis_chip` TINYINT(3) UNSIGNED NOT NULL,
`lhis_crua_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
`lhis_ext_id` INT(10) UNSIGNED NULL DEFAULT NULL,
`lhis_altitude` TINYINT(4) NOT NULL DEFAULT '0',
`lhis_consumo` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
`lhis_ltemp_1` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
`lhis_ltemp_2` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
`lhis_ltemp_3` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
`lhis_ltemp_4` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
PRIMARY KEY (`lhis_id`),
UNIQUE INDEX `lhis_cvei_gps_seq_apl_evt_un_27052016` (`lhis_cvei_id`, `lhis_data_gps`, `lhis_sequencia`, `lhis_tapl_id`, `lhis_cevt_id`),
INDEX `lhis_data_gps_ix` (`lhis_data_gps`),
INDEX `lhis_data_ins_ix` (`lhis_data_ins`)


CREATE TABLE `log_historico_26072016` (
`lhis_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`lhis_cvei_id` INT(10) UNSIGNED NOT NULL,
`lhis_sequencia` SMALLINT(5) UNSIGNED NOT NULL,
`lhis_tapl_id` TINYINT(3) UNSIGNED NOT NULL,
`lhis_cevt_id` SMALLINT(5) UNSIGNED NOT NULL,
`lhis_data_gps` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
`lhis_data_ins` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`lhis_latitude` FLOAT NOT NULL,
`lhis_longitude` FLOAT NOT NULL,
`lhis_cpnt_id` INT(10) UNSIGNED NOT NULL,
`lhis_ignicao` CHAR(1) NOT NULL,
`lhis_velocidade` TINYINT(3) UNSIGNED NOT NULL,
`lhis_nsat` TINYINT(3) UNSIGNED NOT NULL,
`lhis_curso` SMALLINT(5) UNSIGNED NOT NULL,
`lhis_info` SMALLINT(5) UNSIGNED NOT NULL,
`lhis_dop` TINYINT(3) UNSIGNED NOT NULL,
`lhis_input` BIGINT(20) NOT NULL,
`lhis_output` SMALLINT(5) UNSIGNED NOT NULL,
`lhis_tmco_id` TINYINT(3) UNSIGNED NOT NULL,
`lhis_inAlarme` CHAR(1) NOT NULL,
`lhis_chip` TINYINT(3) UNSIGNED NOT NULL,
`lhis_crua_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
`lhis_ext_id` INT(10) UNSIGNED NULL DEFAULT NULL,
`lhis_altitude` TINYINT(4) NOT NULL DEFAULT '0',
`lhis_consumo` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
`lhis_ltemp_1` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
`lhis_ltemp_2` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
`lhis_ltemp_3` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
`lhis_ltemp_4` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
PRIMARY KEY (`lhis_id`),
UNIQUE INDEX `lhis_cvei_gps_seq_apl_evt_un_27052016` (`lhis_cvei_id`, `lhis_data_gps`, `lhis_sequencia`, `lhis_tapl_id`, `lhis_cevt_id`),
INDEX `lhis_data_gps_ix` (`lhis_data_gps`),
INDEX `lhis_data_ins_ix` (`lhis_data_ins`)

The table name is defined by log_historico_ddmmyyyy

You can use EF to map these tables?

Answer 1

In short, yes.

At the event OnModelCreating from your context, define the following:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    ConfigurationRegistrar configurationRegistrar = modelBuilder.Configurations;

    new ConfiguracaoGeralEntidades(configurationRegistrar);
}

This causes us to get the complete configuration of Model Builder within a custom class of handling this setting. Defining this class:

public class ConfiguracaoGeralEntidades
{
    public ConfiguracaoGeralEntidades(ConfigurationRegistrar configurationRegistrar)
    {
        configurationRegistrar.Add(new MinhaConfiguracaoDinamica());
        // Aqui você pode colocar outras configurações, se quiser.
    }
}

Now comes the dynamic part: here you will put the table name as a string and register as a mapping:

public class MinhaConfiguracaoDinamica : EntityTypeConfiguration<LogHistorico>
{
    public MinhaConfiguracaoDinamica()
    {
        ToTable("log_historico_25072016"); // Aqui estou supondo que o mapeamento está todo certo.
        Property(entity => entity.Id).HasColumnName("lhis_id");

        // Coloque as demais configurações aqui.
    }
}

At the event Application_Start from your Global.asax.Cs, put the following:

DbContextInitializer.Init(connectionString);

DbContextInitializer is below:

public static class DbContextInitializer
{
    public static void Init (string connectionString)
    {
        Database.SetInitializer(new CreateDbThrowExceptionIfModelDiffersInitializer<SMDbContext>());

        using(var dbContenxt = new MyDbContext(connectionString))
        {
            try
            {
                dbContenxt.Database.Initialize(true);
            }
            catch(DatabaseModelDiffersException diffException)
            {
                // Aqui você trata exceções em caso de o banco estar diferente dos mapeamentos iniciados.
            }
            catch(Exception ex)
            {
                // Aqui você trata outras exceções.
                throw;
            }
        }
    }

    public class CreateDbThrowExceptionIfModelDiffersInitializer<TContext> : IDatabaseInitializer<TContext> where TContext : DbContext
    {
        public void InitializeDatabase(TContext context)
        {
            using (new TransactionScope(TransactionScopeOption.Suppress))
            {
                if (!context.Database.Exists())
                    context.Database.Create();
            }

            if (!context.Database.CompatibleWithModel(true))
            {
                throw new DatabaseModelDiffersException("Modelo do banco diferente dos mapeamentos dos Models.");
            }
        }

        protected virtual void Seed(TContext context)
        {
            // Aqui você faz algum seeding, se quiser.
        }
    }

    // Exceção personalizada, para tratar as diferenças de banco e seus Models.
    public class DatabaseModelDiffersException : Exception
    {
        public DatabaseModelDiffersException(string msg) : base(msg)
        {}
    }
}

I took it from here.