understanding Entity Framework databases and models

[Violet_82]

HI all, I have a question about how Entity Framework deals with databases and models, especially when it comes down to change your model after the database has been created.
Let's look at a very simple example. I have an EmployerDetails class as below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace input_outputTest.Models
{
    public class EmployerDetails
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public string Surname{ get; set; }
        public int Age { get; set; }
        public int EmployerNumber { get; set; }
    }
}

Then I create my controller EmployerController the usual way, by selecting the "with a MVC read/write actions and views, using EF" template and using the class as model class and creating a new data context class.
OK now, I run the application and everything's cool. Say that now I want to add another class though, called EmployeeDetails very similar to the above one, like so:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace input_outputTest.Models
{
    public class EmployeeDetails
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public string Surname { get; set; }
        public int Age { get; set; }
        public int EmployeeNumber { get; set; }
    }
}

Now, I presume, I need this data to go on a separate table in the same database, but when I create its own controller the same way as I did for the other class, when I run the application I get an error, saying that "context has changed since the database was created. Consider using Code First Migrations to update the database". From what I read, it's normal to get that error because effectively there is a mismatch between the database and the model - in other word, I seem to understand, the EF database is out of sync with the models. I read about migration, ateempted it but it didn't work, but that's not really the reason for my post, not yet at least: what I want to understand is, how does this new class fit in the EF? Should it sit into a different table or what? SHould it have its own controller, as it has now, or not? Am I getting the structure of the application - any application for that matter not just this - completely wrong? What if I want to add another class at a later date, do I have to go through this hell? I would have thought that migration needed to be done only if you changed a model, not if you added another one: for example, take the first class: if I add an extra field, something like

public string Position{get; set;}

then migration must be enabled because the table is missing that new field, but evindetly it's not like that.
Can somebody clarify please and then if the solution is to enable migration I will let you know what I've done and hopefully we can understand why the database isn't updating.
thanks

[djjeavons]

Hi

In the most simple terms, a model can be considered a table in the database. When you first create your application and your first model, EF will create the database and the table that maps to the model for you. Subsequent changes to that model (addition of a new property for example) will require that you update the structure of that table to accommodate the new field, hence the reason for code migration.

However, the same is true when you introduce a new model (that is using a data context) as a new table needs to be created in the database.

With regards to structuring your application, consider that you were not using a Code First approach and designing the database yourself. Given your example of Employer and Employee models this would suggest a natural relationship between one Employer and one or more Employees. Therefore, you would most probably (for normalisation reasons) create a table that stored Employer details and one that stored Employee details. Maybe with a simple structure like:

Table: Employer
Fields:

EmployerID
EmployerName

Table: Employees
Fields:

EmployeeID
EmployeeName
EmployerID <-- This would be a foreign key relationship to the Employer table

There would be better ways to structure the above but this is an example. So given that this is how you might design it in a database, when it comes to creating the models to store this structure you might end up with classes such as:

public class EmployerDetails
{
    public int EmployerID { get; set; }
    public string EmployerName { get; set; }
    public List<EmployeeDetails> Employees { get; set; }
}



public class EmployeeDetails
{
    public int EmployeeID { get; set; }
    public string EmployeeName { get; set;}    
}

In the above, note that the EmployerDetails class has a proeprty called Employees which is a list of EmployeeDetails. That is, one Employer instance can have one or more Employee instances.

So, going back to a Code First approach, you need to consider the relationships you have between data and define your classes to accommodate these relationships.

Please note that I am by no means an expert in Entity Framework as I have hardly used it, but I did a little test in a new project based on your example. Below is what I learned and will hopefully help you. Firstly, create a new project to test this out.

When the project has loaded, ensure you have the Server Explorer window open (View > Server Explorer).

Now, create the EmployeeDetails model:

    public class EmployeeDetails
    {
        public int EmployeeDetailsID { get; set; }
        public string EmployeeName { get; set; }
    }

Then build the application before creating the controller (with views, using EF): EmployeeDetailsController, based on the EmployeeDetails model and using the ApplicationDbContext data context class. At this point, nothing much has happened with regards to the Database, in fact it doesn't even exist yet as can be witnessed by navigating to the project, App_Data folder.

Now run the application and assuming that the site is loaded at the root (localhost:portnumber) note again that there is still no database. Now navigate to the EmployeeDetails route. At this point, EF will create the database as it is the first time that it has been requested to interact with the ApplicationDBContext.

Close the application and return to the code. In the Server Explorer, you should notice a new connection under the "Data Connections" node called DefaultConnection. Expand this node and expand the tables node and you will see a table for EmployeeDetails with two fields matching the EmployeeDetails model:

Now, create a new model for the EmployerDetails:

    public class EmployerDetails
    {
        public int EmployerDetailsID { get; set; }
        public string EmployerName { get; set; }
    }

Build the application and run it. Note nothing changes in the database and no errors occur. Now go back to the code and add a new controller: EmployerDetailsController, based on the EmployerDetails model and using the ApplicationDbContext data context. Run the application and again, assuming the start point is the root of the application, then nothing has changed and no errors should occur. Now navigate to EmployeeDetails or EmployerDetails and you will get the error regarding code migration. So effectively, as soon as EF is engaged, it will look at what is mapped to the data context and will know if it is out of date or not. In this case, it is out of date due to the new model that we have added along with the controller.

So to do the code migration, open the Package Manager Console (View > Other Windows > Package Manager Console) and enter:

Enable-Migrations

This will create a Migrations folder in your solution and should give you a couple of highlighted warnings in the Package Manager Console stating that you should delete this folder and run Enable-Migrations again but adding the -EnableAutomaticMigrations parameter. So, delete the Migrations folder then run:

Enable-Migrations -EnableAutomaticMigrations

Now, run the Update-Database command in the Package Manager Console.

Now the database has been updated and you can navigate to the EmployeeDetails or EmployerDetails views. Go back to the Server Explorer and refresh the Tables node. You should also now see two tables:

Next, if we modify the EmployerDetails model to store a list of related Employees, as follows:

public class EmployerDetails
{
    public int EmployerDetailsID { get; set; }
    public string EmployerName { get; set; }
    public List<EmployeeDetails> Employees { get; set; }
}

Build the application and run it. Navigate to one of the views and again we get the error. So run the Update-Database command again. This time, we can see the change in Server explorer when we refresh the Tables node:

Note how we added the property to the EmployerDetails class but EF modified the EmployeeDetails table and added the foreign key EmployerDetails_EmployerDetailsID to this table. This makes sense as an Employee in this example works for one Employer but an Employer can have one or more Employees.

Hopefully this gives a bit more understanding of what is going on under the hood.

[Violet_82]

djjeavons, thank you very much for your replying and for taking time to run the test. Now I do understand the EF a bit better, especially when it comes to tailor the classes for it.
Now, I followed the procedure described above, but the results are somewhat different. All was well till the migration: when I enable the migration, it asks me to define a context type, which I have done:

PM> Enable-Migrations
More than one context type was found in the assembly 'EntityFrameworkTest'.
To enable migrations for EntityFrameworkTest.Models.UsersContext, use Enable-Migrations -ContextTypeName EntityFrameworkTest.Models.UsersContext.
To enable migrations for EntityFrameworkTest.Models.EntityFrameworkTestContext, use Enable-Migrations -ContextTypeName EntityFrameworkTest.Models.EntityFrameworkTestContext.
PM> Enable-Migrations -ContextTypeName EntityFrameworkTest.Models.UsersContext
Checking if the context targets an existing database...
Code First Migrations enabled for project EntityFrameworkTest.

But I get no warning, in any case I followed your method, deleted the folder and run

PM> Enable-Migrations -EnableAutomaticMigrations -ContextTypeName EntityFrameworkTest.Models.UsersContext
Code First Migrations enabled for project EntityFrameworkTest.
PM> 

Then:

PM> Update-Database
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
No pending code-based migrations.
Running Seed method.
PM> Update-Database -Verbose
Using StartUp project 'EntityFrameworkTest'.
Using NuGet project 'EntityFrameworkTest'.
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
Target database is: 'aspnet-EntityFrameworkTest-20150529080853' (DataSource: (LocalDb)\v11.0, Provider: System.Data.SqlClient, Origin: Configuration).
No pending code-based migrations.
Running Seed method.
PM> 

But the database unfortunately hasn't changed, in fact I still get the error if I navigate to any of the two controllers.

[djjeavons]

Are you sure you want to be using multiple context types? The reason I ask is that for each context type created, EF will create a new database. This is at least the default behaviour when using a Code First approach. Using a database first approach allows you to create multiple contexts pointing to the same database. There are ways around this which I haven't yet looked into but I don't believe you want multiple databases in this instance.

[Violet_82]

No no, I've been meaning to use one of course, my mistake sorry. So, let me see if I get this right, when I created the two controllers, I have created two separate contexts rather than using one, correct?
I will re-create the whole test project then, making sure I select the same context

[djjeavons]

That's right. Each time you create a controller you can choose an existing context or create a new one. In your case, you want to choose the existing context. EF will then update or add new tables to one single database.

[Violet_82]

OK, my mistake then, apologies. Is there anyway I can change the context after the controller's been created, or should I just redo the whole application?

[djjeavons]

Yes, I believe you can. Obviously I don't know what contexts you have at this stage so lets just assume that you have two: ApplicationDbContext and TestContext.

When you open your controller, you will see a line near the top that specifies the context to be assigned to the db variable. It looks like:

private ApplicationDbContext db = new ApplicationDbContext();

Some of your controllers will use private TestContext db = new TestContext(). So the first change will be to change those that do not use ApplicationDbContext (or whichever context you are going to keep) to use ApplicationDbContext. Now, when you make this change, you will get a number of errors, specifically to do with the Models that are referenced within the controllers. This is because they do not belong to the context that you have just changed. To fix this, open the IdentityModel.cs file which can be found in the Models folder and will look similar too:

    public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
    {
        public ApplicationDbContext()
            : base("DefaultConnection")
        {
        }

        public System.Data.Entity.DbSet<Violet_82_UnderstandingEFDBAndModels.Models.EmployeeDetails> EmployeeDetails { get; set; }

        public System.Data.Entity.DbSet<Violet_82_UnderstandingEFDBAndModels.Models.EmployerDetails> EmployerDetails { get; set; }
    }

Note how the models are just properties of the ApplicationDbContext class. You will have classes generated for each of your contexts (but these may reside in the root of the solution). In this example, find TestContext.cs class in the root and open it. You will note a similar line to the above for whatever model is associated to that context. Copy that line and paste it into the ApplicationDbContext class. For example, I have TestContext.cs with a model called TestModel. Once I paste it into the above Identity.cs class the class now looks like:

    public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
    {
        public ApplicationDbContext()
            : base("DefaultConnection")
        {
        }

        public System.Data.Entity.DbSet<Violet_82_UnderstandingEFDBAndModels.Models.EmployeeDetails> EmployeeDetails { get; set; }

        public System.Data.Entity.DbSet<Violet_82_UnderstandingEFDBAndModels.Models.EmployerDetails> EmployerDetails { get; set; }

        public System.Data.Entity.DbSet<Violet_82_UnderstandingEFDBAndModels.Models.TestModel> TestModels { get; set; }

    }

Once you have done this, you can delete the TestContext.cs class and repeate the process for each subsequent context.

[Violet_82]

OK cool. I had a look at both controllers, EmployerDetails and EmployeeDetails and both have the same context, as strange as this may sound:
they both have this

private EntityFrameworkTestContext db = new EntityFrameworkTestContext();

Still, as we know from the package manager console, there are two different contexts. So what I did was CTRL + F for the other missing context, UsersContext according to the package manager console and I've found it inside AccountController.cs:

 // Insert a new user into the database
                using (UsersContext db = new UsersContext())

But I don't believe this is what we're looking for aren't we? In any case, it seems easier to start the application again than hunting around for the offending context, so I may just do that

[Violet_82]

OK so redone the application according to your suggestions.
I created a class EmployeeDetails first, then the EF controller with a new context which the application named TestingEntityFramework.Models.TestingEntityFrameworkContext and all went well (compiled and generated the database).
Then I added the new class EmployerDetail.css (I missed an 's' in the name unfortunately but hey), created a new EF controller and this time I've been careful to re-use the same context TestingEntityFrameworkContext (TestingEntityFramework.Models)
but when I pressed OK I got an error, here is a scrennshot:

Any idea why?

[djjeavons]

You have the same issue that you had in a previous post. It is telling you that no Key is defined in the EmployerDetail class. Did you create a property called ID or EmployerDetailID? If not, add the [Key] attribute to the property in the model that defines the key.

[Violet_82]

It was a typo with the ID: I had the id, I just mistyped it. In any case, I got the controller created,a dn as per your tutorial I now have the error, so I need to enable migration. Alas, still get the warning message despite having used the same context for the two controllers:

PM> Enable-Migrations
More than one context type was found in the assembly 'TestingEntityFramework'.
To enable migrations for TestingEntityFramework.Models.UsersContext, use Enable-Migrations -ContextTypeName TestingEntityFramework.Models.UsersContext.
To enable migrations for TestingEntityFramework.Models.TestingEntityFrameworkContext, use Enable-Migrations -ContextTypeName TestingEntityFramework.Models.TestingEntityFrameworkContext.
PM

Again I CTRL'd F5 both: the first UsersContext which I've found inside the automatically generated AccountControlled

 if (ModelState.IsValid)
            {
                // Insert a new user into the database
                using (UsersContext db = new UsersContext())

and the second one TestingEntityFrameworkContext found in both EmployerDetailController and EmployeeDetailController.
Also, I looked into the model folder for the IdentityModel.cs but it isn't there, see screenshot:

[Violet_82]

I think the matter is, when I create the very first controller, am I supposed to create a new data context class or reuse something that's already there? You said in your example to

using the ApplicationDbContext data context

but I didn't find any with that name, that's why I created a new one and used the same newly created for the second controller. Perhaps this is what's throwing it? If my application is called, say, TestingEntityFramework should I be able to find a context called TestingEntityFrameworkDbContext?

[djjeavons]

That's odd as when I create an MVC application I only have two models (AccountViewModels.cs and IdentityModels.cs). IdentityModels.cs already defines ApplicationDbContext hence the reason that I was using this in my example.

What version of Visual Studio and MVC are you using and how are you creating the application?

[Violet_82]

I have VS pro 2012 (v 4.5.50709)
Also, let me show you what I have in my context list when I try to create a controller (sorry it should've occurred to me to do this earlier):

Ignore the TestingEntityFrameworkContext(TestingEntityFramework.Models) as this is the new one I've created. So this ApplicationDbContext is nowhere to be found I'm afraid. Perhaps the differences between mine and yours are due to the kind of project I create a ASP.NET MVC4 web application (that's what I usually do), then Internet Application.

[djjeavons]

Ah, I am using MVC 5 so there probably are some differences.

Why not upgrade if you are starting out, no point learning from the older version. You can install MVC 5 in VS 2012 from here.

[Violet_82]

I can't, company policy, it has to be done on a corporate basis, so it's not an option. But surely there's got to be a way to do this on MVC 4 and get rid of this silly "More than one context type was found in the assembly..."
More than happy to start another project but it seems like that if I do and I create a new context when I create the first controller and then re-use the same context for the following controller, I still get that message, unless you've got any idea as to what option from that context list I should select when I create a controller. NOt sure if you can see the previous screenshot with the options there

[djjeavons]

Ok, I have created an MVC 4 application and see the same list of data context items that you show in your previous post.

The following is my attempt to replicate your issue and resolution.

Here is what I did when creating the new project.

First, I created a new model for the EmployerDetails data. Then I built the project and then added a new controller using the option "MVC controller with read/write actions and views using Entity Framework" and named this EmployerDetailsController. Then for the Data context class I selected <new data context...> and set the name to ApplicationDb (Note: I only replaced the highlighted part of the full context string, so the full name is Violet_82_MVC4MultipleContexts.Models.ApplicationDbContext - yours will look different.)

Running this presented no errors and the database was created successfully.

Next, I added a new model for EmployeeDetails data, built the project then added another new controller called EmployeeDetailsController using the same options as the EmployerDetailsController. For the data context class, I used the existing ApplicationDbContext class that was created in the previous step.

Running this then gives me the error regarding code migration.

Going to the Package Manager Console and just entering Enable-Migrations gives me the same error as yourself regarding multiple contexts, one for UsersContext and one for ApplicationDbContext. So I then ran: Enable-Migrations -ContextTypeName Violet_82_MVC4MultipleContexts.Models.ApplicationDbContext to enable migrations on the ApplicationDbContext and was given the warning regarding the use of automatic migrations. So I deleted the Migrations folder and ran the following statement: Enable-Migrations -EnableAutomaticMigrations -ContextTypeName Violet_82_MVC4MultipleContexts.Models.ApplicationDbContext. This then created the Migrations folder with a Configuration.cs file.

Then, to update the database with the new model, I ran: Update-Database -ConfigurationTypeName Configuration. Note that I specify the configuration type to use. This worked and running the application presents no errors.

I then modified the EmployeeDetails model to add a new property, ran the project and got the code configuration error again. This time, however, the only command that I needed to run was Update-Database -ConfigurationTypeName Configuration. The database was updated successfully and the project ran fine.

So to paraphrase:

• Create the first model with controller and new data context. Everything works fine.
• Modify a model or create a new one and code migration errors occur (as expected).
• Run Enable-Migrations -ContextTypeName Violet_82_MVC4MultipleContexts.Models.ApplicationDbContext then delete the Migrations folder
• Run Enable-Migrations -EnableAutomaticMigrations -ContextTypeName Violet_82_MVC4MultipleContexts.Models.ApplicationDbContext
• Run Update-Database -ConfigurationTypeName Configuration

Now assuming that for each new model and controller you will use the ApplicationDbContext context class, then the only command you should need to run when modifying or adding new models etc. should be Update-Database -ConfigurationTypeName Configuration

[Violet_82]

OK tested on my side as well, it all works as the example.
Thank you very much for taking the time to test it and explain it to me, much appreciated, since I know that sometimes it must be frustrating to repeat things more than once, but I've understood the whole thing :-)!

[djjeavons]

Your welcome, I've learned a few things as well in assisting you so we're both winners :)