Auto table (re)creation on insert events at case of ef-code-first

Problem

Sometimes there are some reasons to (re)create specific database table(s). Let's consider very common situation with logging. Probably, all projects write some kinds of logs and many of them stored at database. At some moment of time, these logs i.e. tables will increase to incredible great sizes. It is a problem, because size of database is increased accordingly and if we want, for example, to find some certain records, even with WHERE filters and implemented indexes, process of finding may take a lot of time and at this moment database will be under heavy loading, it is very actual, for example, at case of queries with LIKE statement.

At most cases we don't need to keep all logs for all time from beginning. We can periodically delete previous rows, moreover we can make some kind of archivation, that means selecting of old rows, it's archivation, for example, into .zip or .rar files and deletion them from database. So we always will have log table(s) with reasonable size and all previous data will be stored at separated place, from which we can restore them at any moment.

But at this case there are some problems: SELECT and DELETE operation, which will affect on great number of rows at always busy log table(s)(because it is actual log table(s), into which records are inserted all the time with great frequency) will cause performance issues, database loading, and they will be very very slow. 

Solution

You can still implement archivation approach, but another way. Instead of reading, archivating and deleting rows from table(s) you can just periodicaly rename this table, for example, every midnight rename table to MyTableName_ddMMyyyy format. So each day you will have new separated archived table, which is fully free from external pressure and isolated, then you can do whatever you want with it: further archivation or leave it alone without any problems.

But when we rename current table, what will do EF when will attempt to insert next record into table? Yes, error will be thrown, because object(table) is not exists. And this article is dedicated to explain and show how to dynamically (re)create not existed table via EF environment.

Solution is presented as implementation of widely known repository pattern, from which class you can inherit your own repository.

Implementation

Solution is presented as Nuget package, which you can find here https://www.nuget.org/packages/FenixRepo/ 

Install-Package FenixRepo

and source code here: https://github.com/SlavaUtesinov/FenixRepo. 

We have three most important classes from `FenixRepo.Core` namespace, each of them is inherited from previous. They are: FenixRepositoryScriptExtracto, FenixRepositoryCreateTable<T> and FenixRepository<T>. Let's consider them one by one.

FenixRepositoryScriptExtractor

Contains static "global" Initialize method, with two arguments: contextFactory - action for context creation and contextConfiguration - instance of your configuration class, which is usually located at /Migrations folder. This method should be called at first, at case of ASP.NET MVC most preferable place for it is Global.asax. It prepares SQL scripts for all tables(tableTypes) with indexes. 

public static void Initialize<TDbContext>(Func<TDbContext> contextFactory, DbMigrationsConfiguration<TDbContext> contextConfiguration) where TDbContext : DbContext
{
    Factory = contextFactory;
    Configuration = contextConfiguration;

    //Tables are DbSet<T> properties of Context class:
    var tableTypes = typeof(TDbContext).GetProperties().Where(x => x.PropertyType.FullName.Contains("System.Data.Entity.DbSet")).Select(x => x.PropertyType.GenericTypeArguments.First()).ToList();
    
    var migrator = new DbMigrator(Configuration);
    var scriptor = new MigratorScriptingDecorator(migrator);
    var migrationScript = scriptor.ScriptUpdate("0", null);

    using (var context = contextFactory())
    {
        var baseScripts = ((IObjectContextAdapter)context).ObjectContext.CreateDatabaseScript();

        Scripts = tableTypes.ToDictionary(x => x, x => {
            var tableName = GetFullTableNameAndSchema(x, context);
            return new FenixScript
            {
                TableScript = ExtractTableScript(tableName, baseScripts),
                FkScripts = ExtractFkScripts(tableName, baseScripts),
                IndexScripts = ExtractIndexScripts(tableName, migrationScript)
            };
        });
    }
}

FenixRepositoryScriptExtractor uses IObjectContextAdapter.ObjectContext.CreateDatabaseScript method to get SQL script for all tables. It use methods: ExtractTableScript, ExtractFkScripts to extract corresponding SQL parts for particular table. As CreateDatabaseScript not provides SQL for indexes we will use MigratorScriptingDecorator.ScriptUpdate method for that (it returns SQL scripts for all migrations, which, sure, include indexes) and ExtractIndexScripts. Then method saves these pairs: TableType per SQL script into Scripts dictionary.

There is one important note: database can't contain constraints (PK, FKs) with same names. So we can't just execute taken scripts, we should always assign to all of them new unique names with the help of Guid.NewGuid():

public string getBaseScript()
{               
    //new name for PK constraint 
    var answer = Regex.Replace(TableScript, "(?i)primary key", m => $"constraint [{Guid.NewGuid()}] {m.Value}");
    //new names for FKs
    if (FkScripts.Count > 0)
        answer += FkScripts.Select(x => Regex.Replace(x, @"(?i)(?<=add constraint\s+\[).+?\]", m => $"{Guid.NewGuid()}]")).Aggregate((a, b) => $"{a}\r\n{b}");                
    return answer;
}

GetFullTableNameAndSchema method is used to get actual table and schema names via type (solution was found here https://romiller.com/2014/04/08/ef6-1-mapping-between-types-tables/).

FenixRepositoryCreateTable<T>

Creates selected table and indexes with help of "default" Scripts, which I just explained or from migrations:

private void CreateTableInner(DbContext context)
{            
    var migrationScript = GetScriptFromMigrations();   
    //if class has FenixAttribute, we will use it
    if (migrationScript != null)
        context.Database.ExecuteSqlCommand(migrationScript);            
    else
    {                
        //if not, we will get scripts from "default" Scripts
        var info = Scripts[typeof(T)];
        var toExclude = new List<string>();                
        context.Database.ExecuteSqlCommand(info.getBaseScript());
        foreach (var index in info.IndexScripts)
        {
            try
            {
                context.Database.ExecuteSqlCommand(index);
            }
            catch (SqlException e) when ((index.ToLower().Contains("create") && e.Number == 3701) || (index.ToLower().Contains("drop") && e.Number == 1911))
            {
                toExclude.Add(index);
            }
        }
        toExclude.ForEach(x => info.IndexScripts.Remove(x));
    }
}

 If your table scripts are located at several migrations and there is nothing stuff except related to this particular table in their, you can specify these migration with the help of FenixAttribute this way:

[Fenix(nameof(FirstMigrationName), nameof(SecondMigrationName))]    
public class Order
{
     //some stuff...
}

At this case exactly these migrations will be used for table creation. SQL scripts for them will be taken this way: 

private string GetScriptFromMigrations()
{
    var type = typeof(T);
    var fenixAttr = type.CustomAttributes.Where(x => x.AttributeType == typeof(FenixAttribute)).FirstOrDefault();
    if (fenixAttr != null)
    {
        var migrations = (fenixAttr.ConstructorArguments.First().Value as ReadOnlyCollection<CustomAttributeTypedArgument>).Select(x => x.Value.ToString()).ToArray();
        
        var migrator = new DbMigrator(Configuration);
        var allMigrations = migrator.GetLocalMigrations().ToList();
        var scriptor = new MigratorScriptingDecorator(migrator);

        string allMigrationScripts = null;
        foreach (var migration in migrations)
        {
            var target = allMigrations.Where(x => x.Contains(migration)).First();
            var targetIndex = allMigrations.IndexOf(target);
            //source is previous migration
            var source = targetIndex == 0 ? "0" : Regex.Match(allMigrations.Where(x => allMigrations.IndexOf(x) == (targetIndex - 1)).First(), @"(?<=\d+_).+").Value;                    
            string script = scriptor.ScriptUpdate(source, target);
            allMigrationScripts += $"{ExtractScriptFromMigration(script, source)}{"\r\n"}";
        }
        return allMigrationScripts.Trim();
    }
    return null;
}

and extracted with the help of FenixRepositoryScriptExtractor.ExtractScriptFromMigration method, then they are concatenated and executed.

FenixRepository<T>

Main method is BaseWrapper it is some kind of template for Add and AddRange methods, which are passed to it as function argument:

//where T is table type
public T Add(T item)
{
    return BaseWrapper(table => table.Add(item));
}

public IEnumerable<T> AddRange(List<T> items)
{
    return BaseWrapper(table => table.AddRange(items));
} 

TResult BaseWrapper<TResult>(Func<DbSet<T>, TResult> function) where TResult : class
{            
    using (var context = Factory())
    {
        var table = context.Set<T>();
        TResult answer = null;
        try
        {                    
            answer = function(context.Set<T>());                    
            context.SaveChanges();
            return answer;
        }
        catch (Exception e) when (tableNotExistsException(e))
        {                    
            lock (typeof(T))
            {
                try
                {                            
                    context.SaveChanges();                            
                    return answer;
                }
                catch (Exception e2) when (tableNotExistsException(e2))
                {
                    CreateTable(context);                                
                }                            
            }
            return BaseWrapper(function);                                        
        }
    }
}

If intending to insert record(s) ends with error like "There is not table object at database", it acquires a lock, try one more time and, if result is same, CreateTable(context) is invoked.

Usage

First of all you should call Initialize method:

FenixRepositoryScriptExtractor.Initialize(() => new Context(), new Configuration());

That is all, then you can use it this way:

var repository = new FenixRepository<Person>();

//Person table will be created, if it is needed
repository.Add(person);
//or
repository.AddRange(people);

Also you can simple create table:

repository.CreateTable();

Restrictions and notes

1. It is alway better to create tables from migrations via attribute rather then from "default" Scripts, because at last variant it is not quaranted, that all indexes will be created and also migrations can contain some custom code for triggers, views and so on; they will not be found by "default" approach.
2. Target table can't be principal for any another table or you should work around this situation on your own.
3. Methods: Add and AddRange are thread safe at context of table creation.
4. Solution tested with EF 6.1.3 at case of MS SQL

Conclusions

At this article I shown you how to (re)create tables dynamically from migrations code or via "default" approach. This solution can be usefull, for example, at case of logging and archivation problem, when total size of logs is incredibly great and you want to quickly operate with them and partiating them on some logical parts, to later work with these isolated parts. Also you can simple create table, if it is needed.