Introduction to EntityFramework 6 Part 5: updating schemas

Introduction

In the previous part of this series we looked at asynchronous DB calls and logging in EF 6. In this finishing post we’ll take a look at schemas.

If you create a database through code-first in EntityFramework then the new tables will be automatically put into the dbo schema: dbo.Customers, dbo.Orders etc. That is the case with our demo application where all tables, i.e. Cars and the ones related to identity belong to the dbo schema.

However, there are times that you’d like group your tables into different schemas depending on their purpose, domain or some other criteria.

Migrating from one schema to another in EF code-first is quite easy. We’ll apply it to our demo Cars application so have it ready in Visual Studio.

Demo

The goal is to move the domain related tables to the ‘domain’ schema and the identity related tables to the ‘identity’ schema. The solution is that we need to override the OnModelCreating method in the Db context class(es). Open CarsDbContext and add the following method just above the Cars property:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
       modelBuilder.HasDefaultSchema("domain");
       base.OnModelCreating(modelBuilder);
}

Similarly, add the following method to UserManagementDbContext:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
       modelBuilder.HasDefaultSchema("identity");
       base.OnModelCreating(modelBuilder);
}

Save and build the solution so that the changes are properly registered.

Our domain and identity models have just got extended so the underlying database and the model are out of sync. That means we’ll need to perform a migration like in part 2 of this series. Open the Package Manager Console – View, Other Windows, Package Manager Console – and run the following command:

Add-Migration -ConfigurationTypeName Cars.Web.Database.DomainMigrations.Configuration “SettingNewSchema”

This creates a new migration file called somedatestamp_SettingNewSchema.cs with the following contents:

public partial class SettingNewSchema : DbMigration
{
        public override void Up()
        {
            MoveTable(name: "dbo.Cars", newSchema: "domain");
        }
        
        public override void Down()
        {
            MoveTable(name: "domain.Cars", newSchema: "dbo");
        }
}

So we’re simply moving the table(s) from the dbo schema to domain.

Let’s do the same for out identity tables. Run the following command in the package manager:

Add-Migration -ConfigurationTypeName Cars.Web.Database.UserManagementMigrations.Configuration “SettingNewSchema”

You’ll get a file similar to the one above but with a MoveTable statement for each identity table. Next we need to run the SettingNewSchema scripts. Run the following commands in the package manager:

Update-Database -ConfigurationTypeName Cars.Web.Database.UserManagementMigrations.Configuration
Update-Database -ConfigurationTypeName Cars.Web.Database.DomainMigrations.Configuration

Open the DB table structure in Server Explorer and you’ll see the new schema names:

Updated schemas through update database entity framework

Run the application and test viewing, adding and updating the car objects. It should all work like it did before.

This post finishes the introductory series on EF 6. You can view all posts related to data storage on this blog here.

Introduction to EntityFramework 6 Part 4: asynchronous operations and logging

Introduction

In the previous post we looked at CRUD operations in EntityFramework 6. In this post we’ll look at the async coding features of EF.

Asynchronous code execution in .NET has been greatly enhanced by the await-async keywords. If you are not sure what they are about you can start here. They can help keeping the available threads busy thereby utilising the CPU resources better. You have probably seen a lot of methods built-into .NET4.5+ whose names end with “Async”, e.g HttpClient.SendAsync or StreamWriter.WriteAsync.

These awaitable asynchronous methods all return a Task or a Task of T. EntityFramework is no exception to the new trend of making the code more responsive and scalable. The async version of some well-known operators such as ToList() are usually available on methods that return something immediately, i.e. the non-deferred LINQ operators.

Demo

Open the Cars demo application we’ve been working on so far in this series. We have the following Index action in CarController.cs at present:

public ActionResult Index()
{
      return View(db.Cars.ToList());
}

As you type “db.Cars.” in the editor you’ll see that there’s a ToListAsync method. You cannot just use that method like…

public ActionResult Index()
{
      return View(db.Cars.ToListAsync());
}

…since it returns a Task of List of Cars and the view is expecting an IEnumerable of Cars. To make the Index method asynchronous we need to transform it as follows:

public async Task<ActionResult> Index()
{
      return View(await db.Cars.ToListAsync());
}

…where Task resides in the System.Threading.Tasks namespace. If you run the application now you should get the list of cars from the database as before. However, the following is happening behind the scenes:

  • Some thread enters the Index action
  • The thread sees the await keyword and lets another thread take over the processing of the awaitable method, i.e. the collection of cars from the database
  • When the awaitable method is finished by another thread then either the original thread or a new thread finishes the Index method

Check out the above link for a more detailed treatment of the async-await keywords.

Similarly the Details method can be rewritten as follows:

public async Task<ActionResult> Details(int? id)
{
          if (id == null)
          {
              return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
          }
          Car car = await db.Cars.FindAsync(id);
          if (car == null)
          {
              return HttpNotFound();
          }
          return View(car);
}

Note the FindAsync method which as the asynchronous version of Find in EF and returns a Task of Car.

The POST Create method can be made asynchronous by calling the async version of SaveChanges, i.e. SaveChangesAsync:

[HttpPost]
[ValidateAntiForgeryToken]
public async Task<ActionResult> Create([Bind(Include="Id,Make,Category")] Car car)
{
          if (ModelState.IsValid)
          {
              db.Cars.Add(car);
              await db.SaveChangesAsync();
              return RedirectToAction("Index");
          }

          return View(car);
}

In case you’d like to generate asynchronous action methods when inserting a new controller then you can check the following option:

Create async action methods in scaffolding

The templating engine will generate code similar to how our existing code was transformed above.

Logging

The DbContext object has a property called Database which in turn has a property called Log. The Log property is of type Action of String, i.e. you can assign a void method to it that accepts a string. The string parameter includes the actual SQL statements sent to the database.

In CarsDbContext.cs we have an empty constructor at present:

public CarsDbContext() : base("DefaultConnection")
{
            
}

Add the following code to the body of the constructor:

Database.Log = statements => Debug.WriteLine(statements);

Run the application and navigate to /cars. You should see a couple of SQL statements in the Output window related to the retrieval of Cars from the data store:

EntityFramework logging to output window

The first time a query is run you’ll see one or more statements related to data migration, but they won’t be run on subsequent queries.

You can of course assign a more complicated logging delegate to the Log property: log to the database, a file, GrayLog etc.

In the next post, which will end this introductory series on EF, we’ll look at how to update schemas.

You can view all posts related to data storage on this blog here.

Introduction to EntityFramework 6 Part 3: CRUD operations

Introduction

In the previous post we’ve got as far as generating our database and the Cars and user management tables. It’s time to add, delete and edit Car objects using the built-in capabilities of MVC 5.

Open the project we’ve been working on so far and let’s get to it!

Demo

We’ll create our Cars controller first. Right-click the Controllers folder of the Web layer, select Add, Controller… The Add Scaffold window will open. Select the following option:

MVC 5 Controller with views, using Entity Framework

Click Add and fill in the Add Controller dialog as follows:

Create scaffolding for Cars with Entity Framework

We want to base our controller and views on the Car object. The correct DB context to use is the CarsDbContext we created previously. Leave all other options untouched so that even the views will be created. The scaffolding engine will produce some boilerplate code as a starting point. It creates a lot of stuff for us actually: all the controller actions and views necessary to add, edit and delete Car objects. Check the Views folder. There’s a new folder in there called Cars with 5 cshtml files following the default view names: create, delete etc.

In reality you’ll almost always modify the controller code such as putting the following instantiation in a Repository and letting the controller reference it through an abstraction:

private CarsDbContext db = new CarsDbContext();

If you don’t know what’s wrong with putting this piece of code directly in a controller then check out the series on SOLID and loosely coupled layered design with DDD. However, this series is – almost – purely technical so it’s up to you to organise your code as you wish.

Now that we have the user management tables in place we can take advantage of them. We want to restrict access to the Car views to authorised users. Add the Authorize attribute to the controller:

[Authorize]
public class CarsController : Controller

If you’d like to know more about authentication and access control in MVC 5 checkout the series on Identity here.

For easy access open _Layout.cshtml in the Views/Shared folder and locate the following code which generates the code:

<ul class="nav navbar-nav">
         <li>@Html.ActionLink("Home", "Index", "Home")</li>
         <li>@Html.ActionLink("About", "About", "Home")</li>
         <li>@Html.ActionLink("Contact", "Contact", "Home")</li>
</ul>

Add another ActionLink to open the Cars Index view:

<li>@Html.ActionLink("Cars", "Index", "Cars")</li>

Run the application, click the Cars link and you’ll be directed to the login page. There’s a link to register new users. Click on it a register yourself with the application. If all goes well then your registration will succeed and you’ll see “Hello ” + your username in the upper right-hand corner of the view. Click the Cars link again and you’ll get to an empty page:

Empty Cars list after initial run of application

Click the Create New link. You’ll directed to a simple page to add new Cars:

Default create car view generated by MVC 5

The text field for the Make property is OK for demo purposes. In reality we might need to pick the Make from a drop-down list so that we don’t end up with values like Ford, ford, FORD in the database. However, the text field for the category is completely off. We’d like to use the enumeration values in here. We don’t want to allow the users to type in just any category. The reason is that the scaffolding engine created the following code to render a field for the category in Create.cshtml:

@Html.EditorFor(model => model.Category)

It didn’t know how to render an editor for an enum so the best approximation was a text field. You probably agree that this is not OK. So we’ll need to digress a little from our main discussion on Entity Framework and do some other work before we resume: we’ll create a new template.

Add a folder called EditorTemplates within the Views/Shared folder so that the new template will be available for all views. Right-click this new folder and select Add, View… as editors are simply partial views. We need to be careful with the naming of the custom editor for the car category. We need to set it to the type of the Category property which is CarCategory. This is how the Razor engine will find the correct partial template. In case the property type is ‘int’ it will go look for a partial editor view called ‘int’. So in the View name field type in “CarCategory” and select the “Create as a partial view” option. You’ll get an empty file called CarCategory.cshtml.

We’ll need to get hold of the values in the CarCategory enumeration. Also, we want to make sure that if someone extends the enumeration then the drop down list Category editor will automatically pick up the new value. We’ll build an extension method that returns a sequence of SelectListItems based on the enumeration values.

Add a new folder called Extensions to the Web layer. Insert a class called EnumExtensions into that folder:

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

namespace Cars.Web
{
    public static class EnumExtensions
    {
        public static IEnumerable<SelectListItem> GetItemsAsSelectListItems(this Type type
            , int? selectedItem)
        {
            if (!typeof(Enum).IsAssignableFrom(type))
            {
                throw new ArgumentException("You must pass in an enum type.");
            }

            string[] names = Enum.GetNames(type);
            IEnumerable<int> values = Enum.GetValues(type).Cast<int>();

            return names.Zip(values, (n, v) =>
                new SelectListItem() {Text = n, Value = v.ToString(), Selected = v == selectedItem });
        }
    }
}

Make sure you modify the namespace to Cars.Web so that the extension method becomes available throughout the Web project without having to import a namespace.

In short we do the following in this code:

  • We check if the type is an enum. If it’s not, then an exception is thrown
  • We extract the names and integer values from the enum, such as Family=1,Sports=2 etc
  • We use the Zip LINQ operator to join the names and values into one sequence and build a list of SelectListItems
  • The selectedItem parameter will be used to set the originally saved category as the selected item. Without this check the saved category won’t be selected when updating a Car

If you’re not sure of the usage of the Zip operator, check its documentation here.

CarCategory.cshtml will have the following code:

@using Cars.Domain

@{
    var selectListItems = typeof(CarCategory).GetItemsAsSelectListItems((int?)Model);
}

@Html.DropDownList("", selectListItems)

You are probably wondering what this Model is. These templates also have Model properties like normal views. The Model in this case will be the object that the template is operating on, i.e. CarCategory which can be converted into a nullable int. The CarCategory will be set when editing a Car, hence the need for a nullable value.

Run the application, navigate to Cars, log in and click the Create New link. The car types should show up correctly in the drop down list. Add a couple of new cars. Each time you’ll be redirected to the Cars Index page with the updated list of cars:

New car objects inserted into database

Test the Edit link. You should see that the category is correctly selected in the drop down list.

Then play around with the other links, i.e. Details and Delete. They should work just fine. You can even check the database to see how the cars are stored:

Car objects shown in database

In the next part of this series we’ll look at the new asynchronous language features and logging in EF.

You can view all posts related to data storage on this blog here.

Elliot Balynn's Blog

A directory of wonderful thoughts

Software Engineering

Web development

Disparate Opinions

Various tidbits

chsakell's Blog

WEB APPLICATION DEVELOPMENT TUTORIALS WITH OPEN-SOURCE PROJECTS

Once Upon a Camayoc

Bite-size insight on Cyber Security for the not too technical.

%d bloggers like this: