Performing Entity Framework Code First Migrations step-by-step

If you need to perform Entity Framework Code First Migrations, you will probably end up following this article: https://msdn.microsoft.com/en-us/data/jj591621(v=vs.113).aspx

However, as with most Microsoft articles, the documentation leaves a lot to be desired...

Therefore, I will provide a more thorough step-by-step walk through on just how to do this!

1. After opening up the Package Manager Console, you will want to make sure you choose the "Default project" before running the command to "Enable-Migrations"





2. This will then create a Configuration.cs file shown as follows:








3. Next, when you run "Add-Migration <MyMigrationName>", then you will get the following message along with the migration file added to your project:








4. You can then run the "Update-Database" command if you want to run the command directly against the database. However, if you are like most developers and want to know what the database script looks like before it is applied to the database, you can instead run the "Update-Database -Script" command which will generate a SQL script instead which you can then save off for reference!





5. For every change each developer makes to the Entity Framework Code First models, you simply repeat the "Add-Migration" and "Update-Database" steps respectively (corresponding to the Code First model changes that were made) and you can ignore the "Enable-Migrations" command.  That is all there really is to it! :-) 

Handling multiple Foreign Keys to the same database table in Entity Framework Code First

I recently encountered a requirement while working with Entity Framework Code First that required mapping more than 2 foreign keys to the same database table.

The convention for Entity Framework Code First, by default, does not make this easy simply by defining [ForeignKey] Data Annotations.

Fortunately, a quick search turned up this Stack Overflow thread: http://stackoverflow.com/questions/5559043/entity-framework-code-first-two-foreign-keys-from-same-table

This thread ultimately resulted in 2 possible solutions to this problem.

The first solution required the usage of the FluentAPI for Entity Framework Code First by relying on the OnModelCreating event in the DbContext class:



The second solution required the use of the [InverseProperty] Data Annotation instead:

Using LINQPad to test and query with Entity Framework

If you want to easily test out your Entity Framework assemblies without writing a separate Console application or creating a separate Visual Studio project, the best way to do this is to use LINQPad!

I have been using LINQPad for several years for all of my C# development needs including Entity Framework, but I have never shared a Blog post about how to do this in the past.

If you go to the LINQPad site, you can find a brief article on how to work with Entity Framework here: http://www.linqpad.net/EntityFramework.aspx

However, this article is not quite comprehensive and leaves out a number of pieces, therefore, I thought I would share a step-by-step article for those working with Entity Framework.

1. Open up LINQPad and in the left hand navigation, you will see an option to "Add connection"
2. You will then be presented with a dialog to select your Entity Framework connection.  For most developers using Entity Framework v. 4.1 or above, you will choose the Entity Framework (DbContext) option.
3. Once you have chosen your desired Entity Framework connection, you will be presented with another dialog which allows you to choose your Entity Framework assembly
4. Once you choose your Entity Framework assembly, you will be required to choose the corresponding DbContext for your Entity Framework assembly
5. Next, you will be required to specify a connection string for your Entity Framework assembly. For most developers, you will choose the option for "Via the parameterless constructor" which will subsequently require you to select the database connection string from an App.config file
6. Then, you will have to choose the path to the App.config file (which will be usually named something like MyAssembly.dll.config)
7. Lastly, you will need to choose a name for your Entity Framework connection.
8. Once all necessary information has been entered, you can "Test" your connection.
9. If the test is successful, you can click on "OK" for the Test dialog and then click on "OK" again so that your Entity Framework connection is added to LINQPad!
10. Now, to use this connection in your Entity Framework queries, you can click on the newly created connection hyperlink for "Use <my>connection" or you can select your connection from the "Connection" dropdownlist.  
11. After this is complete, you are all set to begin writing queries against your Entity Framework assembly in the LINQPad Query window!  Woo hoo!

Entity Framework Database First vs Entity Framework Code First connection strings

If you look on http://connectionstrings.com, unfortunately, you will not find sample connection strings for Entity Framework (either Database First or Code First).

Most developers that initially started working with Entity Framework, probably have been using Entity Framework Database First and therefore are familiar with the common lengthy connection string as follows:



However, the Entity Framework Code First connection strings follow the more standard ADO.NET model for database connection strings such as the following:


You can read more about the differences between the 2 Entity Framework models here: https://msdn.microsoft.com/en-us/data/jj556606%28v=vs.113%29.aspx?f=255&MSPPError=-2147217396

Mocking Entity Framework Database Transactions

If you are writing Unit Tests for any of your classes that leverage Entity Framework, you should be able to Mock most operations leveraged by Entity Framework.

However, Entity Framework 6 introduces features for wrapping your operation in a BeginTransaction() statement as described here: https://msdn.microsoft.com/en-us/data/dn456843.aspx

The problem with this scenario, of course, is that this code cannot easily be Mocked!

Well, what do you do in these scenarios?

Well, first of all, I would recommend looking at the use case scenarios to determine if you really need this functionality since Microsoft describes this in the article:


In all versions of Entity Framework, whenever you execute SaveChanges() to insert, update or delete on the database the framework will wrap that operation in a transaction. This transaction lasts only long enough to execute the operation and then completes. When you execute another such operation a new transaction is started.

Therefore, transactions are being used as required by Entity Framework for insert, update and delete operations!!

If you read articles such as this which does some additional examination on the repercussions of using Entity Framework Database Transactions (https://coderwall.com/p/jnniww/why-you-shouldn-t-use-entity-framework-with-transactions), you will see that the recommendation is to NOT USE Entity Framework Database Transactions (especially with Web Applications).

Therefore, unless the business logic ABSOLUTELY requires the use of Entity Framework Database Transactions in this manner, you can probably safely remove this code and thereby save yourself a great deal of hassle while writing your Unit Tests!!

Mocking Entity Framework for your Unit Tests using NBuilder

If you need to Mock Entity Framework in your Unit Tests you may end up referring to this article: https://msdn.microsoft.com/en-us/library/dn314429.aspx

In the article, they require you to create a test collection of your DbSet objects which are subsequently passed to your DbContext instance.

In addition, if you have multiple DbSet objects which are involved in a particular query, then this increases your workload even further!

However, you can use a framework such as NBuilder which is available as a NuGet package in order to simplify some of this code for you:


Alternatively, you can use some NuGet packages which takes away some of the extra setup for Mocking Entity Framework such as the following:

https://github.com/RichardSilveira/EntityFramework.MoqHelper

https://github.com/scott-xu/EntityFramework.Testing

Could not find a parameterless constructor when using Moq

If you need to Mock objects which expect parameters in the constructor, you may encounter a message such as the following:

"Can not instantiate proxy of class: DataAccess.DataModel.MyDbContext. Could not find a parameterless constructor."

Based on the error message, the solution requires you to pass a constructor to the parameter of your Mocked object.

So how exactly do you do that?

Well, the solution is, in fact, rather easy!

You just pass the required parameter to your Mocked object like so:


In this case, I am passing the database connectionString as a parameter to my Mocked DbContext object so that it can instantiate the object appropriately. That is all there is to it!

Implementing the Repository Pattern in C# with Entity Framework

If you are looking to implement the Repository Pattern in C#, you may find lots and lots and LOTS of conflicting solutions about how to implement the Repository Pattern in your own application.

If you just do a quick search just on MSDN, you will find articles such as the following:

https://blogs.msdn.microsoft.com/wriju/2013/08/23/using-repository-pattern-in-entity-framework/

http://www.asp.net/mvc/overview/older-versions/getting-started-with-ef-5-using-mvc-4/implementing-the-repository-and-unit-of-work-patterns-in-an-asp-net-mvc-application

If you look through the examples above, you will see that they offer different solutions to the same Repository Pattern problem.  However, the second example provides the ability to create a Generic Repository which is much more appealing than creating a different repository for each class in your entire data model!

Unfortunately, the MSDN example lacks a definition of a generic interface which GenericRepository implements.  Looking at the code, you can derive your own IRepository interface, but the example still lacks support for a Dependency Injection or IoC container.  As you will readily notice, the example directly requires an implementation of SchoolContext to be created.  The way in which the example gets around direct creation of the SchoolContext is by using a Unit of Work class which handles the creation of this instance.  While the Unit of Work class can be useful in aggregating multiple repositories, it may not make much sense for instances when you simply need a single repository, which is why an IoC container is very useful!

So how exactly do you get Ninject to work in this scenario?

For Ninject, you simply use the following code to inject your SchoolContext instance:


You can simply add the following code to your RegisterServices method inside of your NinjectWebCommon.cs file and this should do the trick for you!

Why Entity Framework POCO class properties should be marked "virtual"

I had always wondered why Entity Framework would generate all of my POCO classes decorated with the virtual keyword for all of the class attributes, when many code samples using Entity Framework never used the virtual keyword at all!

Well, I soon found the answer in this MSDN article: https://msdn.microsoft.com/en-us/data/dn314429

As it turns out, marking the attributes as virtual is a deliberate decision on the part of Microsoft in order to better support Unit Testing and Mocking!

Therefore, if you want to support Unit Testing and Mocking in your own Entity Framework implementations (and I am guessing that you do), then you will want to mark these attributes virtual as well!

Generating Entity Framework Code First POCO classes from an existing Entity Framework Model (EDMX file)

If you have an existing Entity Framework model (EDMX file) you generated from your database and you would like to generate POCO classes for using as Domain Classes or for Entity Framework Code First, you can easily accomplish this using Code Generation!

1. Right click on your Entity Framework model and select "Add Code Generation Item"
2. You will then be prompted to select a Visual Studio Template.  Based on the version of Entity Framework that you are using, you can choose either "EF 5.x DbContext Generator" or "EF 6.x DbContext Generator".  For my particular example, I am using Entity Framework 6, so I ended up using "EF 6.x DbContext Generator"
3. Finally, once you choose your Entity Framework Code Generation template, you will then be prompted to run the template.  
4. When prompted with the warning dialog about running templates on your computer, make sure you click "OK" in order to allow the template to run.  You may be prompted multiple times, so you may have to either click "OK" multiple times or select the checkbox for "Do not show this message again"
5. Once you have completed running the code generation templates, you will have your generated code classes!

Generating Entity Framework Code First POCO classes

Normally, when you think of Entity Framework Code First, you think that you have to generate all of your POCO classes completely by hand!

Fortunately, though, a clever developer has created the Entity Framework Reverse POCO Generator! https://visualstudiogallery.msdn.microsoft.com/ee4fcff9-0c4c-4179-afd9-7a2fb90f5838

If you are using Visual Studio 2013, you will also need to install the Entity Framework 6 Tools for Visual Studio 2013 from here: https://www.microsoft.com/en-us/download/details.aspx?id=40762

This Visual Studio extension allows you to generate Entity Framework Code First POCO classes directly from your database!

Unfortunately, all of the POCO classes are generated into a single class file, but you can then use a tool such as Resharper to extract the classes into separate C# files and then customize the generated POCO classes to ensure they match the design requirements of your system!

How neat is that??

Re-generating an ADO.NET Entity Data Model from the database

I recently had to change my underlying database schema and therefore needed to update my Entity Framework Data Model.

Unfortunately, just updating the data model directly caused problems with column mappings, so I ended up having to regenerate the ADO.NET Entity Data Model.

Going through the Visual Studio Entity Data Model wizard went through just fine, however, because of the structure of the project/solution, I quickly discovered that I could not name my entity class the same name as before!

Instead, my class name was showing up as "Entities" instead of my original class name!

Well, thankfully, changing the name of the class was relatively easy by simply modifying the <EFModel>.Context.cs class file.

public partial class Entities: DbContext
{
        public Entities()
            : base("name=Entities")
        {
        }
}

I then simply changed this to match my Entities class name as follows:

public partial class MyCustomEntities: DbContext
{
        public MyCustomEntities()
            : base("name=MyCustomEntities")
        {
        }
}

Now I could work with my Entity Framework classes just as before!!

Entity Framework-Unable to load the specified metadata resource

I was working on updating my application with a newer version of Entity Framework when I suddenly encountered the following error message:

Based on a few discussion forum posts, there was a mention that the name of the .csdl, .ssdl and .msl files may have been incorrect/invalid in the Entity Framework connection string.

Well, based on looking at my connection string, I noticed that was indeed the case!

When I re-generated my Entity Framework Designer Model, I ended up changing the name of the underlying EDMX model file which consequently changed all of the names that are also required in the Entity Framework connection string.

Therefore, I had something like the following:

<add name="MyEntities" connectionString="metadata=res://*/OldModel.csdl|res://*/OldModel.ssdl|res://*/OldModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(local);initial catalog=MyCatalog;user id=sa;password=P@ssword!;multipleactiveresultsets=True;application name=EntityFramework&quot;" providerName="System.Data.EntityClient" />

Since the EDMX model names were invalid, I had to change it to the following:

<add name="MyEntities" connectionString="metadata=res://*/NewModel.csdl|res://*/NewModel.ssdl|res://*/NewModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(local);initial catalog=MyCatalog;user id=sa;password=P@ssword!;multipleactiveresultsets=True;application name=EntityFramework&quot;" providerName="System.Data.EntityClient" />

That was all that was needed to resolve my issue!

Using the Attach method in Entity Framework

If you are working with Entity Framework, you might encounter situations where you end up working with older versions of Entity Framework that still use ObjectContext whereas newer versions of Entity Framework use DbContext instead.

In any case, if you end up working with some old code or code samples that use ObjectContext, you may end up seeing references to using the Attach method.

However, one of the problems with using the Attach method is that it will not do anything if the object state is not tracked as modified!

Therefore, even when you call SaveChanges, your changes may not be persisted to the database!

Fortunately, there is a solution to resolving this problem by setting the EntityState to Modified.

This is outlined in this article: https://msdn.microsoft.com/en-us/data/jj592676.aspx

So your code will probably end up looking like this instead:

context.Attach(existingBlog);
context.Entry(existingBlog).State = EntityState.Modified; 
context.SaveChanges();

You can read up more about Attaching and Detaching objects using ObjectContext on MSDN here: https://msdn.microsoft.com/library/bb896271(v=vs.100).aspx

Database Integration Tests with Entity Framework

If you are using Entity Framework and want to use Database Integration tests as part of your test suite, then you will want to definitely check out this article: http://tech.trailmax.info/2014/03/how-we-do-database-integration-tests-with-entity-framework-migrations/

It provides insight on how to seed data into your database and then ensure that the data is subsequently rolled back out of the database!

Unit Testing and Mocking with Entity Framework

One of the major problems with Unit Testing .NET applications in the past has been the inability to test data access layers that leverage Entity Framework.

Fortunately, though, Microsoft has now provided an article which describes how to do just that!

Mocking Entity Framework when Unit Testing ASP.NET Web API 2
http://www.asp.net/web-api/overview/testing-and-debugging/mocking-entity-framework-when-unit-testing-aspnet-web-api-2

This article describes how to implement an interface for Entity Framework that will then not only allow you to use Unit Testing and Mocking for your Entity Framework classes, but also leverage Dependency Injection!

All of this functionality comes with using the newer DbContext functionality that was made available since Entity Framework v. 4.1!

More than one migrations configuration type was found in the assembly 'MyData'. Specify the name of the one to use.

I was recently running an Add-Migration command using Entity Framework Code First Migrations, when I encountered the following error message:

"More than one migrations configuration type was found in the assembly 'MyData'. Specify the name of the one to use."

As I soon discovered, I was using multiple Configuration.cs classes (to correspond to my multiple DbContext instances) in my assembly, thus causing the Add-Migration command to fail with this error message.

Therefore, I had to modify the command to be the following instead:

Add-Migration "MyMigration" -ProjectName "MyProject" -StartUpProjectName "MyProject" -ConnectionStringName "MyConnString" -ConfigurationTypeName "MyMigration.Configuration" -Force –Verbose

The target context 'MyDbContext' is not constructible. Add a default constructor or provide an implementation of IDbContextFactory.

I was recently using Code First Migrations with Entity Framework and I was using the Enable-Migrations command when I suddenly encountered this error message:

The target context 'MyDbContext' is not constructible. Add a default constructor or provide an implementation of IDbContextFactory.

As it turned out, I was missing a default constructor in my DbContext implementation that was preventing the running of this command.

As soon as I added this back in, I was able to run my migration command!

Upgrading from Entity Framework 5 to Entity Framework 6

I have some applications that I have been developing for a few years that have been using Entity Framework 5 so I decided to finally refresh them and update/upgrade them to using Entity Framework 6.

Well, fortunately, the upgrade was relatively painless.

Whereas in Entity Framework v. 5.0, I had the following namespaces:

using System.Data.EntityClient;
using System.Data.Objects;

I simply had to change these to the following for Entity Framework v. 6.0:

using System.Data.Entity.Core.EntityClient;
using System.Data.Entity.Core.Objects;

That was pretty much all there was to it!!

Data binding to an ASP.NET GridView control using LINQ and Entity Framework DbContext

I have been using Entity Framework for quite a few years and when I first started using Entity Framework, the only option was using traditional ObjectContext.

However, with the more recent releases of Entity Framework, the recommendation is to now use DbContext in favor of ObjectContext. 

Therefore, I began updating all of my Entity Framework Class Libraries over to the new DbContext, but after compiling my solution and attempting to use a LINQ Query to bind to an ASP.NET GridView, I received the following error message:

Data binding directly to a store query (DbSet, DbQuery, DbSqlQuery) is not supported. Instead populate a DbSet with data, for example by calling Load on the DbSet, and then bind to local data. For WPF bind to DbSet.Local. For WinForms bind to DbSet.Local.ToBindingList().

After doing some research on the web as to the nature of this error message, I discovered, that I simply had to cast my LINQ Query to a List (by appending .ToList() to the end of the LINQ Query result).

Once I did that, I was able to successfully bind my LINQ Query to the GridView control without receiving any further error messages!!