The source code of this project can be accessed from Github.
1. Introduction
The main idea of this blog is you are going to write a web application with Entity Framework, and not only that, you want the project to come with a data migration plan. When you wrote an application, the database may evolve over time, and you want gracefully and gradually migrate the database in a live production environment. This is where EF data migration comes into play. This article will talk through this in a number of steps. All other coding aspects, e.g configuration, coding standard, validation, etc, and even entity framework modeling are skipped in order to bring clarity to the core issue, data migration.
2. Prerequisite
Microsoft allows creating a project from either a visual studio IDE template or using dotnet command line (CLI) interface. In this article, we will create a project from dotnet CLI. Additionally, we will use Visual Studio Code instead of using Visual Studio IDE, however the usage will be limited to edit files and navigate project folders. All building and running application will be done via dotnet CLI commands. Here is a number of prerequisite:
- Download and install Visual Studio Code from here
- Run Visual Studio code, and install C# extension from OmniSharp. To install the extension click icon on the left panel, or click menu View --> Extension
- Download and install dotnet 5 SDSK from here , you can download any SDK version v5.0.0, or above. If you are in a windows OS, make sure the installed directory is included in your computer system PATH.
- Make sure you have a local instance of MS SQL server with Windows Authentication enabled
2. Creating solution and project skeleton using dotnet CLI
We will create a solution containing two projects, the main project will be generated from an empty web template, and the other project is a library project which contains our database model, context, and later on our migration files. Here we will do it using dotnet CLI commands via command prompts, or you can open a termianl from Visual Studio code
- Create a directory for our solution 'EFCoreMigration', and move to that directory
mkdir EFCoreMigrationcd EFCoreMigration
- Run dotnet command to create a new solution file
dotnet new sln
this will create a solution file with the name of the current directory, or you can specify the name of the solution file with argument -n,
dotnet new sln -n EFCoreMigration
- Create two projects, one for WebApp which of type web, another for DataAccess, which of type classlib
dotnet new web -o WebAppdotnet new classlib -o DataAccess
To see every available project template, you can list them using
dotnet new -l
- Add those two projects to the solution
dotnet sln add WebAppdotnet sln add DataAccess
Note, if you don't specify the solution name, it will use the default which is the name of the current directory. A complete command can be like
dotnet sln [solution name] add WebApp
Or you can also specify the full path of the project file like
dotnet sln [solution name] add WebApp\WebApp.csproj
- Add a reference to DataAccess project in the WebApp project
dotnet add WebApp reference DataAccess
Or you can also specify the full path of the project file
dotnet add WebApp\WebApp.csproj reference DataAccess\DataAccess.csproj
3. Create model and data context in DataAccess
Here we use a code-first approach of the entity framework. Very basic model is used.
- In the Visual Studio Code, open DataAccess directory, and rename Class1.cs To Book.cs, and type this code below:
using System;namespace DataAccess{public class Book{public int Id { get; set; }public string Name { get; set; }}}
- In the terminal, go to DataAccess directory, and add Microsoft.EntityFrameworkCore package (version 5.0.3)
cd DataAccessdotnet new package Microsoft.EntityFrameworkCore -v 5.0.3
- In the Visual Studio Code, create DataContext.cs in the DataAccess project.
using System;using Microsoft.EntityFrameworkCore;namespace DataAccess{public class DataContext: DbContext{public DbSet<Book> Books { get; set; }public DataContext(DbContextOptions options) : base(options){}protected override void OnModelCreating(ModelBuilder modelBuilder){//Can add constraint, index, check, data type, and even data seed}}}
- Make sure you save all changes and compile DataAccess project to make sure it builds by typing in the terminal
dotnet build
4. Initiate database in the WebApp
- In the terminal go to WebApp directory, and add Microsoft.EntityFrameworkCore.SqlServer (version 5.0.3). This step depends on what database server you use, if you use other than the MS SQL server, find an appropriate provider package for your database server
dotnet new package Microsoft.EntityFrameworkCore.Sqlserver -v 5.0.3
- In the Visual Studio Code, open WebApp directory, and edit Startup.cs. Add DB context in the ConfigureServices section.
using Microsoft.EntityFrameworkCore;.......public void ConfigureServices(IServiceCollection services){services.AddDbContext<DataAccess.DataContext>(options =>options.UseSqlServer("Server=localhost;Database=EFCoreMigration;Trusted_Connection=True;"));}
UseSqlServer is an extension method in the Microsoft.EntityFrameworkCore.SqlServer, and to use it you need to add using Microsoft.EntityFrameworkCore at the top. The code will connect to local instance of MS SQL server using the logged user account, and creating a EFCoreMigration database.
- In addition to that, adds a DataContext to Configure method in the Startup.cs, and making sure the database is created before executing database related middleware, by putting Database.EnsureCreated() before them.
public void Configure(IApplicationBuilder app,IWebHostEnvironment env,DataAccess.DataContext dataContext){dataContext.Database.EnsureCreated();
- Make sure you save all changes. In the terminal, go to WebApp directory and type
dotnet run
- Check that the application is working, by running the browser with port the service is listening to (e.g in my machine is http://localhost:5000 and https://localhost:5001). To cancel the service Click + c.
- Check the database is instantiated by checking that EFCoreMigration database is present in the local instance of the MS SQL server by using e.g. Microsoft Server Management Studio.
- What I have not shown is how to use DataContext. Normally to use DataContext, you just need to add the DataContext in the controller constructor and assigning it to a variable in the controller. After that, you can use it anywhere in the controller. As this project just a barebone web application, and there is no WebAPI, MVC, Razor or Blazor middleware, and endpoints are configured, you can get the DataContext registered in the services by invoking HttpContext object that is passed in the middleware. Below is the example, see context.RequestServices.GetService<DataAccess.DataContract>(). You can re-run the application, and check the browser, which now displays the book count. Note, you cannot use the DataContext passed in the Configure method as this object is passed in the phase of middleware building time and has been long disposed of.
public void Configure(IApplicationBuilder app,IWebHostEnvironment env,DataAccess.DataContext dataContext){dataContext.Database.EnsureCreated();if (env.IsDevelopment()){app.UseDeveloperExceptionPage();}app.UseRouting();app.UseEndpoints(endpoints =>{endpoints.MapGet("/", async context =>{using var dbc = context.RequestServices.GetService<DataAccess.DataContext>();await context.Response.WriteAsync($"Book Count : {dbc.Books.Count()}");});});}
5. Add data migration feature
Up to now, we have used the Entity Framework in our code, but without the data migration feature. In most projects, the database may evolve over time, so we may need to come up with a strategy to change the database over time. Entity Framework Core has a data migration feature, and below is one way to do it.
- First, we need to install the EF toolchain into dotnet command. Type in the terminal:
dotnet tool install --global dotnet-ef --version 5.0.3
This will allow us to run dotnet ef command.
- Add Microsoft.EntityFrameworkCore.Design to the DataAccess project. In the terminal, go to DataAccess directory, and type
dotnet add Microsoft.EntityFrameworkCore.Design -v 5.0.3
- In Visual Studio Code, go to DataAccess directory, and create DataContextFactory.cs
using System;using Microsoft.EntityFrameworkCore.Design;using Microsoft.EntityFrameworkCore;namespace DataAccess{public class DataContextFactory : IDesignTimeDbContextFactory<DataContext>{public DataContext CreateDbContext(string[] args){var builder = new DbContextOptionsBuilder<DataContext>();//this code will be never executed in runtime only in design timebuilder.UseSqlServer("Server=localhost;Database=EFCoreMigration;Trusted_Connection=True;");return new DataContext(builder.Options);}}}
- In the terminal, go to DataAccess directory, type:
dotnet ef migrations add InitialSchema
It will create Migrations directory with three files:
- [yyyyMMddHHmmss]_InitialSchema.cs
- [yyyyMMddHHmmss]_InitialSchema.Design.cs
- DataContextModelSnapshot.cs
The InitialSchema has two method Up and Down. Up is executed when the migration is applied to the database, the Down is executed when the migration is removed from the database. The DataContextModelSnapshot contains the snapshot of the database model if migration is applied to the latest migration. So DataContextModelSnapshot.cs will be overwritten if a new migration is added or removed.
- The migration feature has the ability to apply the database changes on the fly by setting it up in the Startup,cs. In the Visual Studio Code, open WebApp directory, and edit Startup.cs. Change dataContext.Database.EnsureCreate() to dataContext.Database.Migrate().
- Delete the database EFCoreMigration previously created. Make sure you save all changes, and then go to the terminal and run the web application.
dotnet run
- After you successfully run the application check the database, EFCoreMigration. The database will have a table called dbo.__EFMigrationsHistory, with columns: MigrationId, and ProductVersion. ProductVersion column is used internally by the Entity Framework and reflects the EntityFrameworkCore version, and MigrationId value is in the format of [yyyyMMddHHmmss]_[Migration name].
6. Make changes to the database model
After you have enabled the data migration, the next question is what to do when you have to make changes the database model. Of course, there is a spectrum of database model changes and not all of them have the same complexity. However, for a common scenario of change, the following procedure should be sufficient.
- Changes can be made to the data model, be careful not to make breaking changes
- Changes can be made to OnModelCreating method in DataContext.
- In the terminal, go to DataAccess directory, type
dotnet ef migrations add [Your Own migration name]
- deploy as you normally do, the Migrate method in the Startup.cs will automatically apply the migrations to the latest changes.
- If you want to roll back the last migration, type:
dotnet ef migrations remove
and after that deploy as you normally do.
Note: if you have experience using the EF (Core or earlier version) data migration feature in live deployment, please feel free to comment and contribute, and comments on your experiences (pro and cons).
No comments:
Post a Comment