Help with setting up Dbcontext with Entityframework using good practices

Question

I have my application divided into layers with Class Library and my layer with the Entity Framework, where I set up the DbContext, is the Repository.

My inherited Dbcontext class:

public class Context : DbContext
{
    private static string connectionString = "Data Source=(local); Initial Catalog=CRM; Integrated Security=True;";
    public Context() : base(connectionString)
    {
        // Set the auto versioning
        Database.SetInitializer(new MigrateDatabaseToLatestVersion<Context, Migrations.Configuration>()); 
        // Gets or sets the database initialization strategy. 
        Database.SetInitializer<Context>(new Repository.DatabaseAdditionalConfigurations<Context>());
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // Pluralizing
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        // Non delete cascade
        modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
        // Configure Pessoa
        modelBuilder.Configurations.Add<Domain.Pessoa>(new Domain.PessoaTypeConfiguration());

        base.OnModelCreating(modelBuilder);
    }

    #region DbSet´s ...
}

Note that the first line of the constructor is for auto database migration using the Migrations: Database.SetInitializer(new MigrateDatabaseToLatestVersion<Context, Migrations.Configuration>());.

Is this a good time in the class life cycle for this line to be? Or should it be out in another context?

And I also created the class UniqueKeyAttribute to configure the fields that are unique in my database:

using System;

namespace Domain.Attributes
{
    ///<summary> 
    ///A unique attribute 
    ///</summary> 
    public class UniqueKeyAttribute : Attribute { }
}

And for that attribute to be translated into the database I added the following class, which unfortunately I can’t remember the source:

public class DatabaseAdditionalConfigurations<T> : IDatabaseInitializer<T> where T : DbContext
{
    public void InitializeDatabase(T context)
    {
        var created = false;

        if (context.Database.Exists())
            created = true;
        else
            context.Database.CreateIfNotExists();

        if (!created)
            CreateUniqueKeys(context);
    }

    public void CreateUniqueKeys(T context)
    {
        //Fetch all the father class's public properties 
        var masterProperties = typeof(DbContext).GetProperties().Select(x => x.Name).ToList();

        //Percorre cada DBSet<> do DbContext
        foreach (var item in typeof(T).GetProperties().Where(p => masterProperties.IndexOf(p.Name) < 0).Select(x => x))
        {
            //busca o tipo de "T" 
            Type entityType = item.PropertyType.GetGenericArguments()[0];
            // Cria as chaves únicas
            var fields = from f in entityType.GetProperties()
                         where f.GetCustomAttributes(typeof(Domain.Attributes.UniqueKeyAttribute), true).Count() > 0
                         select f.Name;

            var uniqueKeys = "";
            foreach (string s in fields)
            {
                if (string.IsNullOrEmpty(uniqueKeys) || string.IsNullOrWhiteSpace(uniqueKeys))
                    uniqueKeys = s;
                else
                    uniqueKeys += ", " + s;
            }
            if (!string.IsNullOrEmpty(uniqueKeys) && !string.IsNullOrWhiteSpace(uniqueKeys))
                context.Database.ExecuteSqlCommand("alter table " + entityType.Name + " add unique(" + uniqueKeys + ")");
        }
    }
}

Well, first I have a problem. Now that I’ve added control with Migrations i don’t know at what point I can put the setting Database.SetInitializer<Repository.Context>(new Repository.DatabaseAdditionalConfigurations<Repository.Context>()); so that the method CreateUniqueKeys can create unique keys in the bank. How could I treat this? It would be in the method Seed class Configuration of Migrations?

Another issue is that my database was created automatically only when AutomaticMigrationsEnabled = true; Migrations Configuration class constructor was set to true. Do you really need this, or am I doing something wrong? Going down this path is good practice?

Remembering here about the question of the ideal location for: Database.SetInitializer(new MigrateDatabaseToLatestVersion<Context, Migrations.Configuration>());.

Answer 1

Connectionstring in Context Statement

It is not good practice. By posting your site on a different basis (as in Azure, for example), the system would no longer function properly. Ideally you set your context as follows:

public Context() : base("name=SeuSistema")

And in the Web.Config from the root, have this setting:

<configuration>
    <connectionStrings>
        <add name="SeuSistema" connectionString=""Data Source=(local); Initial Catalog=CRM; Integrated Security=True;" providerName="System.Data.SqlClient" />
    </connectionStrings>
</configuration>

Remove Pluralization of Tables

This isn’t exactly good practice. It only prevents the Framework from pluralizing its tables, which can get weird if you write the system in English (for example, a Model called Contato would have a controller called ContatoesController).

The easy and simple way to solve the database pluralization problem is by using [Table].

Remove Cascade Delete

This isn’t exactly good practice either. Of course the Cascade on delete is a dangerous option, but not using this option can create strange inconsistencies. The ideal is not to allow deletion of records who have a lot of dependent data.

SetInitializer

The command only indicates that all Migrations pending must be executed at system startup (does not require the use of command Update-Database). Not exactly a good practice because it can perform wrongly Migrations automatic, which can generate unpredictable results in case of frequent changes of templates. This setting is not required.

The source Migrations\Configuration.cs is already executed when the command is executed Update-Database and the publication of its system using the Web Deploy. The method Seed() already performs minimal database data insertions and modifications and it is not necessary to implement a IDatabaseInitializer whole for startups, unless you need a very specific behavior that normal startup does not, which I find very difficult.

CreateUniqueKeys

The MVC is designed for Surrogate Keys, I mean, primary keys that are already unique. The idea of other unique keys serves well only if it is in the programmer’s interest to identify two columns as keys, being one independent of the other. For this, just use decorate all the desired attributes with [Key].

For example, if I have a model called Fabricante and I do not wish two manufacturers to have the same name. Nome not exactly the key. In this case, use [Index(IsUnique=true)].

However, still the creation of a constraint unique goes against the idea of Entity Framework, that the database should be agnostic and not all databases support unique. Within the MVC approach, still using the example of Fabricante, the ideal is to check if there is a record that has the same name as the record to be inserted/updated.

Automatic Migrations

As stated above, the mechanism of Automatic Migrations can be useful only if there is no need for a rigid control of the state of the database (for example, if the scope has not yet been very well defined, if there are few programmers working on the project, and if the project is right at the beginning). At the developmental stage, this can be very useful.

However, it is not recommended to use this configuration if your system already has a production instance. In this case, it is best to use Migrations manuals, generated by users.