How to support user-added columns without updating the EF model?

Question

I have an app C# with a model Entity Framework 6 "Database First" created from the basic structure of a database SQL Server, which belongs to a third party software.

Users of this software can add user fields to some tables by setting their name and data type, which are added to the table in SQL Server.

Example: in one implementation, the customer can add to the Products table a u_color field of type nvarchar, in the other implementation the customer can add to the Products table a column u_dataPrimeiraCompra of type datetime.

This Products table will thus have a set of columns present in all software implementations, but in each implementation specific columns of this implementation can be added.

My point is: Is there any way, at runtime, to inform the model of the existence of additional columns, for example: defining them in a file .config, so that it can assign or retrieve values from these additional columns, or otherwise more "direct" assign values to those columns, even without mirroring them in the template?

Answer 1

I don’t think Entity Framework will care about the additional columns as long as they don’t hinder the rescue of entities without additional values.

Adding columns without disturbing the Entity

To do this just set a default value for the additional fields, creating them with even pure SQL.

You would have to store which additional columns exist. It might even be in the same database, say in a call table ExtraColumns, which could be read by the Entity Framework and normally mapped to a class ExtraColumn.

using (var context = new MyContext())
{
    // pegando o ObjectContext
    var objectContext = context is DbContext
         ? ((IObjectContextAdapter)context).ObjectContext
         : context as ObjectContext;

    var isNullable = false;
    var nomeTabela = "Produtos";
    var nomeColuna = "NovaColuna"; // NOTA: se essa string vier do usuário então será
                                   // necessário validar o texto para evitar SQL Injection
    // Exemplo: permitir apenas letras
    if (nomeColuna.Any(ch => !char.IsLetter(ch)))
        throw new Exception("Senhor usuário, o nome da coluna permite apenas letras.");

    var tipoColuna = "nvarchar(max)";

    objectContext.ExecuteStoreCommand(string.Format(@"
        ALTER TABLE {0} 
        ADD [{1}] {2} {3} 
        CONSTRAINT DEFAULT_{0}_{1} DEFAULT {4}
    ", nomeTabela, nomeColuna, tipoColuna, isNullable ? "NULL" : "NOT NULL", valorDefault));

    var extraColumn = new ExtraColumn
    {
        NomeTabela = nomeTabela,
        NomeColuna = nomeColuna,
        TipoColuna = tipoColuna,
    }
    context.ExtraColumns.Add(extraColumn);
    context.SaveChanges();
}

Recovering values from extra columns

To recover values in these additional fields, you can use the connection itself used by DbContext or ObjectContext, but without going through the ORM itself, using the connection itself with the database and creating a DbCommand and then a DbDataReader:

using (var context = new MyContext())
{
    // pegando o ObjectContext
    var objectContext = context is DbContext
         ? ((IObjectContextAdapter)context).ObjectContext
         : context as ObjectContext;

    var nomeTabela = "Produtos";

    var extraColumns = context.ExtraColumns
        .Where(c => c.NomeTabela == nomeTabela)
        .ToList();

    var query = "SELECT "
        + string.Join(",", extraColumns.Select(c => c.NomeColuna))
        + " FROM " + nomeTabela;

    // pegando a conexão com o DB a partir do ObjectContext
    var conn = ((EntityConnection)objectContext.Connection).StoreConnection;

    using (var command = conn.CreateCommand())
    {
        command.CommandText = query;
        var reader = command.ExecuteReader();
        while (reader.Read())
        {
            // reader[0] agora contém a primeira coluna extra
            // reader[1] agora contém a segunda coluna extra
            // ...
        }
    }
}

Saving extra column values

To save values in these additional fields, you can use the method ExecuteStoreCommand, as we did before to create the new columns:

using (var context = new MyContext())
{
    // pegando o ObjectContext
    var objectContext = context is DbContext
         ? ((IObjectContextAdapter)context).ObjectContext
         : context as ObjectContext;

    var nomeTabela = "Produtos";
    var values = __lista_de_valores_a_atualizar__;

    var extraColumns = context.ExtraColumns
        .Where(c => c.NomeTabela == nomeTabela)
        .ToList();

    var queryFmt = "UPDATE "
        + string.Join(",", extraColumns.Select((c, i) => string.Format("{0} = {{{1}}}", c.NomeColuna, i))
        + " FROM " + nomeTabela;

    // NOTA: O método abaixo NÃO é suscetível a SQL Injection
    objectContext.ExecuteStoreCommand(queryFmt, values.ToArray());
}