Entity Framework Database Initialization

Initializing a database with Entity Framework is essential. It creates the database, tables and all constrains. In a previous article, we saw that is it possible to generate the database when the context specify to the database to initialize itself or when adding an entity to the database. However, when the database exist and that we have changed model classes, how can we control how the database schema is altering? This is the goal of this article. We will see how we can control Entity Framework to create and especially how to update database schema.

We have already see the default initializer. It is named CreateDatabaseIfNotExists. This is only interesting when you start a brand new application. The reason is that a small change like adding a new property to a class will throw an exception. The reason is that Entity Framework realize that the model from the code is not anymore synchronized with the table schema. This result to an InvalidOperationException that look like this:

Additional information: The model backing the ‘YourContext’ context has changed since the database was created. Consider using Code First Migrations to update the database.

InvalidOperationExceptionModelBackingChanged

If you want to use that type of initializer, you have to manually delete the whole database for every modification of your context. For your information, the default intializer could also be explicitly specify in the contructor of your DbContext.

public YourContext(): base("DefaultConnection")
{
    Database.SetInitializer<YourContext>(new CreateDatabaseIfNotExists<YourContext>()); //Default one
}

Instead of having to delete yourself every times the database, you can use the second initializer that will drop for you the database if any changes has been detected. This second initializer the drop the database for you is named DropCreateDatabaseIfModelChanges.

public YourContext(): base("DefaultConnection")
{
    Database.SetInitializer<YourContext>(new DropCreateDatabaseIfModelChanges<YourContext>()); //Drop database if changes detected
}

It is important to note that this initializer will not delete the database if you do not change model. If you run your application multiple time, it could start to have a lot of data that you may not want. In the scenario you would prefer to erase every data of your database even if you do not change the model, than you have to use a third initializer. DropCreateDatabaseAlways drop the database every time the application start.

public YourContext(): base("DefaultConnection")
{
    Database.SetInitializer<YourContext>(new DropCreateDatabaseAlways<YourContext>()); //Drop database every times
}

The problem with all these initializer is that all of them at some point let you down with default value. You have to have a SQL script to fill up tables with initial data. This is where the most useful initializer exist. It is the custom initializer. You can create you own initializer and inherit from one of the three we just discussed. For example, you can drop all the database every times the schema as changed which will create all tables but also in this custom initializer tell Entity Framework to insert default demo values. You can also create one that does not inherit any of the three but inherit from IDatabaseInitializer. The advantage of inheriting from an existing initializer is that you can use the leverage of basic functionnality and only override the Seed method to push data into the website.

public YourContext(): base("DefaultConnection")
{
    Database.SetInitializer<YourContext>(new CustomInitializer<YourContext>()); //Custom if model changed and seed values
}
//...
public class CustomInitializer<T> : DropCreateDatabaseIfModelChanges<YourContext>
{
    protected override void Seed(YourContext context)
    {
        base.Seed(context);
    }
}

The CustomInitializer method override the Seed method. This is where you can use the context passed by parameter to insert data into your database. You do not have to call SaveChanges to save anything because the class that call the seed method call the SaveChanges right after calling the seeding method.

CustomInitializer

If the database already exist, you may stumble into the case of having an error. The exception “Cannot drop database because it is currently in use” can raise. This problem occurs when an active connection remains connected to the database that it is in the process of being deleting. A trick is to override the InitializeDatabase method and to alter the database. This tell the database to close all connection and if a transaction is open to rollback this one.

public class CustomInitializer<T> : DropCreateDatabaseAlways<YourContext>
{
    public override void InitializeDatabase(YourContext context)
    {
        context.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction
            , string.Format("ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", context.Database.Connection.Database));
        
        base.InitializeDatabase(context);
    }

    protected override void Seed(YourContext context)
    {
        var person = new Person() {Name = "SeededPerson", BirthDate = new DateTime(1900, 1, 1)};
        context.Persons.Add(person);
        base.Seed(context);
    }
}

The last configuration is to remove all initialization. This will not check if the database has changed, neither check if something is not synchronized between tables and model classes. This is perfect if you have an existing database that is not handled by Entity Framework for the creation and insertion of initial values.

public YourContext(): base("DefaultConnection")
{
    Database.SetInitializer<YourContext>(null); //No initialization
}

Before concluding, I have to say that any of the initializer should have been set in the static constructor of your context. The static constructor is called before any constructors and is executed once. This is what we want. The reason is that in some of your application, you may initialize more than once the context. You do not want to execute all the process to check if the database if ready to be changed or not.

static YourContext()
{
    //Database.SetInitializer<YourContext>(new CreateDatabaseIfNotExists<YourContext>()); //Default one
    //Database.SetInitializer<YourContext>(new DropCreateDatabaseIfModelChanges<YourContext>()); //Drop database if changes detected
    //Database.SetInitializer<YourContext>(new DropCreateDatabaseAlways<YourContext>()); //Drop database every times
    //Database.SetInitializer<YourContext>(new CustomInitializer<YourContext>()); //Custom if model changed and seed values
    Database.SetInitializer<YourContext>(null); //Nothing is done
}

We have see how to use Entity Framework to initialize a database but also how to seed it with testing values. We have see many different way to initialize the database, tables and model classes with Entity Framework. So far, we have not discussed about Entity Framework Migration Tool to initialize the database. This tool allow to have manual call to with Entity to the database to perform initialization. It has the advantage to have the full control of when and what is done but has the disadvantage to have more to do. Information about Migration tool will be discussed later. You can find all code discussed in this article at GitHub or download the Zip file here.

Entity Framework Database Setup for Code First

This article is part of a series of Entity Framework articles. This first article is how to generate the database from mode classes.

First, let’s define model classes that will be used across all articles.

public class Person
{
    public int Id { get; set; }
    public int Name { get; set; }
    public DateTime BirthDate { get; set; }

    public ICollection<Person> Friends { get; set; }

    public House Residence { set; get; }
}

public class House
{
    public int Id { get; set; }
    public double Price { get; set; }

    public Address Address { get; set; }

    public ICollection<Person> Resident { get; set; }
    public Person Owner { get; set; }
}

public class Address
{
    public string Street { get; set; }
    public int Number { get; set; }
    public string City { get; set; }
}

But, first we will comment the Address class and all references to it. The reason is that we do not want for the moment to configure anything.

Then, we need to add Entity Framework to your project.

EntityFrameworkAddNugetReference

The last step is to tell Entity Framework to initialize the database. This will create the database and all tables defined by the DbContext.

public static void BuildDatabase()
{
    using (var context = new YourContext())
    {
        context.Database.Initialize(true);
    }
}

We could also add an Entity to the database to generate the database automatically. This would produce the same result.

public static void BuildByAddingEntity()
{
    using (var context = new YourContext())
    {
        var stud = new Person() { Name = "Person1", BirthDate = new DateTime(1990, 01, 01) };
        context.Persons.Add(stud);
        context.SaveChanges(); 
    }
}

The result is not very interesting because it creates something without the help of any connection string. This mean that the database name is generated with the context name and the namespace. For example, the code executed generate a database would be “EntityFrameworkTestConsole.DataAccessLayer.YourContext”. To fix this issue, we need to define a connection string and than set it in the YourContext. It also has used the default Sql Server defined for your machine. In general, it is SQL Express. This is very restrictive.

SqlManagerEntityFrameworkDatabaseName

If you setup a connection string with a name, for example you could use “DefaultConnection”, this one will be used if set in the context.

The app.config is where the connection string can be defined.

  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" 
             type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" 
             requirePermission="false" />
  </configSections>
  <connectionStrings>
    <add name="DefaultConnection" 
         connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=EntityFrameworkTestConsole;Integrated Security=True" 
         providerName="System.Data.SqlClient" />
  </connectionStrings>
public class YourContext : DbContext
{
    public YourContext(): base("DefaultConnection")
    {

    }
    public DbSet<Person> Persons { get; set; }
    public DbSet<House> Houses { get; set; }
}

This generate the database with the name specified in the Catalog from the connection string. You could also specify directly the connection string into the base() but this will be a show stopper if you are building application that must change the server and database depending of the environment (dev, test, prod).

SqlManagerEntityFrameworkDatabaseName2

Entity Framework takes care of everything for you. Tables are created from your entity defined in your context. It also takes care of all primary keys and foreign keys for you.
DatabaseTables

So far, we have see that Entity Framework can generate the database from minimalist code. Only a context need to be defined. Of course, setting a connection string and associate the connection string name with the context give you more control to which server and database to connect Entity Framework.

You can find this code at GitHub. This version of code can be downloaded in a Zip file here.

Asp.Net Web Api Getting 401unauthorized code

If you are creating a brand new Web Api and try to execute a POST query with Web Api you might receive a 401 status code from the request. This might be because the security of your service is not set correctly. Open the Web Api project (hit F4 when having the project selected from the Solution Explorer).

PropertyForWebApiProject

The second thing that you have to ensure is that you are requesting a json call. This mean that you must have in the request header the content-type set to json.

User-Agent: Fiddler
Host: localhost:17420
Content-Length: 16
Content-Type: application/json

For example, running a post on an action that take a single string require Fiddler to have the Request Header set with the previous 4 lines and the request body of the string desired. For example “This is a test”.

public void Post([FromBody]string value)
{
}

FiddlerSingleStringPost

For a more exhaustive test, you can send an object. This require you to use the JSON syntax. The Web Api Controller also requires to have the class defined and the parameter to use this class.

public void Post([FromBody]DTOExecutionParameters value)
{
}
public class DTOExecutionParameters
{
   public string  NameTask { get; set; }
   public string  ParameterName { get; set; }
}

WebApiFiddlerPost

This request has the status 200 and if you set a breakpoint in Visual Studio you can see the values.

PostValueInTheControllerWebApi

CreateIdentityAsync value cannot be null when logging with User created with Migration Tool

If you create a user from your Asp.Net MVC (Identity) and logging with this one, it should work. However, if you create your users by code, for example with Entity Framework Migration seeding method, you may have for result an error page. The error is not very clear. It can say that the value cannot be null and that the parameter name is value. This does not give a lot of information about where the error is. The stack trace shows some information about security claim and the problem is trigged by the call to the UserManager CreateIdentityAsync method.

CreateUserError

In fact, the problem is that the Security Stamp was not set. This was found after seeing a difference in AspNetUsers table.

SecurityStamp

As you can see, the column was not set for user created by Entity Framework Migration Seed method.

To solve this issue, the seed method changed to generate a Security Stamp. The security stamp can be generated with a random GUID.

var adminUser = new ApplicationUser { Id = Guid.NewGuid().ToString()
	, FirstName = "Patrick"
	, LastName = "Desjardins"
	, DisplayName = "pdesjardins"
	, Email = EMAIL_ADMIN
	, CreationDateTime = runningContext.GetCurrentTime()
	, UserName = userValueGenerator.GenerateUserName(EMAIL_ADMIN), PasswordHash = ApplicationUser.HashPassword("123123")
	, ValidationDateTime = runningContext.GetCurrentTime()
	, SecurityStamp = Guid.NewGuid().ToString()
};

With the Security Stamp set, it is possible to login without having CreateIdentityAsync raising any exception.

Linq to Entity to get the number of day between two dates

If you want to have the amount of day between two dates, you can get those dates back to C# and do the difference between them. However, it is possible to tell Sql Server to do it for you by Linq To Entity.

It has change since Entity Framework (EF) 6. You have to use DbFunctions class now to execute complex operations to Sql Server.

The operation that interest us is named DiffDays. It takes two parameters. The first one is the smallest date, the second one is the biggest date. If you invert these parameters, the count will be negative.

Here is an example of use:

 var allActiveContest = GetAllActiveContestForUser(userId)
                .Select(d=>new ContestWithSmallStatistics(){
                               Id=d.Id,
                               Name=d.Name,
                               NumberOfDaysRemainingBeforeEndDate = DbFunctions.DiffDays(d.RegistrationRules.StartingTime,d.RegistrationRules.EndingTime).Value,
                               NumberOfUsersRegistered = d.Contester.Count
            });

The main advantage is that you can have from the database exactly what you want instead of having a class that would have two dates. You could also use it for having some logics that will not return everything. For example, we could return only Active Contest for contest that has more than 30 days.

AutoMapper and constructor with parameters

In some use case you are forced to have classes that has constructor with parameters. This is more rare if you are using Entity Framework (EF) because it requires to have parameterless constructor. However, if the scenario occurs, remember that AutoMapper does not have this constrain. In fact, you can have a private parameterless constructor for Entity Framework (EF) and force the use of a public constructor with parameters when using in your code.

First of all, during the mapping configuration, you must use ConstructUsingServiceLocator method.

Mapper.CreateMap<ContestEditableViewModel, Model.Entities.Contest.Contest>()
      .ConstructUsingServiceLocator();

This instruct AutoMapper to check for the option ConstructServicesUsing method that can be provided during the mapping instruction.

Second, when you are using AutoMapper to map between classes, you must specify every parameters.

var model = AutoMapper.Mapper.Map<ContestEditableViewModel, Model.Entities.Contest.Contest>(viewModel
                , options=>options.ConstructServicesUsing(
                    t=>new Model.Entities.Contest.Contest(yourFirstParameter, yourSecondParameter, /*and so on*/)
                )
            );

This way, you can have classes that have parameters and control how to provided them values.

Truncate a table without removing all constraints

Truncated a table as the advantage to remove every data without logging or locking every rows. This is a huge advantage over the Delete statement. This mean that truncated table cannot be restored, neither you can have a where clause into the SQL statement. Truncate also has the ability to reset the seed to its initial value. On delete triggers are also not fired and all foreign keys constraint must be removed or disabled.

Instead of removing all constraints, it is possible to tell SQL Server to not check foreign key.

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE [Schema].[Table]; 
SET FOREIGN_KEY_CHECKS = 1;

This will result to remove data of the table in the fastest way possible. If you want to have more details you can check that article that I previously wrote about Truncate vs Delete.