Building a Maintainable Web App with Web API 2 and Entity Framework 6, Part 1: Data Storage

In the first iteration of our sample web app, we're using EF Migrations. EF Migrations work well if a single developer is working on a trivial app, typically a proof-of-concept or a throwaway app. Why?

  • The database actually stores tables to track migrations. The database should not care about its own schema history, but rather should just care about the actual storage of application data. This is a violation of Separation of Concerns (SoC).
  • Our web app has classes that track migration history. Again, this is a violation of SoC; why should a production web application have db migration history in it?
  • EF, by default, stores application data to LocalDB. While this works fine for a learning exercise, you gain little to no benefit for actually publishing your database to production, which is somewhat important when writing a web app.
  • EF also has default SQL Server type mappings to your model properties, which may or may not be what you want them to be. For example, a .NET string type maps to a SQL Server type of NVARCHAR(MAX). Unless you need to store Unicode text of an indeterminate length, this is probably overkill.

If you care about your data integrity, you'll want to manage the schema yourself, which is what we're going to do. However, we'll be able to utilize what EF has already given us by default to get us going in the right direction.

The first thing we're going to do is add a SQL Server Database Project to our solution entitled BookService.SqlServer. Since we're going to want to deploy our web app and database to Azure, right-click the project in Solution Explorer, select Properties, then under Project Settings, change the Target Platform to Microsoft Azure SQL Database. Azure SQL is essentially a subset of installed SQL Server, so setting this property will help us ensure our schema matches what Azure SQL requires.

A really awesome feature of a SQL Server Database Project is the ability to import the schema from an existing database. So, we're going to do that and see if we need to make any tweaks. Right-click the project and select Import > Database. Server name will be (localdb)\MSSqlLocalDB (if you have the same development environment as me, else let Google be your guide), and select the database BookServiceContext-xyz (yep, even our db name has been controlled by Entity Framework). Keep the default options, then click Start.

After a few seconds, you'll see three tables under dbo > Tables:

  • _MigrationHistory
  • Authors
  • Books

We're going to first delete the _MigrationHistory, as that simply does not need to be there.

Next, double-click the Authors table. Id is fine as an int db type, but Name seems a bit excessive at nvarchar(max). So let's change that to varchar(100). If we need to eventually support Unicode characters or longer author names, we can update the column accordingly. Be sure to save your changes.

Now open the Books table. Update the schema to the following:

  • Id: int
  • Title: varchar(500)
  • Year: int
  • Price: decimal(7, 2) (we don't expect to carry any books over $100k at this point)
  • Genre: varchar(100)
  • AuthorId: int

Now we need an easy way to publish our database. For now, all we care about is publishing to our local SQL Express instance, but the steps are the same for remote instances as well. Right-click the project, select Publish, enter server name .\SQLExpress, database name BookService, then we'll save the publish profile so we can quickly access it later, then click Publish. You'll now have the database set up on your local SQL Express instance instead of LocalDB.

But what about seed data? EF does give us a simple option for seeding our application with initial data, but again, why should the web app be responsible for tracking this? With a SQL Server Database Project, you can run both pre- and post-deployment scripts to manipulate data. Refer to the repo on GitHub to see how this is accomplished.

At this point, our SQL Server Database Project is good to go, however we still have to deal with the web app migrations files. Fortunately, the solution here is rather simple: just delete the Migrations folder and all of its underlying files.

Next, we want to ensure our models classes enforce the schema as much as we can. While more advanced options are available, typically you'll use data annotations to:

  • Ensure something is required
  • Enforce max/min values
  • Enforce max string lengths
  • Handle foreign key relationships if the property name breaks EF convention (we don't have to worry about this one right now).

Lastly, by convention, EF maps DbSet names to table names. This means that the Authors DbSet will map to an Authors table. I personally prefer singular table names so I can type things like Author.Name instead of Authors.Name, which just feels weird. So, we'll override this convention in the BookServiceContext class by overriding the method OnModelCreating(DbModelBuilder modelBuilder) and using the code first fluent API to tell EF the table mappings we want.

After a bit of testing, I've confirmed that our changes are good to go. We'll submit a PR to merge our changes into master (and approve it since I'm doing it all myself :)), and the branch will live on at https://github.com/IndifferentDisdain/BookService/tree/data-storage.

Next in this series, we'll refactor our API controllers to not depend directly on our EF context, thus increasing the testability and maintainability of our application.