Flattening Entity Framework relationships with ExpressMapper

Last Updated: April 20, 2016 | Created: April 20, 2016

This article looks at how Microsoft’s Entity Framework (EF) handles extracting data from nested relationships, i.e. linked EF classes. This is a process known as flattening. I will show that EF produces efficient SQL commands for this type of access.

I then talk about making development of such accesses by using an Object-to-Object mapper and introduce the new Flattening feature in ExpressMapper, a relatively new mapper that is quite lean and quick. The last part is a detailed description, with lots of examples, of how to use flattening in ExpressMapper and what is is capable of.

Quick background on flattening

If you understand the issues around relational databases, foreign keys and flattening then skip this.

Well constructed relational databases try to minimise the duplication of data, e.g. only have one copy of the customer details. They do this by creating extra tables to hold each of these unique pieces of data and using ‘foreign keys’ if another piece of data needs to refer to it. Let me give you an example.

In the diagram below, which shows the EF classes, a product has a ‘Variant’, which is linked by the foreign key ‘VariantId’ and that Variant can have a number of size information, given by the ‘Size’, which has a foreign key ‘SizeId’.

Product, ProductVariant, Size

The benefit of splitting the data like this is we can change say the Color information on a specific  ProductVariant and it is automatically picked up by all the products that point to it. In software terms we would call this an application of the DRY (Don’t Repeat Yourself) principal.

The down side if if we want to show a list of products on screen its quite likely that we want to include the Color and Size information. This means we need to ‘pick out’ the Color column from the linked ‘ProductVariant’ table, and the Name column in the Variant’s linked ‘Size’ table. This is called flattening and happens a lot in real-life applications.

How Entity Framework does flattening

I’m going to use a very simple example to show how EF does flattening. The diagram below shows a simple Father->Son->Grandson nested relationship.

ExpressMapper-father-son-grandson

The EF command to get all of the properties from all of the linked records would be:

var flattened = dbContext.Fathers.Select(f => new { 
    f.MyInt,
    f.MyString, 
    SonMyInt = f.Son.MyInt,
    SonMyString = f.Son.MyString, 
    GrandsonMyInt = f.Son.Grandson.MyInt,
    GrandsonMyString = f.Son.Grandson.MyString});

The result is an anonymous class containing all the data from all the relationship.

The T-SQL EF produces for this access

It turns out that EF produces the very efficient T-SQL command for this sort of flattening. The T-SQL command that EF puts out is as follows:

SELECT 
    [Extent1].[MyInt] AS [MyInt], 
    [Extent2].[MyInt] AS [MyInt1], 
    [Extent2].[MyString] AS [MyString], 
    [Extent3].[MyInt] AS [MyInt2], 
    [Extent3].[MyString] AS [MyString1], 
    [Extent1].[MyString] AS [MyString2]
    FROM   [dbo].[Father] AS [Extent1]
    INNER JOIN [dbo].[Son] AS [Extent2] 
        ON [Extent1].[Son_Id] = [Extent2].[Id]
    LEFT OUTER JOIN [dbo].[Grandson] AS [Extent3] 
        ON [Extent2].[Grandson_Id] = [Extent3].[Id]

This is an optimal T-SQL command and here is execution plan that proves it.

ExpressMapper-father-son-grandson-sqlNOTE: Flattening is only useful for reading data, as any update to a column in a related table can only be done in EF by reading in the inner class, updating its data and calling EF’s .SaveChanges(). However reading data for display is very common and therefore we want it to be really efficient.

Using an Object-to-Object Mapper with EF

My experience has been that you spend a LOT of time writing EF .Select() statements like the one above in even a small application. They are repetitive and boring, which often leads to making mistakes. This caused me to build the open-source package GenericServices, which uses an object-to-object mapper  (currently AutoMapper, but l am planning to change to ExpressMapper).

The idea behind using an object-to-object mapper (referred to as mapper from now on) is that you create a class, or classes, which represent what you need from the database. These classes are often called DTOs (Data Transfer Objects), or in ASP.NET MVC style ViewModels – I will refer to them as DTOs in this article.

The role of the mapper is to work out how to map the EF classes to the DTO(s). The mapper does this based on a set of rules. Let’s consider one implementation of an object-to-object mapper that will work with EF – ExpressMapper.

Introducing ExpressMapper

As I said earlier I have used AutoMapper, but I have been tracking the development of a new mapper called ExpressMapper, which has some advantages. ExpressMapper is newer than AutoMapper and relies on building LINQ commands for all its mappings. The main advantage is that its setup is much faster than AutoMapper, and one or two other small things make it fit with EF more easily.

However while I wanted to start using ExpressMapper but it didn’t have the flattening feature. So, with the help of ExpressMapper’s author, Yuriy Anisimov, I added the flattening feature. This is now released in version 1.8.1 which you can find as a Nuget package here.

I will give you three examples of how ExpressMapper’s flattening works and then describe the differences between ExpressMapper and AutoMapper, which is used more widely because it has been around a long time.

1. Flattening nested references

1.a. Non-null nested references

In the case of flattening ExpressMapper and AutoMapper uses a concatenated name rule,  e.g. SonMyInt would match Son.MyInt and SonGrandsonMyString  would match Son.Grandson.MyString. So, looking at our previous example of Father->Son->Grandson if we created a class as shown below and then ran the code at the end then ExpressMapper would produce the same sort of result, and SQL that my hand-coded select would have done.

public class FlattenDto
{
   public int MyInt { get; set; }
   public string MyString { get; set; }

   public int SonMyInt { get; set; }
   public string SonMyString { get; set; }

   public int SonGrandsonMyInt { get; set; }
   public string SonGrandsonMyString { get; set; }
}

...
//code to set up the mapping 
ExpressMapper.Mapper.Register<Father, FlattenDto>().Flatten();
ExpressMapper.Mapper.Compile(CompilationTypes.Source);
//code to execute the EF command and get the data
var flattened = dbContext.Fathers.Project<Father, FlattenDto>().ToList(); 

The SQL code is the same as I showed on the hand-written code.

1.b. Nullable nested references

If either the Son or the Grandson can be null then these relationships are called an ‘optional relationship’ in EF, or One-to-ZeroOrOne references in SQL. ExpressMapper handles null references, so handling null references mapping is fairly straight forward. However you do need to think that some of the properties may be null if a nested relationship is null, otherwise EF will complain that it can put a null in it.

For example if we take our previous example and we make the GrandSon an optional relationship with the following EF configuration (see the HasOptional on line 7) then the Grandson property may be null.

public class SonConfiguration 
   : EntityTypeConfiguration<Son>
{
   public SonConfiguration()
   {
      ToTable("Son");
      HasKey(t => t.Id);
      HasOptional(t => t.Grandson);
   }
}

Having done that ExpressMapper will handle not having a grandson (or a son if you set that as optional too). However we do need to change the DTO, as now the the SonGrandsonMyInt can now be null (see the int? on line 13 of this class definition from the Unit Tests of ExpressMapper).

//code to set up the mapping 
ExpressMapper.Mapper
    .Register<Father, FlattenDtoWithNullable>().Flatten();
ExpressMapper.Mapper.Compile(CompilationTypes.Source);
//code to execute the EF command and get the data
var flattened = dbContext.Fathers
    .Project<Father,FlattenDtoWithNullable>().ToList(); 

The result of this will be that SonGrandsonMyInt and SonGrandsonMyString will both be null if no Grandson relationship exists, but will contain the Grandson’s MyInt and MyString if the relationship does not exist.

NOTE: AutoMapper does not have this feature by default – see later section of differences between ExpressMapper and AutoMapper.

2. Running Linq commands on collections

As well as accessing columns in one-to-one relationships there is another feature that EF and ExpressMapper’s flattening can provide for one-to-many relationships. That is it can convert a small but useful number of LINQ collection commands into SQL commands. The commands that EF supports are:

  • Any: This returns true if there is one or more rows in the collection.
  • Count: This returns an int of the number of rows in the collection.
  • LongCount: Same as Count, but returns a long (64 bit) type.

To use these you simply add the method name on the end of a EF POCO class properly that implements IEnumerable (but not a string). Let me give you a very simple example. The code below shows a EF POCO class called FatherSons which contains a collection called Sons. The DTO below it has a property called SonsCount, which ExpressMapper turns into the LINQ command Sons.Count().

public class FatherSons
{
   public int Id { get; set; }

   public int MyInt { get; set; }
   public string MyString { get; set; }

   public ICollection<Son> Sons { get; set; }
}
public class FlattenFatherSonsCountDto
{
   public int MyInt { get; set; }
   public string MyString { get; set; }

   public int SonsCount { get; set; }
}

...
//code to set up the mapping 
ExpressMapper.Mapper
    .Register<FatherSons, FlattenFatherSonsCountDto>().Flatten();
ExpressMapper.Mapper.Compile(CompilationTypes.Source);
//code to execute the EF command and get the data
var flattened = dbContext.Fathers
    .Project<FatherSons, FlattenFatherSonsCountDto>().ToList(); 

The resulting SQL is again very good (see below):

 
SELECT 
    [Extent1].[Id] AS [Id], 
    (SELECT 
        COUNT(1) AS [A1]
        FROM [dbo].[Son] AS [Extent2]
        WHERE [Extent1].[Id] = [Extent2].[FatherSons_Id]) AS [C1], 
    [Extent1].[MyInt] AS [MyInt], 
    [Extent1].[MyString] AS [MyString]
    FROM [dbo].[FatherSons] AS [Extent1]

Full list of ExpressMapper flattening features

  1. Flattening looks at properties inside classes, either EF relationships or EF Complex Types. For the DTO to match it must match on two counts:
    1. The Name must match: The DTO name should be  a concatenating the names without the dots, e.g. Variant.Size.Name is accessed by having a property called VariantSizeName in the DTO.
    2. The Type must match: The type of the DTO property must be either:
      1. The same type as the inner property, e.g. SonMyInt can be of type int.
      2. A nullable version of type of the inner property, e.g. SonGrandsonMyInt is of type Nullable<int>. This is useful when you have optional relationships (see 3 below).
      3. A class that has been registered with ExpressMapper as mapping between the inner property and the DTO class (see 2 below for a better explanation!)
  2. You can have nested DTOs within DTOs. For instance in our Father->Son->Grandson examples you can have a property called SonGrandson, which is a DTO class. The only rule is you must also register the SonGrandson DTO class as well as the outer DTO. There is an example of this in the ExpressMapper Unit Tests – see test on line 95 of FlattenEntityTests.cs and note the registering of the extra DTO class on line 27 of the same file.
  3. Flattening can handle optional relationships, i.e. null references to a class. In that case it will return null for all the properties you access in the nested class(es). See example 1.b above.
  4. Flattening can handle circular references, e.g. if class person has a property called Boss of type person then you can have a DTO property such as BossBossBoss to get the third boss up (or null if the hierarchy doesn’t go that high).
  5. Flattening also looks at collection properties, i.e. one-to-many relationships.  If the DTO contains a property with the name of the collection, plus an ending of Any, Count or LongCount then it adds the LINQ method of that name. The property must be of the right type, i.e. bool, int or long respectively otherwise you get an ExpressMapperException.

Configuring flattening in ExpressMapper

  • Flattening only happens if you add the method .Flatten() to the registration of the mapping.
  • If you use ExpressMapper’s .Ignore() or .Member() methods in registration with Flattening then your .Ignore() or .Member() calls will take over from flattening, i.e. Flattening will not touch those source properties. The order in which you apply .Flatten, .Ignore and .Member does not matter.
  • The comparison of names follows ExpressMapper configuration, which defaults is case insensitive. For instance in the Variant.Size.Name case the match would work with variantsizename or any other upper/lower sequence of the same letters.
    NOTE: this is useful when you are mapping to json in an API and you often want the first letter of the property to be lower case.
  • Flattening only works on the Source side, i.e. it does not ‘unflatten’ if you do a DTO->class mapping. I did not implement this because it just doesn’t work with EF, which needs you to link to tracked classes. It is also very difficult to get right in all cases, e.g. what do you do with a Count??
    NOTE: That is why you see the command ExpressMapper.Mapper.Compile(CompilationTypes.Source) in the examples. Because I only use the class->DTO direction then I don’t bother to compile the reverse direction.

Comparison with AutoMapper

Many people, including me, are already using AutoMapper, so it is useful to point out the differences.

  1. AutoMapper is a bit more specific on matching the concatenated names, and has some extra features for changing this. My implementation in ExpressMapper simply matches the next part of the string as it goes, plus the type. I think AutoMapper would not accept vAriantsiZenAme but ExpressMapper’s Flatten will.
  2. AutoMapper has other flattening features, like GetMethod. ExpressMapper’s Flattening does not support that as they don’t work with EF.
    NOTE:  The way to overcome this is either by specific .Member() set ups or more elegantly by using DelegateDecompiler. DelegateDecompiler allows you to add ‘computed’ properties which DelegateDecompiler can turn into LINQ code that EF can execute. I use DelegateDecompiler in my GenericServices package and it is very helpful. You should look it up!
  3. AutoMapper does not, by default, handle nullable nested references (see example 1.b above). This is a plus for ExpressMapper.

Conclusion

I wanted to get three main things across in this article:

  1. Entity Framework is creates very efficient SQL code when accessing nested One-to-One or One-to-ZeroOrOne relationships.
  2. Object-to-Object mappers can make your life a lot easier in writing EF POCO classes to DTO code.
  3. ExpressMapper now has a good Flattening feature.

When I have the time (!) I plan to update my GenericServices project to use ExpressMapper. It has some nice features and my very simple testing says its about eight times faster on setup, i.e. the registering of the mappings, than AutoMapper. However ExpressMapper is about the same speed as AutoMapper on the actual mapping of the data.

I should say that EF doesn’t do so well at producing efficient T-SQL commands if you use EF’s .Include() method to eager load properties that are collections. I would like to write about that later if I have time, as there are definitely some things to watch out for in cases like that.

Well done for getting to here. The article is a bit long but hopefully useful.

Happy coding!

Handling Entity Framework database migrations in production – part 4, release of EfSchemaCompare

Last Updated: November 23, 2017 | Created: April 7, 2016

Some months ago I wrote an article on Simple-Talk ‘Deploying an Entity Framework Database into Production‘, with a more detailed series on this blog site (see list below). I have finally found time to release the package I mentioned in the articles as an open-source (MIT) project and also on NuGet as EfSchemaCompare.EF6.

In this article I explain where EfSchemaCompare is useful and give three real-world users of the package in my own e-commerce application development. The whole series deals with the issues of creating/updating a database schema in a robust and testable way. The list of articles are:

UPDATE: I have released a version of EfSchemaCompare for Entity Framework Core – see this article.

Where would you find EfSchemaCompare useful

The main reason to use EfSchemaCompare is if you use EF with a database but also want to:

  1. Take over the creation, definition or migration of the database rather than having EF handle it.
  2. You want to build a EF model that works with an existing database.

If you work this way then you have a problem: you need to make sure any changes to the database are matched by changes to the EF classes/configuration, or vise-versa. For instance, if you added a foreign key relationship in your database, or a new column to a table then you need to make the correct changes to the EF classes/configuration.

The important thing to see is that, other than when using EF reverse engineering (see next paragraph), EF never looks at the schema of the database, even when it is doing migrations. It just assumes the database in the state that EF’s metadata model says it is. (For more on how this works under the hood then see Max Vasilyev’s interesting article about how EF migrations are calculated and stored).

So one way to overcome any mismatch between EF and SQL is to re-import the database via EF’s reverse engineer the database option. However I have found that to be a rather blunt instrument as it adds all the possible relationships and also has fixed names for properties.

However now EfSchemaCompare gives you a second option. EfSchemaCompare is designed to check that EF’s view of the database is in line with what SQL says the database schema is. You can use it in your Unit Tests, or in your deployment scripts. It returns a go/no-go response with useful error/warning messages to spot what is wrong. It can also help you work out how to set up SQL tables to handle some of the more complex EF table relationships, like Many-to-Many tables (see example 3 near the end).

Let me give you three examples of how I use EfSchemaCompare.

Examples of how I use EfSchemaCompare

I was building an e-commerce web site on Azure and I wasn’t comfortable that EF’s Code First Migrations approach was robust enough in my situation (read the first article for more details on why). I therefore swapped over to using DbUp, which is a script-based database deployment/update tool.

However it turns out matching a database change to the form that EF needs is quite complex! I therefore wrote EfSchemaCompare . I will detail three different ways I use EfSchemaCompare in my Unit Tests and deployment tests.

1. General checking in Unit Tests

The NUnit Test below is part of my standard tests and it simply checks that EF’s current model of the database matches the database my Unit Test has.

[Test]
public void CompareEfSqlTestLabelAppDbOk()
{
    using (var db = new MyDbContext())
    {
        var comparer = new CompareEfSql();
        var status = comparer.CompareEfWithDb(db);

        Assert.True(status.IsValid, status.GetErrors());
        Assert.False(status.HasWarnings, string.Join("\n", status.Warnings));
    }
}

The method it uses, CompareEfSql.CompareEfWithDb, is fairly quick, assuming the MyDbContext has been used in the Unit Tests, and finds 90% of the issues that can occur. It also gives error messages that talk about the EF classes rather than in SQL terms, so its easier for software developers to understand.

Note: The above version assumes the EF POCO classes are in the same assembly as the EF DbContext. There is another version if you have your EF POCO classes in a different  assembly to the EF DbContext (I do!). There is also another version if you have multiple DbContexts covering one database.

2. Detailed checking before I release to production

The NUnit Test below checks my production SQL database against the development database. The SQL to SQL checking is more thorough, but only checks things that EF cares about (see list of limitations).

[Test]
public void CompareLabelAppDbWithAzureTestLabelAppDbOk()
{
    using (var db = new MyDbContext())
    {
        var comparer = new CompareSqlSql();
        var status = comparer.CompareSqlToSql( "LabelAppDb", "AzureLabelAppDb");

        Assert.True(status.IsValid, status.GetErrors());
        Assert.False(status.HasWarnings, string.Join("\n", status.Warnings));
    }
}

The method it uses, CompareSqlSql.CompareSqlToSql which is very quick. It takes either a connection string name, or a full connection string. The error messages are all about SQL, so can be harder to interpret if your aren’t use to SQL, but its comprehensive.

Note that I use the more strict version where my Unit Test fails on warnings (see line 10 test).  Warning are things that should not affect EF, but are worth knowing about. You may not want to be so strict, but in my application I am. See the documentation on the difference between errors and warning.

3. When I am making complex changes to the database

Some changes are really easy, like adding a new column to a table. However when you change relationships it can be a challenge to get the SQL and EF classes in step. There is another command, CompareEfGeneratedSqlToSql, which is very useful.

This generates a brand new database using EF, with the name of the DbContext but with ‘.EfGenerated’ appended on the end. It then compares this with your current database using SQL-to-SQL testing.

[Test]
public void CompareLabelAppDbWithAzureTestLabelAppDbOk()
{
    using (var db = new MyDbContext())
    {
        var comparer = new CompareSqlSql();
        var status = comparer.CompareEfGeneratedSqlToSql(db, "LabelAppDb");

        Assert.True(status.IsValid, status.GetErrors());
        Assert.False(status.HasWarnings, string.Join("\n", status.Warnings));
    }
}

The method it uses, CompareSqlSql.CompareEfGeneratedSqlToSql, is quite slow because it gets EF to create a new database. However it does provide a way to a) compare exactly what EF would have done against what your SQL database looks like and b) you get a EF generated database from your current EF classes/configuration to inspect yourself.

Some things, like EF’s Many-to-Many relationships, require the SQL database to have a new table with a specific name and setup. Failing to follow the style that EF needs will mean EF won’t fill in the many-to-many links. Adding the right tables is often a case of seeing what EF does by inspecting the ‘.EfGenerated’ database and copying the SQL EF generated into your new database (or implementing your own many-to-many handling of course, which I cover in Part 2 of this article).

The other thing I noticed is that EF is somewhat forgiving if you make mistakes when you use EF’s Data Annotations or EF’s Fluent API to alter the way the database works. For instance if you configure a required to optional relationship but give it a non-nullable foreign key it will add its own hidden nullable foreign key. In fact I found about three errors like this in my 22-table application, all around relationships.

EfSchemaCompare will point out these discrepancies and you can fix them. Your minor configuration mistakes can be found mainly by the column names, which normally contain the name with a _ in the middle. I have also found places where the Cascade delete option was different, again through me misconfiguring a relationship.

Conclusion

Hopefully this article will give you a feel for why you might find the NuGet package EfSchemaCompare.EF6 useful, plus some tips on how to use it in your application. Its certainly a niche tool, but if you are using EF on a database that is not controlled by EF then its a life-saver. Do have a look at the project on GitHub for documentation and Unit Tests that might give you more ideas on how to use it.

Finally I wanted to thank and Hakon Thomas who field tested the first version. Their feedback has shaped the tool and hopefully made the documentation a bit clearer too.

Happy coding.


Want some help with EF?

Are you looking for someone to help you use Entity Framework more effectively in your business? I am a contract software developer / architect with many years of experience and I am happy to work remotely.

Have a look at my Hire Me page and drop me a email via my contact me form to see if I can help.


 

Converting your ASP.NET MVC5 application to use Bower, Grunt and Gulp

Last Updated: November 5, 2016 | Created: February 9, 2016

I changed over to Visual Studio 2015 (VS2015) a while ago, and one of the main reasons for moving was to use some of the new features for handling web packages. I also wanted to look at the preview of ASP.NET Core 1 to see how they used these features.

VS2015 contains as standard Bower, which is a web package manager used across the whole of the web development community. In addition VS2015 supports a web build automation system using Grunt and/or Glup. These are great tools for a number of reasons that I won’t cover now, but at the end of this article I list some of the advantages I found when I swapped over – and I think they are really worth having!

Note: While this article talks about VS2015 it is also applicable to VS2013 as the same Bower, Grunt and Gulp features are available for VS2013 via add-on extensions. If you have VS2013 then read this article by Scott Hanselman on what packages you need.

I personally needed the power of these tools on an ASP.NET MVC5 e-commerce project which is nearing completion and we are doing fairly drastic things to the design and JavaScript front-end. I didn’t want to upgrade to ASP.NET Core 1 this late in the project, especially as ASP.NET Core 1 is very different to MVC5 and not released yet. I therefore set about harnessing these tools in an existing project, with the aim of following the ASP.NET CORE 1 style to make upgrading later as simple as possible.

NOTE: There is a sample application on GitHub to go with this article. In this application I have converted an existing ASP.NET MVC5 project away from using the normal NuGet/BundleConfig to using Bower and Grunt for handling web packages. It is open-source so please have a look and see if it is useful.

This sample application also contains a library called BundlerForBower, B4B for short, which takes over the role of MVC’s BundleConfig. I mention the use of B4B towards the end of this article, but I have also written a more detailed article about B4B called Introduction to BundlerForBower for ASP.NET MVC5.

UPDATE: BundlerForBower is now available on NuGet

BundlerForBower is available on NuGet – see https://www.nuget.org/packages/Bundler4Bower/ 

Swapping from NuGet to Bower

If you create a brand new ASP.NET CORE 1/MVC solution in VS2015 then the MVC project looks very different to the current ASP.NET MVC5 structure. This is because the whole structure has been reconfigured to be more applicable to modern web development (see the ASP.NET CORE 1 overview article for more information).

We are interested in how it handles the web packages because if we are going to adopt Bower etc. we want to use the same approach as the new ASP.NET structure so that any future upgrades will be simpler. There are many changes in ASP.NET CORE 1 but the key web package changes are:

  1. It automatically uses Bower, not NuGet, to load web packages like JQuery, Bootstrap etc.
  2. It includes a ‘Dependencies’ virtual folder that contains:
    1. A Bower folder that containing our web libraries, e.g. JQuery
    2. An npm folder that contains our build automation tools, e.g. Gulp and plugins.
  3. It doesn’t have an App_Start/BundleConfig.cs as Gulp does the bundling/minification.

So let us now change our existing MVC5 project to match this approach.

1. Use Bower, not NuGet for web package management

If you have an existing MVC5 application you won’t see the Manage Bower Packages option in any of your menus. It needs a file to unlock the feature. Therefore the very first step is to do is create a bower.json file to your MVC5 project, by right clicking the MVC project folder in the solution explorer and select: Add>New Item and then find the “Bower Configuration file” in the list of templates, e.g.

CreateBowerFile

This will create the file bower.json and activate bower. The bower.json is the file that Bower writes to when you load a packages. This is useful for package restore, i.e. restoring the bower files if haven’t already got them loaded. As you don’t normally include the web packages in your source control then you need this if loading the application into a different development environment.

You don’t have to, but I also set up a .bowerrc file. This is the Bower configuration file and contains things like what directory where the packages will be written to. I added the following in the .bowerrc file:

{
  "directory": "lib"
}

which set the top-level directory ‘lib’ as the place that bower will place the libraries it loads. If your don’t do this then I think (I haven’t checked and the docs don’t say) the default is the top-level directory ‘components’, based on this stackoverflow question.

Once you have these files you when you right click the MVC project folder in the solution explorer you should see a new option: Manage Bower Packages (Note: I have found that this command doesn’t immediately appear once you have added the files. Restarting the Visual Studio and reloading the solution fixes this).

Selecting the Manage Bower Packages function brings up a screen (shown below) which is very like the new V3 NuGet screen. However in this case it is accessing the bower.io API to allow you to browse the packages available via Bower.

ManageBowerPackages

BE WARNED: I have found a few of issues in the current Bower interface:

  1. I used the interface to try and find a package called ‘fontspy’ but it could not find it, although Bower’s online search said it was there. However if I went into the bower.json file and typed “fontspy” intellisence knew about it and gave me the version. I then right-clicked the bower.json file and selected Restore Packages and fontspy loaded ok. The interface never showed that fontspy was loaded.
  2. I found Underscore using the interface, but it only gave version 1.5.2, which is an older version. If I went into bower.json and typed “underscore” it found version 1.8.3. I used that version by using Restore Packages to load it (see point 1).
  3. The interface says ‘No dependencies’ for packages that do actually have dependences, which is a bit confusing. Clicking the ‘project URL’ and looking at a file called ‘bower.json’ will show you the actual dependencies. However, even if the screen doesn’t show the dependencies bower will load any dependant packages if required.

If you select and install a package two things happen:

  1. The bower.json file is updated with the loaded package(s).
  2. The package and any of its dependant packages are loaded to the directory pointed to by .bowerrc.

Here is the content of bower.json after JQuery, BootStrap and Microsoft.jQuery.Unobtrusive.Validation has been loaded.

{
  "name": "ASP.NET",
  "private": true,
  "dependencies": {
    "jquery": "2.1.4",
    "bootstrap": "3.3.5",
    "Microsoft.jQuery.Unobtrusive.Validation": "~3.2.3"
  }
}

By default the loaded packages are not included in the project, which is good as we don’t want them to be sent to the web site, or store in source control. However this means to see them we need to turn on the ‘Show all Files’ option in VS’s Solution Explorer, and after installing a new package you will need to ‘Refresh’ the Solution Explorer view. Here is a view of the loaded packages directories with bootstrap opened up.

Note: You won’t see the lib directory in the sample application as they are not included in source control. If you download the application you need to right-click on the ‘bower.js’ file and click Restore Packages. That will download the packages and populate the lib directory.

BowerLibShowingBootStrap

Note: you will see that package jquery.validation is loaded even though it does not appear in the bower.json file. This is because Microsoft.jQuery.Unobtrusive.Validation’s own bower.json files says it dependents on jquery.validation so Bower automatically loads that if it is not already there.

2. Remove web packages loaded by NuGet

Tip: It took me a bit of searching to find the equivalent web packages in Bower. JQuery was easy but some of the packages have difference versions with very similar names. It’s worth cross-referencing the files that NuGet loaded with the bower equivalent before you uninstall the NuGet versions.

Previously you used NuGet to load your web packages, but when changing to Bower I really recommend your remove those packages from NuGet. This ensures that you won’t confused between the two packages, and more importantly if Bower updates its packages there isn’t a chance that you are using an older package loaded by NuGet. I also find it nice to not have a long list of JavaScript files under the ‘Script’ directory – now I can put my JavaScript files in there and it’s not confusing.

To Uninstall NuGet loaded packages then simply open NuGet and select the ‘Installed’ tab, then ‘Uninstall’ the packages that Bower is now handling. Note that your current views and layouts will stop working, which leads me on to the next section.

Swapping from BundleConfig to Grunt/Gulp

In ASP.NET MVC5 CSS and JavaScript files were normally delivered by App_Start/BundleConfig.cs which also looked after bundling and minification in a released application. Also, if you required compiling of Less, Sass, TypeScript, CoffeeScript, templates, image spriting etc. you may have used Web Essentials.

The new way is to use build automation tools like Grunt or Glup to achieve the same thing. These tools have a large base of what are called ‘Plugins’ which include all the features of Web Essential and much more.

You have a choice of using either Grunt or Gulp, or both. They both do the same thing but in slightly different ways. I ended up using Grunt for no more reason than I found some useful commands in Grunt format. Gulp is the standard automation tool if you create a new ASP.NET CORE 1 project, but as I said either will do the job.

Note: If you want to use Gulp instead of Grunt then I recommend this ASP.NET CORE 1 documentation on Gulp. If you want an example Gulp file then create an ASP.NET CORE 1/MVC project and look at the gulpfile.js that it produces.

1. Installing Grunt

The build automation tools need to be loaded, and for that we use NPM. NPM is a library system that makes it easy to share code. Grunt (and Gulp), plus there extensions, called plugins, can be loaded by NPM into your application.

First you need to add a NPM configuration file to the project in a similar way you did for the Bower configuration file, i.e. right clicking the MVC project folder in the solution explorer and select: Add>New Item and then find the “NPM Configuration file” in the list of templates.

This adds a file called package.json and opens you in that file. Now you manually add the Plugins you need. Thankfully you get great intellisence help with package names and their versions, so it’s not too hard. What you put in depends on what you want to do, but here is my file as a start:

{
  "version": "1.0.0",
  "name": "ASP.NET",
  "private": true,
  "devDependencies": {
    "grunt": "0.4.5",
    "grunt-contrib-clean": "0.6.0",
    "grunt-contrib-cssmin": "0.14.0",
    "grunt-contrib-jshint": "0.11.0",
    "grunt-contrib-concat": "0.5.1",
    "grunt-contrib-uglify": "0.8.0",
    "grunt-contrib-watch": "0.6.1",
    "grunt-contrib-copy": "0.8.2"
  }
}

Here is link to the reference article that I got this from. The only extra command I added was grunt-contrib-copy, which I needed for copying the bootstrap font files.

Once you have filled or changed the package.json file you should right-click the file and click Restore Packages. This will cause npm to look for those packages and load them. If you do a Refresh in the VS Solution Explorer you should find the package/plugins in the ‘node_modules’ directory.

NOTE: Be warned. If you have made a mistake in the file you won’t get any kind of error feedback when you run Restore Packages – it still says ‘Installing packages complete’. However if the files don’t seem to update look at the Output Window>Bower/NPM.

2. Define your Grunt tasks

You define the tasks you want to call in the Grunt configuration file, using the normal right-click the project and select Add > New Item. Select the Grunt Configuration file option, leave the default name, gruntfile.js, and click the Add button.

There are lots of ways of doing this and not really the space to explain all the possibilities, but I do give some links later. To give you a general introduction I have listed a very cut-down gruntfile.js which only concatenates and minifies the CSS files.

module.exports = function (grunt) {

    // Project configuration.
    grunt.initConfig({
        concat: {
            css: {
                src: ['lib/bootstrap/dist/css/bootstrap.css', 'Content/Site.css'],
                dest: 'css/styles.css'
            },
        },

        cssmin: {
            css: {
                src: 'css/styles.css',
                dest: 'css/styles.min.css'
            }
        }
    });

    // Load the plugin that provides the tasks we need
    grunt.loadNpmTasks('grunt-contrib-concat');
    grunt.loadNpmTasks('grunt-contrib-cssmin');

    // Default task(s).
    grunt.registerTask('default', []);

    // Build task(s).
    grunt.registerTask('build:css', ['concat:css', 'cssmin:css']);
};

In the example above I listed the files I wanted to concatenate from the packages that Bower loaded earlier. The simplest way to do this is compare the names of the files you loaded via your existing BundleConfig class and look for the same name in the lib directory. The files you want are often in a directory called ‘dist’, but some packages like Microsoft.jQuery.Unobtrusive.Validation don’t follow that rule.

With the gruntfile.js set to the code shown above up then if you can open the Task Runner Explorer window (try View > Other Windows > Task Runner Explorer) then you should see something like this:

TaskRunnerWindowYou can see the concat:css and cssmin:css tasks, plus the build:css task which executes concat:css followed by cssmin:css. The result of running concat:css is you will find a file called ‘styles.css’ which is a combination (bundle) of the bootstrap.css file followed by the Site.css file. If you run cssmin:css then a second file called ‘styles.min.css’ will appear which is the minified version of ‘styles.css’.

You run commands by right-clicking on a command and selecting Run. Feedback is pretty good, with the output of the task shown to the right of the command part of the window.

There is plenty more you can do but the example above gives you the basic idea. I would refer you to these useful articles for more information.

Note: different gruntfile in the sample application

You will find that in the sample application I use a different approach to specifying the files that are in each bundle, because I want another feature, BundlerForBower (explained later), to also have access to the list of bundles and their files. Therefore I place the array of files in a json file which I store in the App_Data directory. You can see the complete version of my gruntfile.js here and the json file it reads here.

3. Binding actions to Visual Studio Events

TaskRunnerWindowYou can bind any of the actions in the GruntFile to a Visual Studio events: Before Build, After Build, Clean and Project Open. You do this by right-clicking on a command you want to link and selecting Bindings. Below is an example taken from the ASP.NET grunt documentation, where you bind the watch feature to Project Open.

These bindings are stored as a specially formatted comment at the top of the gruntfile.json.

4. Delivering CSS/JavaScript to the Brower

The new ASP.NET CORE 1/MVC6 project does not use BundleConfig.cs to combine and minify CSS and JavaScript. It uses some new razor <environment names=”Development”> feature to choose between individual files or the concatenated & minified files produced by the Grunt/Glup task. It seems that the approach is to include all the individual files in in Development mode. In Production/Release it includes the concatenated and minified file with a cache buster suffix added using the new asp-append-version tag inside a HTML <script>. The asp-append-version tag generates unique hash based on the file content (see this helpful stackoverflow answer explanation).

In an existing MVC5 project we don’t have the new <environment> or the asp-append-version tag so we need to find an alternative. There are two ways to do this:

a. Use BundleConfig.cs to deliver the concatenated files

One of the reasons I chose the Grunt approach is it created both a concatenated, non-minified file and the minified file for both CSS and JavaScript. This means I could create a bundle inside BundleConfig.cs that looked like this:

bundles.Add(new StyleBundle("~/Content/css").Include("~/css/mainCss.css"));

In development mode it would deliver one large CSS file, styles.css, which isn’t minified. In release mode it would use the styles.min.css file with a cache buster suffix, i.e. a string that changes when the file changes which makes sure an older, browser-cached version isn’t used.

b. Using the library BundlerForBower (B4B)

The above solution works but throws up loads of issues when debugging of JavaScript. As the whole point of using Bower etc. is because you have a lot of front-end code to debug the first solution is less than idea.

I therefore built a specialised version of the MVC5 BundleConfig, called BundlerForBower or B4B for short, which is specifically written to work closely with Bower, Grunt and Gulp. It also follows some of the design styles used in the current ASP.NET Core 1 applications.

B4B consists of some code that performs a similar role at MVC’s BundleConfig class plus two extension methods that are very similar Html helper methods to MVC5’s `Styles` and `Scripts` classes to deliver bundles in a view. It is therefore quite easy to convert an existing MVC5 views over to B4B by making the following replacements:

@Styles.Render(“~/Content/mainCss”) is replaced by @Html.HtmlCssCached(“mainCss”) and @Scripts.Render(“~/bundles/javaScriptBundle”) is replaced by @Html.HtmlScriptsCached(“javaScriptBundle”)

As well as bundles that deliver groups of files from within your application ASP.NET Core 1 has a set of tags for delivering files from a Content Delivery Network (CDN), with fallback capabilities. I have reproduced this feature in B4B as it is so useful. Public CDNs are available across the world for many of the standard libraries like JQuery and Bootstrap, and can be faster than local delivery especially if the user has already loaded the standard library when using another web site.

NOTE: I have written an article called ‘Introduction to BundlerForBower for ASP.NET MVC5‘ which describes B4B in detail. B4B is an open-source (MIT) project and is included in the sample application.

My reflections on using Bower and Grunt

I think that Bower and Grunt/Gulp are a really good step up from the old NuGet, BundleConfig and Web Essentials. I spotted a few issues that I have noted in this article, but they were easy to get round and are likely to be fixed soon. The difference was immediately apparent as I was now much more in control on my web packages. It solved three problems that I had with the NuGet, BundleConfig and Web Essential’s approach which are worth listing, as they point out some of the gains this new approach brings.

1. Better coverage of packages

I use a JavaScript package called JQuery-FontSpy, which isn’t in NuGet. Previously I had to copy the files I needed by hand from GitHub. However JQuery-FontSpy is, of course, available in Bower. This is typical of more specialised packages and one of the reasons why switching to Bower is so useful.

2. More up to date packages

I am using handlebars.js templates in my web application to dynamically create complex HTML at runtime via JavaScript. While a handlebars package is available via Nuget it is at version 3.0.0 which isn’t the latest and misses a feature I would have liked to use. However the Bower version is at version 4.0.3, which is the latest, and because Bower is the main way of releasing packages like this then it will always have the latest.

This reminds us that web packages only get into NuGet because someone takes the trouble to transfer it to NuGet, and they may not feel the need to keep it updated. Whereas Bower is the primary tool for deploying web packages so the latest and greatest of a package will be available.

3. Bower libraries can contain more information

The Nuget version of libraries like BootStrap and JQuery contain just the final .js and .min.js versions of the file. However if you load these libraries via Bower you find they have a directories that contain the various parts of the library.

This is very helpful for me as I change my e-commerce site from using BootStrap to the final CSS design scheme. While I don’t want much of BootStrap’s CSS I would like to use some of its modules, like Modal, to save me reinventing that code. If I was using NuGet I would have had to go the bootstrap site and make a custom download, which I would have to redo if any new version came out. However with Bower I simply installed the bootstrap package and add the individual modal JavaScript and CSS, via less, files I need from the bootstrap\js and bootstrap\less directories to my gruntfile.js build script. If I want to add another module, like tooltip, I just include those files in my build script too. This is a much nicer approach than a custom download.

Even more web tools…

I should mention an alternative/adjunct to Bower etc. in the form of ‘Node.js tools for Visual Studio’ extension for VS. This has some extra features over the plain Bower, Grunt/Glup such as JavaScript debugging and profiling. It is definitely worth a look.

I used Node.js as a separate application some years ago when developing a Single Page Application and Node.js was great, if a bit daunting to learn coming from a VS background. However it seems like ASP.NET CORE 1 is going to use Bower, Grunt/Gulp as standard so I decided to start with that for now. You can use both the build in Bower and the Node.js extension together, so maybe I might progress to the Node.js extension in the future.

Conclusion

I have really appreciated the new Bower, Grunt/Gulp tools. They have made changing my e-commerce web site over to a proper design much easier. I also find the build tools are more comprehensive, yet allow me to just run one commend, ‘build’, to make sure everything is up to date.

I did have to create BundlerForBower (B4B) for helping with the process (see separate article on this). B4B means I have one file, BowerBundles.json, to update and everything ripples through the system. I also like the Unit Test checking that B4B contains as it stops me forgetting something before I release to production.

Please feel free to take a copy of the sample application and play with it. Hopefully this article and the sample will help you convert to Bower and be ready for ASP.NET Core 1.

Happy coding!

Introduction to BundlerForBower for ASP.NET MVC5

Last Updated: October 16, 2016 | Created: February 9, 2016

I have recently changed over my ASP.NET MVC5 application to use Bower and Grunt for handling all my web packages, e.g. JavaScript and CSS libraries. You can read about this in another of my articles called Converting your ASP.NET MVC5 application to use Bower, Grunt and Gulp. In changing over that I tried to follow the approach that ASP.NET Core 1 uses with web packages. Most things, like its use of Grunt/Gulp for web build tasks was fine, but when it came to the the inclusion of JavaScript and CSS files in a HTML web page, then I had to find a new way.

This article is about my solution called BundlerForBower, or B4B for short, which is available in an open-source project. My B4B solution provides similar features to MVC5’s BundleConfig approach, but is designed specifically to work with Bower and the new Grunt/Gulp build process.

UPDATE: BundlerForBower now available on NuGet

BundlerForBower is available on NuGet – see https://www.nuget.org/packages/Bundler4Bower/ 

ASP.NET Core 1 approach

When swapping over to Bower and Grunt/Gulp I wanted to follow as closely as possible the approach that ASP.NET Core 1 uses, as I expect to change over to using ASP.NET Core 1 some time soon. As you will see from my other article it wasn’t hard to follow the same approach for most things, but for delivering JavaScript and CSS files in a HTML web page I had two problems.

The first was a technical problem, in that ASP.NET Core 1 uses new <link> and <script> tags which MVC5 does not have access to. The second was a design problem in that ASP.NET Core 1 approach was to have the user list the files to be delivered in TWO places: one in the Gulp file for the build process and the second in the HTML views that needed them. That isn’t DRY (Don’t Repeat Yourself)!

These two things spurred me on to create a better solution. In fact I have made B4B configurable to work in both an ASP.NET MVC5 application AND in the new ASP.NET Core 1 application as I think the design of B4B, which is DRY, is useful in ASP.NET Core 1 too.

The parts of BundlerForBower

I am going to describe the various part of B4B, but as a start let me introduce the three main parts.

  1. A extension class called BowerBundlerHelper which needs to be placed in you MVC application so that it has access to various MVC features.
  2. A BowerBundles.json file that contains the list of bundles and their files. This is used both by Grunt/Gulp to prepare the files and by B4B to deliver the correct files at run time. Note: This file should be placed in the App_Data directory of a MVC5 project (not sure where to put it in ASP.NET Core 1).
  3. A class library called B4BCore which the BowerBundlerHelper class uses to handling bundling. This class library also contains a useful class called CheckBundles that is useful for checking all your bundles are up to date before your release anything to production.

As you can see from point 2 this approach is DRY, as the same file that is used for building the concatenated and minified production files is also used by B4B to delivery the individual files in Debug mode and the production files in non-Debug mode.

Using BowerBundlerHelper to deliver bundles

The BowerBundlerHelper extension class has two Html helper methods that are very similar to MVC5’s Styles and Scripts classes, but applied as extension methods on the HtmlHelper class. They are:

  1. @Html.HtmlCssCached("bundleName"), which is equivalent to @Styles.Render("~/Content/bundleName")
  2. @Html.HtmlScriptsCached("bundleName"), which is equivalent to @Scripts.Render("~/bundles/bundleName")

B4B makes a decision to delivers individual files or the single minfied file that Grunt produced is defined by whether the code was compiled in DEBUG mode or not. This feature can be overridden on each method by the optional ‘forceState’ parameter.

Using BowerBundleHelper to deliver static files with cachebuster added

When delivering static files, e.g. images, you need to think about what happens if you change the file content. The problem is if you change the file content but not its name then if caching is turned on the user’s browser will use the old file content, not the new file content.

The BowerBundlerHelper has a command to turn a normal file reference into one containing a cache  busting value. For instance for an image you would use something like this in your razor view:

<img src='@Html.AddCacheBusterCached("~/images/my-cat-image.jpg")' />

There is a more detailed section on this later in this article.

BowerBundles.json file format

The BowerBundles.json file holds the data on what files are in reach bundle. This is equivalent MVC5′ s BundleConfig.cs in that you define your bundles here. I happen to think its a bit simpler than BundleConfig.cs, mainly because Grunt/Gulp is doing much of the hard work.

The key thing is that this file is the single source of what files are in what bundles. This file is used both by Grunt/Gulp to concatenate and minify the files and by B4B to deliver the right files at run time. It is also used by B4B’s CheckBundles (see later) class to check that your bundles are all correct.

The file is, by default, must be called BowerBundles.json and should be placed in MVC5’s App_Data directory. The file format is a json object which can contain a mixture of two formats: one for files delivered from your web application and a second format for using a Content Delivery Network (CDN) to delivery standard libraries

1. Delivery of files from your application

For delivering bundles of files from your application then each bundle is property that contains an array of strings holding the relative file references to each file you want in a bundle. Here is a simple example:

{
  "mainCss": [
    "lib/bootstrap/dist/css/bootstrap.css",
    "Content/site.css"
  ],
  "standardLibsJs": [
    "lib/jquery/dist/jquery.js",
    "lib/bootstrap/dist/js/bootstrap.js"
  ],
  "appLibsJs": [
    "Scripts/MyScripts/*.js"
  ]
}

The name of the property, e.g. mainCss is the name of the bundle and the array is the list of files in order that should be included. So to include the mainCss bundle you would include the command @Html.HtmlCssCached("mainCss") in your _Layout.cshtml file, or whatever View that needed it.

As you can see you can specify an exact file, or add a search string like "Scripts/MyScripts/*.js", but the order is then dependant on the name and some (many) files need to be loaded in a specific order. Directory searches can include file searches as well, e.g "Scripts/*/*.js", but at the moment I have not implemented the Grunt/Gulp’s /**/ search all directories and subdirectories feature.

Please see the section in the ReadMe file which gives you the steps to add a new file bundle.

2. Delivery of files from Content Delivery Network (CDN), with fallback

B4B can also handle the delivery of JavaScript via a Content Delivery Network (CDN). You can define a CDN url, with fallback in the BowerBundles.json file using the following syntax:

  "standardLibsCndJs": [
    {
      "development": "lib/jquery/dist/jquery.js",
      "production": "jquery.min.js",
      "cdnUrl": "https://ajax.aspnetcdn.com/ajax/jquery/jquery-2.1.4.min.js",
      "cdnSuccessTest": "window.jQuery"
    },
    {
      "development": "lib/bootstrap/dist/js/bootstrap.js",
      "production": "bootstrap.min.js",
      "cdnUrl": "https://ajax.aspnetcdn.com/ajax/bootstrap/3.3.5/bootstrap.min.js",
      "cdnSuccessTest": "window.jQuery && window.jQuery.fn && window.jQuery.fn.modal"
    }
  ]

The individual properties are explained in the B4B ReadMe CDN section but you can see from the data above we have both a ‘development’ file, which is delivered in debug mode, and a ‘production’ file that is delivered if the ‘cdnSuccessTest’ fails.

Using the above CDN bundle, standardLibsCdnJs, in your application will insert two <script> loads, each with a JavaScript section which used the {cdnSuccessTest} code to check that the CDN had loaded properly. If the test was false then the CDN worked and nothing else happens. However if it fails then the JavaScript inserts a extra <script> load to pull in the file given by the {production} property. The code output by the first CDN definition would look like this:

<script src='https://ajax.aspnetcdn.com/ajax/jquery/jquery-2.1.4.min.js'></script>;
<script>
   (window.jQuery||document.write(
   "\x3Cscript 'src=~/js/jquery.min.js?v=SnW8SeyCxQMkwmWggnI6zdSJoIVYPkVYHyM4jpW3jaQ\x3C/script>'));
</script>

Please see the section in the ReadMe file which gives you the steps to add a new CND bundle.

NOTE: At the moment I have not implemented CSS CDN support. The testing code is quite complex and I left it out for now. If someone wants to implement that then please let me know.

Adding a cachebuster to other static files

As well as bundles B4B can help with individual static files, e.g. images. These is a command called @Html.AddCacheBusterCached(“~/images/my-cat-image.jpg”). In this case a checksum of the file will be calculated based on its content and added as a cachebuster value.

In the case where you can pre-calculate the cachebsuter value then there is a second version which looks like this @Html.AddCacheBusterCached(“~/js/jquery.js”, “2.1.4”).

The way that the cachebuster is applied is set by the `StaticFileCaching` property in the B4B config. This means you can use different ways of applying caching busting by adding your own `BundlerForBower.json` file with a different cache busting scheme (see next section).

By default B4B uses the standard ASP.NET approach of adding a suffix, e.g. the command @Html.AddCacheBusterCached(“~/images/my-cat-image.jpg”) would produce the following html.

http://localhost:61427/images/my-cat-image.jpg?v=xKyBfWHW-GTt8h8i8iy9p5h4Gx9EszkidtaUrkwVwvY

Note: I use the SHA256 Hash which produces a hash which is related to the content. However this does take some time on large files, so I cache the SHA256 Hash to improve later access times.

B4B’s options: BundlerForBower.json

I have tried to make B4B flexible so I have put some of the key setting is a json file so that you can override them if you want to change things. I is also useful for me as ASP.NET Core 1 will need different settings. Below is the default setting for B4B, held in the file defaultConfig.json.

{
 "BundlesFileName": "BowerBundles.json",
 "StaticFileCaching": "{fileUrl}?v={cachebuster}",
 "JsDirectory": "js/",
 "JsDebugHtmlFormatString": "<script src='{fileUrl}'></script>",
 "JsNonDebugHtmlFormatString": "<script src='{fileUrl}?v={cachebuster}'></script>",
 //see http://stackoverflow.com/a/236106/1434764 about why we need to escape the document.write()
 "JsCdnHtmlFormatString": "<script src='{cdnUrl}'></script><script>({cdnSuccessTest}||document.write(\"\\x3Cscript src='{fileUrl}?v={cachebuster}'\\x3C/script>\"));</script>",
 "CssDirectory": "css/",
 "CssDebugHtmlFormatString": "<link href='{fileUrl}' rel='stylesheet'/>",
 "CssNonDebugHtmlFormatString": "<link href='{fileUrl}?v={cachebuster}' rel='stylesheet'/>",
 "CssCdnHtmlFormatString": "" //I have not currently implemented CDN for CSS files. Doable, but complicated.
}

I have used meaningful names to make the setting more comprehensible. These cover things like the names/locations of the directories where the minified files are found and the <link> and <script> code they output.

Can I point out that you can see a parameter in the setup called {cachebuster}. B4B adds a suffix to production files, just like MVC’s BundleConfig does, so that if the file changes the the new file will be used rather than the previous version in the browers local cache.

I actually use a SHA256 Hash as the cachebuster suffix rather that say the time the file was last written. This allows me to just rebuild everything and the caching suffix won’t change on files where the content is the same.

Changing the B4B options

I have used a flat object structure as that allows you to override just the item(s) you want while leaving the other properties at their default state. For instance to override just the directory where B4B looked for the JavaScript minified files then you would place the following json in a file called BundlerForBower.json in the MVC App_Data directory.

{
  "JsDirectory": "differentTopDir/bundles/"
}

See the following examples from the sample application:

  1. Override just the name of the bundle file see this file
  2. Override all the properties – see ASPNET Core 1 Config/bundlerForBower.json

Note: This last example shows how you would change the setting to match what ASP.NET Core 1 would need.

Unit Testing your bundles

Early on I was using a prototype of B4B in an e-commerce site I am working on. I deployed some code to my test site and it didn’t quite do what I had thought it should. I realised I had changed some JavaScript code and had not rebuilt the minified file.

I am pretty paranoid about problems that could hit a production site so I build a fairly comprehensive set of tests to check for any problems in the JavaScript and CSS bundles. The class is called CheckBundles.

To Unit Test your bundles then you need to create the CheckBundles in such a way that it knows where you MVC project is. If you are using the standard setup then the ctor can work this out by giving a type that is in your MVC application, e.g.

var checker = new CheckBundles(typeof(BowerBundlerHelper));

Notes:

  1. I use typeof(BowerBundlerHelper) rather than something like typeof(HomeController) as I wanted a type that did not need me to add the System.Web.Mvc assembly to my Unit Tests.
  2. There are other version of the CheckBundles ctor if you have an unusual setup. Please consult the CheckBundles code.

You most likely should run two tests:

  1. checker.CheckAllBundlesAreValid(). This checks all the bundles found in the BowerBundles.json file and returns a list of error messages. If there are no errors it returns an empty collection. (see sample project CheckBundles Unit Test example). The rules its checks against are:
    – Does the bundle contain any file references?
    – Do any of those file references use a search string that B4B does not support, e.g /**/
    – Do all of those files and their directories exist?
    – Does the concat file exist? (can be turned off via ctor param if not using concat files).
    – Was the concat file updated more recently than all the files referenced in the bundle?
    – Does the minified file exist?
    – Was the minified file updated more recently than the concat file (or all the files referenced if no concat)?
    For CDN bundles it checks:
    – Does the configuration support CDN for this file type?
    – Does your CDN bundle contain all the properties that the CDN format string needs?
    – Does any of the file definitions contain a search pattern? That is not allowed.
    – Does the ‘Development’ file and the ‘Production’ file exist?
  2. checker.CheckBundleFileIsNotNewerThanMinifiedFiles(). This does what it says. It checks that you haven’t changed the BundleFile and not run the Grunt/Gulp build process to ensure the minified files are up to date.

There is a really good example of using these methods to check your MVC bundles in the sample application. Have a look at this Unit Test class which uses CheckBundles in an NUnit based Unit Test.  I find this very helpful.

Conclusion

Hopefully this article, plus the sample application with its own ReadMe files and Unit Tests will give you good idea on whether B4B could help you. I do recommend you look at the other article called “Converting your ASP.NET MVC5 application to use Bower, Grunt and Gulp” for an overview of how to use Bower etc and how B4B fits into this.

I would appreciate your feedback on B4B. I have used it and been very happy with it, but I haven’t created a NuGet package yet. Anyone got a good link on how to produce multiple versions for each .NET version?

Happy coding!

How to take an ASP.NET MVC web site “Down for maintenance”

Last Updated: June 18, 2016 | Created: December 22, 2015

I am working on an ASP.NET MVC5 e-commerce site and my focus is on how to apply database migrations to such a site (see previous articles on this subject). I have decided that for complex database changes I will take the web site “Down for maintenance” while I make the changes. This article details how, and why, I implemented this feature.

Having researched how to at a site offline I found some very good examples (see Khalid Abuhakmeh‘s good post and this helpful stackoverflow question & answer by FaNIX). They were really helpful (thanks guys) but my needs were slightly different. This article, which forms part of my series on Handling Entity Framework database migrations in production, describes my solution.

What I am trying to achieve

I want to create commands that allow me to stop all users, other than an admin user, accessing the web site. I am doing this to stop the database being accessed so that I can carry out a database migration. A database migration is normally followed by an updating the software, and I want the new software to obey any ‘offline’ mode that the last software has. This allows my admin user, or the automation system, to check the new site before bringing it back online.

From the customer point of view want to have friendly messages so that they know what is happening. I especially don’t want someone to be half way through placing an order and then lose it because the site suddenly goes down. That is definitely a way to upset customers!

Bring all these points together then the process for taking the web site down, doing the migration, checking it and bring it back up had the following requirements:

  1. I want to warn my customers, so I put up a “the site will go down for maintenance in xx minutes”.
  2. When the site is down it should:
    1. Show a useful message to the customers.
    2. Return a 503 Service Unavailable HTTP status code (so that the web crawler don’t index the pages).
    3. Still allow my admin user to access to the site so that I can check the upgrade had worked before maying the site live again.
  3. If I upload new software then that also should come up as “down for maintenance” until the admin user has checked it out and makes it back online.
  4. Finally my site is Microsoft’s Azure Cloud offering so I have the following technical requirements:
    1. I cannot log in locally, which affects how I know who is the admin user.
    2. I also want a solution that will handle scaling up on Azure, i.e. it must work if I have multiple instances of the web site running. This affects how I store the offline information.

Why didn’t I use app_offline.htm?

While adding  a file called app_offline.htm to your root directory is a very quick and simple way of taking an ASP.NET web site offline (see article by Scott Guthrie) doing some research pointed out some issues. Khalid Abuhakmeh‘s post points out that app_Offline.htm could cause problems with search engines (read his post for a fuller list of issues).

However the biggest issue for me was the app_offline.htm approach does not let anyone access the site. I wanted to be able to log in and test the site after an upgrade but before making it live again (requirement 2.3). That single requirement meant I had to find another way.

The other plus side to the solution I developed is a bit specific to my application, but it is worth mentioning. My solution uses Azure WebJobs which access the database. I therefore needed to stop these from accessing the database during the upgrade. There were a number of ways I could have done that, but it turns out that WebJobs can see the special offline file (described later), which can be used to put the WebJobs into a ‘stalled’ state during the upgrade. This meant my solution could stop the whole site including associated background tasks.

Using an Action Filter to redirect users

As Khalid Abuhakmeh and FaNIX suggested the best way to implement this is by adding a MVC Action Filter. This intercepts each action call and allows you to change what happens. Here is my Action Filter code:

public class OfflineActionFilter : ActionFilterAttribute
{
    public override void OnActionExecuting(ActionExecutingContext filterContext)
    {
        var ipAddress = HttpContext.Current.Request.UserHostAddress;
        var offlineHelper = new OfflineHelper(ipAddress, 
              filterContext.HttpContext.Server.MapPath);

        if (offlineHelper.ThisUserShouldBeOffline)
        {
            //Yes, we are "down for maintenance" for this user
            if (filterContext.IsChildAction)
            {
                filterContext.Result = 
                     new ContentResult { Content = string.Empty };
                return;
            }
                
            filterContext.Result = new ViewResult
            {
                ViewName = "Offline"
            };
            var response = filterContext.HttpContext.Response;
            response.StatusCode = (int) HttpStatusCode.ServiceUnavailable;
            response.TrySkipIisCustomErrors = true;
            return;
        }

        //otherwise we let this through as normal
        base.OnActionExecuting(filterContext);
    }
}

The decision as to whether we should tell the user that the site is “down for maintenance” is done by the OfflineHelper class, which I will describe later, and its sets the ThisUserShouldBeOffline property to true. If true (see test on line 9) then we stop the normal page display and redirect them to the “Offline.cshtml” view while also setting the StatusCode to ServiceUnavailable (503) so web crawlers won’t index the pages while offline.

This action filter needs to be run on all actions. To do this we add it to the GlobalFilters.Filters in the Global.asax.cs file, e.g.

protected void Application_Start()
{
    AreaRegistration.RegisterAllAreas();
    //we add the filter to handle "down for maintenance"
    GlobalFilters.Filters.Add(new OfflineActionFilter());
    FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
    ... etc.

The OfflineHelper class

I had to decide how the application would know it was in “down for maintenance” mode. In the end I decided to use the absence/presence of simple text file to control the mode. Using a file seems a bit archaic, but it fits the requirements:

  • I didn’t want to use the database, as I want the database quiescent during migrations.
  • A file would be read when new software is loaded, which would continue to be in “down for maintenance” mode until the admin user used a command to delete the file.
  • A simple file will work with Azure’s multiple instances when you scale up because all instances share the same local file system (see this helpful stackoverflow answer).
  • It allows an automated deployment script to take the site offline by writing an appropriately formatted file, and take it back online by deleting the file.
  • Plus if anything goes wrong I can use FTP to manually read, write or delete the file.

Here is the code from the OfflineHelper class:

public class OfflineHelper
{
    public static OfflineFileData OfflineData { get; private set; }

    /// <summary>
    /// This is true if we should redirect the user to the Offline View
    /// </summary>
    public bool ThisUserShouldBeOffline { get; private set; }

    public OfflineHelper(string currentIpAddress, 
          Func<string, string> mapPath)
    {

        var offlineFilePath = 
            mapPath(OfflineFileData.OfflineFilePath);
        if (File.Exists(offlineFilePath))
        {
            //The existence of the file says we want to go offline

            if (OfflineData == null)
                //We need to read the data as new file was found
                OfflineData = new OfflineFileData(offlineFilePath);

            ThisUserShouldBeOffline = DateTime.UtcNow.Subtract(
                OfflineData.TimeWhenSiteWillGoOfflineUtc).TotalSeconds > 0 
                && currentIpAddress != OfflineData.IpAddressToLetThrough;
        }
        else
        {
            //No file so not offline
            OfflineData = null;
        }
    }
}

As you can see from the code the absence of the ‘offline file.txt’ file is a simple test as to whether we should even consider being offline. If there is a file, and it hasn’t already been read in then we read it. Using this approach means we only take the performance hit of reading the file once, which is done via the OfflineFileData class (explained later).

If the offline file exists then there is a test to see if this user is allowed to access the site. If the time for the site to be offline hasn’t happened yet, or if the user is coming on the stored IP (which we will see later is taken from the authorised person who set the site to go offline) then the user is let through.

As we will see later the static OfflineData property is useful for showing messages.

The OfflineFileData class

The OfflineFileData class is in charge of the offline file and showing/changing its content. The class is a bit long so I will show it as two parts: a) the reading of the file, which is done when creating the OfflineFileData class, and b) the GoOffline and RemoveOffline commands

a) Reading the Offline File

The code is as follows:

public class OfflineFileData
{
    internal const string OfflineFilePath = "~/App_Data/offline file.txt";
    //The format of the offline file is three fields separated by the 'TextSeparator' char
    //a) datetimeUtc to go offline
    //b) the ip address to allow through
    //c) A message to show the user

    private const char TextSeparator = '|';

    private const string DefaultOfflineMessage = 
        "The site is down for maintenance. Please check back later";

    /// <summary>
    /// This contains the datatime when the site should go offline should be offline
    /// </summary>
    public DateTime TimeWhenSiteWillGoOfflineUtc { get; private set; }

    /// <summary>
    /// This contains the IP address of the authprised person to let through
    /// </summary>
    public string IpAddressToLetThrough { get; private set; }

    /// <summary>
    /// A message to display in the Offline View
    /// </summary>
    public string Message { get; private set; }

    public OfflineFileData(string offlineFilePath)
    {
        var offlineContent = File.ReadAllText(offlineFilePath)
            .Split(TextSeparator);

        DateTime parsedDateTime;
        TimeWhenSiteWillGoOfflineUtc = DateTime.TryParse(offlineContent[0], 
              null, System.Globalization.DateTimeStyles.RoundtripKind,
              out parsedDateTime) ? parsedDateTime : DateTime.UtcNow;
        IpAddressToLetThrough = offlineContent[1];
        Message = offlineContent[2];
    }
    //more below ....
}

The code is fairly obvious. It reads in three fields which it expects in the file and sets the three properties.

  1. TimeWhenSiteWillGoOfflineUtc: The DateTime in UTC format as to when the site should be offline.
  2. IpAddressToLetThrough: The IP of the admin person that put the site into offline mode, so we can let that particular person through.
  3. Message: An message that the admin person can give, like “Expect to be back by 9:30 GMT”

UPDATE: There was a bug in the parsing of the date, which did not take into account the UTC Z on the end. I have updated the DateTime.TryParse call to use the System.Globalization.DateTimeStyles.RoundtripKind style which has fixed it.

b) The GoOffline and RemoveOffline commands

The last part of the OflineFileData class contains the commands to put the site into, and take up out of, offline mode. I build these as static methods and are shown below:

public static void SetOffline(int delayByMinutes, string currentIpAddress, 
      string optionalMessage, Func<string, string> mapPath)
{
    var offlineFilePath = mapPath(OfflineFilePath);

    var fields = string.Format("{0:O}{1}{2}{1}{3}",
        DateTime.UtcNow.AddMinutes(delayByMinutes), TextSeparator, 
        currentIpAddress, optionalMessage ?? DefaultOfflineMessage);

    File.WriteAllText(offlineFilePath, fields);
}

public static void RemoveOffline(Func<string, string> mapPath)
{
    var offlineFilePath = mapPath(OfflineFilePath);
    File.Delete(offlineFilePath);
}

I think the code is fairly self explanatory. Note that the use of the the Func<string, string> mapPath is used to pass in the Server.MapPath method from the MVC Action. This also allows the code to be easily Unit Tested.

The Offline.cshtml View

The View ‘Offline.cshtml file is placed in the Views/Shared directory and looks like this (note: I am using bootstrap for my CSS)

@{
    ViewBag.Title = "Offline";
}

<div class="container">
    <div class="row">
        <div class="col-md-12 text-center">
            <h2>Our site is not currently available.</h2>
            <h3>@LabelApp.Utils.OfflineHelper.OfflineData.Message</h3>
        </div>
    </div>
</div>

As you can see this fairly simple view that says the site is offline and also shows a message that the admin person entered when GoOffline is called. This could be something like “We expect to be back up at 7am”.

The MVC Actions

The last piece of the puzzle is the MVC actions that the admin user calls to go offline or return to normal working. They are pretty simple, but I give them in case you aren’t sure how to do this. The actions go inside a Controller, which I haven’t shown and you need some sort of way to make them visible in the menu when a user with the Admin rule is logged in.

[Authorize(Roles = "Admin")]
public ActionResult GoOffline()
{
    return View(new OfflineModel());
}

[HttpPost]
[ValidateAntiForgeryToken]
[Authorize(Roles = "Admin")]
public ActionResult GoOffline(OfflineModel dto)
{
    OfflineFileData.SetOffline(
         dto.DelayTillOfflineMinutes, 
         HttpContext.Request.UserHostAddress, 
         dto.Message, 
         HttpContext.Server.MapPath);
    return View("Index");
}

[Authorize(Roles = "Admin")]
public ActionResult GoOnline()
{
    OfflineFileData.RemoveOffline(HttpContext.Server.MapPath);
    return View("Index");
}

Again, fairly straightforward. Note that the ‘HttpContext.Request.UserHostAddress’ returns the IP address of the current user as a string. This is stored in the offline test file so that we can let that user through the offline check. Also OfflineModel model contains an int property called DelayTillOfflineMinutes and the string Message that the admin person can optionally add.

What I have not shown

I have not shown the simple banner that appear when the web site is set to offline. This is added to the default layout file, normally called _Layout.cshtml in the Views/Shared folder. It accesses the static property OfflineData in the OfflineHelper class and if not null can calculate and show the time till the site goes offline as a warning to the user.

Also, in my system I give feedback to the admin user that the system after the offline/online calls as, from their point of view, nothing has obviously changed.

Down sides of this approach

I always like to look at the down sides of any approach I use. When architecting any system there is nearly always a trade off to be had. In this case we are putting an Action Filter that is called on every action call, which has a performance impact. The main performance costs are:

  1. Checking if the file exists.
  2. Reading the file content.

In my first version I read the file every time, which if there was a file then we had a 3ms with +- 3ms deviation overhead. In the newer version I only read the file on the first time we find it. This improved the performance for the case where the offline file exists.

I have instrumented the creation of the OfflineHelper in the  OfflineActionFilter and 99% of the time is in the OfflineHelper, which you would expect. When running on a B1 Basic single core , i.e. not very powerful, and the time that the OfflineHelper takes are:

  • When online: average 0.3ms, +-0.1ms deviation
  • When offline or going offline : average .4ms, +- 0.1ms deviation

Note: Has approx 6ms cost when first read of file.

Clearly there is a cost to using this approach, as File.Exists() takes some time. It would be possible to add some caching, i.e. you only look for the file if more than x seconds has passed since you did so. At the moment I am happy to live with these figures.

Other than that I cannot see any other major problems with this approach.

Conclusion

There are many ways to handle taking an ASP.NET MVC web application “down for maintenance” but for my particular requirements when doing data migrations this seems a good choice. I particularly like that if you upload new software it restarts still in offline mode, which is what I want as I can then check the migration + new code works before taking it back online.

Please do have a look at the series Handling Entity Framework database migrations in production for more on database migrations. It is a big subject, but important.

I hope this helps you. Please do feed back if you have any suggestions.

Happy coding!

Handling Entity Framework database migrations in production – part 2, Keeping EF and SQL scheme in step

Last Updated: November 23, 2017 | Created: November 4, 2015

This is the second article in a series about using Microsoft’s data access technology, Entity Framework (EF) in an application that to be ‘always on’, in my case an ASP.NET MVC e-commerce web site, yet is still being developed so there is a need to update the database schema, i.e. the tables, views, constraints etc. that define how the data is held in the database.

In this article I look at the problem of keeping EF’s view of the database schema, known as the EF database model, in step with the actual SQL database schema. The list of articles will be:

UPDATE: I have released a version for Entity Framework Core – see this article.

I will start by talking about how EF works, especially around how EF builds its in-memory view of the database, known as the database model, and what happens when it applies database migrations.

Note: Please see the first article for my reasons why I decided EF’s standard migration feature was not sufficient.

Matching Entity Framework changes to Database changes

EF is great to develop with as it does a number of clever things in the backgrounds to help developers. One of the main useful things EF does is make a relational database look like a set of linked classes. This is very clever, but to do this EF’s view of the database relationships must match the reality of what is in the d. To understand why this is problem then I need to describe how EF knows about the database schema.

How EF finds out about the database schema

EF holds a view of what it thinks the database schema is in what it calls the ‘database model’. This is a set of metadata that it builds at application start-up from the EF DbContext class and the associated classes referred to by your DbContext. There are therefore two main ways of telling EF what the database schema looks like. They are:

  1. EF uses your code to define the database schema.
    In this mode the developer normally defines/designs the classes and configures a special class called DbContext, which EF uses to calculate the EF metadata model, i.e. its view of what the database schema should look like. Then used what EF calls a ‘database initializer’ to create the database. There are then ways to update the database if you change the EF database model, see Code First Migrations.
  2. EF imports the schema of and existing database to create the data classes.
    The other way is to allow EF to build the classes and DbContext based on the schema of an existing database. It does this by a one-time scan of the database schema and using templates to build the classes and DbContext (see another of my articles which describes this in detail). If you change the database then you can either re-import database schema again, or there is also a way of using Code First Migrations with an existing database to handle update.

The important thing to see is that, other than the one-time scan in point 2, EF never looks at the schema of the database, even when it is doing migrations. It just assumes the database in the state that EF’s metadata model says it is. (For more on how this works under the hood then see Max Vasilyev’s interesting article about how EF migrations are calculated and stored).

What happens if EF’s database model is not correct?

EF will only find out there is a mismatch between its database model and the actual database schema when it accesses the part of the database that does not match. I have not tried all possible combinations, but the error I got when I added a properly called ‘EfOnlyProperty’ to an EF class without a corresponding column in the table was:

System.Data.Entity.Infrastructure.DbUpdateException : An error occurred while updating the entries. See the inner exception for details. —->
System.Data.Entity.Core.UpdateException : An error occurred while updating the entries. See the inner exception for details. —->
System.Data.SqlClient.SqlException : Invalid column name ‘EfOnlyProperty’.

Another example is when EF creates a database then it can add some hidden tables to facilitate many-to-many relationships (see my article on how EF handles many-to-many relationships). This means that any changes to the database must include these extra tables for EF to work properly. If a class called ‘Parent’ has a many-to-many relationship with a class called ‘Child’ and the linking table is missing then you get this error.

System.Data.Entity.Infrastructure.DbUpdateException : An error occurred while saving entities that do not expose foreign key properties for their relationships. The EntityEntries property will return null because a single entity cannot be identified as the source of the exception. Handling of exceptions while saving can be made easier by exposing foreign key properties in your entity types. See the InnerException for details. —->
System.Data.Entity.Core.UpdateException : An error occurred while updating the entries. See the inner exception for details. —->
System.Data.SqlClient.SqlException : Invalid object name ‘dbo.ParentChild’.

I needed an EF/SQL sanity checker

The idea of changing my database and having EF out of step was not appealing at all. That way you just don’t know what will happen, which is deadly.

I could build the database using SQL and use EF’s import existing database method described earlier. However I know from experience that it can be a pain when database updates come along and you need to re-import again. Also EF’s database import uses set property names and exposes every relationship, which isn’t ideal for good programming practice.

So I wanted to use EF Code First approach yet define the database through T-SQL. That meant I needed something to check that my EF/software view of the database schema and the actual SQL database schema were in step. Ideally this would be something I could run as part on my Unit Tests so that any mismatch shows up immediately.

My solution to comparing EF/SQL databases

I therefore created some software that compared the EF database model metadata against the actual SQL schema as read from a database. This method runs as a Unit Test and reads the EF database model metadata and reads the actual SQL database schema. It then compares the following:

  • Each table: does a table exist for each EF class?
  • Each column: does a column match EF class properties in name, type/nullable, size, primary key(s) and key order?
  • Each relationship:
    • Do SQL foreign key constraints exist for each EF relationship?
    • Have the extra many-to-many tables that EF used been configured properly?
    • Are the Cascade Deletes the same between EF and SQL?

If any of the above is out of step then the Unit Test fails and outputs useful error messages. At the same time it also produces warnings for tables and/or columns in the SQL database that EF does not use. These should not cause a problem to EF, but might show something that EF has missed.

Here is an example of a call to CompareEfWithDb to check that the EF’s model matches the schema of the database that the ‘YourDbContext’ is connection string points to:

using (var db = new YourDbContext())
{
    var comparer = new CompareEfSql();

    var status = comparer.CompareEfWithDb(db); 
    //status.IsValid is true if no errors. 
    //status.Errors contains any errors.  
    //status.Warnings contains any warnings
}

In my Unit Test I fail the test if status returned is not valid and I print out any error messages. I also tend to fail the test on warnings too, as it often points to something I have missed.

Another slightly different method has an extra parameter with a connection string to a separate database. This allows me to check a different database schema, e.g. my production database, against the current EF model. By running this as an extended Unit Test before deployment I make sure that I know if the production database needs to be updates before the new software is deployed. This gives me great peace of mind that my SQL databases are in line with the current EF data classes.

Here are two examples of the type of error output I get when running the CompareEfWithDb method. The first is a simple example is of a missing column in the database. The error message from calling the CompareEfWithDb method is:

Missing Column: the SQL table [dbo].[Child] does not contain a column called EfOnlyProperty. Needed by EF class Child.

The second example is a class called ‘Parent’ which has many-to-many relationship with a class called ‘Child’ and the linking table is missing. The error messages from calling the CompareEfWithDb method are:

  1. Missing Link Table: EF has a Many-to-Many relationship between Parent.ManyChildren and Child but we could not find a linking table with the right foreign keys.
  2. Missing Link Table: EF has a Many-to-Many relationship between Child.ManyParents and Parent but we could not find a linking table with the right foreign keys.

Add CompareSqlToSql for a fuller picture

I found the CompareEfWithDb method very helpful, but it doesn’t quite cover everything. At time goes on I am planning to move some of the more complex access to SQL Stored Procedures (SPs) to both gain better performance and facilitate the decoupling of the database from the software. EF can use SPs but they don’t appear in EF database model, so CompareEfWithDb couldn’t help.

Having created all the code for CompareEfWithDb it was pretty simple to create a SQL to SQL compare, called CompareSqlToSql. This could check one database against another, and can include a comparison of other SQL features. It does a slightly fuller comparison that the EF/SQL does as we can compare everything rather than surmising some things, like many-to-many table settings, from EF. It can also check non-EF parts like SPs and their parameters.

Note: You still need good integration and system level testing to catch anything that these tools miss.

Using SQL compare to understand what EF is doing

I should say that CompareSqlToSql has proved to be much more useful than just checking SPs. It turns out that EF is somewhat forgiving if you make mistakes when you use EF’s Data Annotations or EF’s Fluent API to alter the way the database works. For instance if you configure a required to optional relationship but give it a non-nullable foreign key it will add its own hidden nullable foreign key. In fact I found about three errors like this in my 16-table application, all around relationships.

If you want to create a database that the EF database can use you need to fix these errors in your code, or at least replicate what EF has done for the database to work. CompareEfWithDb won’t spot them for you, but comparing an EF-generated database with your script-generated database will find them. To do this you need to:

1. Create a new database using the DbContext method .Database.Create() then you have a SQL database based on EF’s database model. We will call it MyEfDb and I give the code below:

using (var db = new YourDbContext(MyEfDbConnectionString))
{
    if (db.Database.Exists())
    {
        db.Database.Delete();
    }
    db.Database.Create();
}

2. Then create a database using your scripts and DbUp (see first article). We will call it MySqlDb.

3. Now run CompareSqlToSql (or another compare scheme tool – see below) with MyEfDb as the reference database and MySqlDb as the ‘to be checked’ database.

You can spot EF corrections to your minor configuration mistakes mainly by the column names, which normally contain the name with a _ in the middle. I have also found places where the Cascade delete option was different, again through me misconfiguring a relationship.

Using a SQL compare to build your scripts

The other reason for doing a SQL compare of an EF-generated database with your current script-generated database is to build the scripts you need to update your database after you have made changes to the EF classes. There are various tools that can compare two SQL databases and provide a script to update one to the other – see this useful list on stackoverflow, although it is a bit old. Note: some of these are commercial products that you need to buy.

If you don’t have one of these then the output of CompareSqlToSql will show all the SQL differences, but not in a nice SQL script way. For instance if we repeat the many-to-many example above, with a class called ‘Parent’ which has many-to-many relationship with a class called ‘Child’ and the linking table is missing. The error messages from CompareSqlSql are:

  1. Missing Table: The ‘MyEfDb’ SQL database has a table called [dbo].[ParentChild], which is missing in the MySqlDb’ database.
  2. Missing Foreign key: The ‘MyEfDb’ SQL database has a foreign key Parent: ParentChild.Parent_ParentId, Referenced: Parent.ParentId, which is missing in the ‘MySqlDb’ database.
  3. Missing Foreign key: The ‘MyEfDb’ SQL database has a foreign key Parent: ParentChild.Child_ChildId, Referenced: Child.ChildId, which is missing in the ‘MySqlDb’ database.

Note: If anyone is interested in the CompareEfWithDb and CompareSqlToSql Unit Test methods I could make the package publicly available. However the first version was a massive 2-day hack and does not handle all possible EF combinations, such as TPT and TPH inheritance, complex types etc. It therefore needs a lot more work before it can be released for general use.

Telling Entity Framework that you will handle migrations

The last thing we need to do is stop EF handling database changes when you change your EF code. If you don’t turn this off then when you change EF database classes EF will block you from running the application until it has updated the database, using whatever EF ‘Database initializers’ is set up (EF’s default initializer is CreateDatabaseIfNotExists) .

To stop EF trying to handle migrations we have to do is provide a null database Initializer. There are two ways of doing this (you only need one):

  1. Call ‘SetInitializer<YourDbContext>(null)’ at startup
  2. Add the following to the <appSettings> part of your web/application config file, e.g.
<appSettings>
    <add key="DatabaseInitializerForType YourApp.YourDbContext, YourApp" value="Disabled" />
</appSettings>

I personally add the appSetting to my config file as that way I know it is done.

Note: See this useful documentation for more on null database Initializers.

Conclusion

Well done for reading this quite long article. In this post I have described how EF keeps its model view in line with the actual database schema. From this we see it does not read the schema other than in one very limited situation of importing an existing database. I also describe what happens if EF’s database model and the actual database are out of step.

I then go on to describe a Unit Test tool that I have developed to check if EF’s database model is the same as the actual SQL model. However you can use the technique in the section ‘Using SQL compare to build the right update scripts’ to create two database and then use some sort of SQL compare tool to get the difference script.

Note: If you are interested in using the CompareEfWithDb and CompareSqlToSql Unit Test methods then let me know. I won’t be able to get a release out of this package for some time as it needs a lot of extra work to support all EF’s features and I am busy working on another project.

Finally I describe how to stop EF from trying to look after changes in the database model, which you need to do if you are taking over the job of handling database migrations.

Happy coding!

Postscript

I am now using this approach on my existing e-commerce project and I needed to add my first change the the database schema. The change was simple, adding two bytes to an existing table, which is non-breaking change to the database. It is worth comparing the new process with the old process.

Previously I was using EF data migrations such a change used to take me 30 minutes+. Having made the change to the data classes I needed to check those changes by updating/recreating the Unit Test database. This I had to do in a different way to the local/azure databases because the DatabaseInitializer was different, which required me to editing out some code, deleting the Unit Test database, and then resorting some code. Also, because the way I updated the Unit Test was different to the local/production databases I needed to check everything again when I undated the local/azure databases .

In comparison using the process laid out in these two articles it took me less than 15 minutes to make the changes on all three databases: Unit Test database, local developer database and Azure. I use the similar method to update all three database, but in the case of the Unit Test I also do a complete delete/setup to ensure there isn’t a problem later. However once I had changed the Unit Test database and ran CompareEfWithDb and CompareSqlToSql I was very sure that the other two updates would work. Much less stressful.

I also found it more natural to define the alter table in T-SQL. Maybe its just my preference, but you do get Intellisence when writing the T-SQL script in Visual Studio.

Nice to see it working for real.

 

Handling Entity Framework database migrations in production – part 1, applying the updates

Last Updated: April 7, 2016 | Created: November 4, 2015

This is the first article in a series about using Microsoft’s data access technology, Entity Framework (EF) in an application that to be ‘always on’, in my case an ASP.NET MVC e-commerce web site, yet is still being developed so there is a need to update the database schema, i.e. the tables, views, constraints etc. that define how the data is held in the database.

This is a series, and in this first article I deal with the issue of updating database schema in a robust and testable way. The list of articles will be:

However before detail my update method I will start by describing the general problems around a database update.

Why production database updates are so challenging

Databases contain date, and that data is often very valuable to someone. In the e-commerce site I am working on the database holds customer orders which they have paid for and they expect them to be delivered. If we want the business to succeed that data better not be lost or corrupted.

The problem is that as the business grows we are likely to need to change the type/format of data we hold in the database, known as the database schema. Simple changes like adding a column to hold say a comment is normally easy to add, as the new table will work with the existing software. However more complex additions of say a new table which links to existing data, i.e. in SQL terms it has a foreign key, are a lot more difficult to do.

Updates also become especially challenging if you want the existing web site to stay ‘live’ while you change the database, i.e. the old software still works with the new database while you are updating its schema and data.

In my case the application is an e-commerce web site using ASP.NET MVC with EF and is running on Microsoft Azure. In the diagram below we see a typical ‘live’ deployment via Azure’s staging slot.

Azure staging-live diagram

The point about this arrangement is that the staging slot holds the new version of the software, which is accessing the same database as the live site. When the new version checks out the DevOps person swaps the staging-live site. The users should see no interruption to their use of the site.

The challenge is if the new software needs a change to the database schema the database must be updated while the existing version of the software is still running. Also the database must support both the old and the new software until the swap is done. That is quite a challenge.

Note: You can read more about the challenges of deploying and updating databases, and the Simple-Talk web site has a whole section on Database Lifecycle Management.

My requirements for the database deployment process

After a lot of research I came up with a list of requirements a system that deployed a database. I decided the process must be:

  • Testable: I can test any database change before running it on the Production database.
  • Automated: I can automate the whole process so that I can’t get it wrong.
  • Trackable: Each database should have a log of what has been done to its schema.
  • Atomic: The update process must either be completed successful or entirely rolled-back.
  • Recoverable: Each update should automatically make a backup in case the worst happens.
  • EF-compatible: The database changes have to work with Entity Framework.
  • Comprehensive: There must be Full SQL access; I needed to change tables, views, stored procedures, constraints etc.

Why I decided EF’s standard migration feature was not sufficient

I am very familiar with EF’ database migration feature and I have used it in the early stages of developing an application. However, it’s this knowledge that made me decide it wasn’t sufficient for this current project. The primary reason is because it is not very testable, i.e. it is hard to run the update on a test database. A secondary reason is that I had come across problems when applying migrations to a Production system – the update runs as part of the application start-up and feedback of errors is very limited. There is a way to run EF migrations from a command line, which does improve the process, but the problem of testability still remains.

All in all I just didn’t feel comfortable with using EF’s own database migration feature, so I set out to find a better way. After a lot of searching I found a package called DbUp.

Note: I talk more about how EF matches its view of the database with the actual schema of the database in the second article.

Database Migrations the DbUp way

DbUp is an open-source, .NET-based tool for running scripts on a database. DbUp is elegantly simple, and uses an “Apply These Scripts…” approach, i.e. you write a list of scripts, SQL or C# based, with a name that sets the order, e.g. Script001, Script002 etc. You can call DbUp via a Console App or directly from PowerShell. It uses a special table in the database to see which scripts have been run and runs any scripts that are new.

Here is an example of using DbUp from my application:

var upgrader = DeployChanges.To
        .SqlDatabase(dbConnectionString)
        .WithScriptsAndCodeEmbeddedInAssembly(Assembly.GetExecutingAssembly())
        .WithTransaction()
        .LogToConsole()
        .Build();

var result = upgrader.PerformUpgrade();

if (result.Successful)
etc.…

Looks simple, but there are lots of little things in DbUp that shows someone has really thought the process through. I have to admit I found the documentation a little obscure in places, but that is because I was most likely thinking in an EF way. Once I got me head around it I started to appreciate the quality of DbUp.

A snippet from one of my test T-SQL scripts looks like this:

create table $schema$.[DataTop](
	[DataTopId] [int] IDENTITY(1,1) PRIMARY KEY,
	[MyString] [nvarchar](25) NULL,
	[DataSingletonId] [int] NULL,
)
go

create table $schema$.[DataChild](
	[DataChildId] [int] IDENTITY(1,1)PRIMARY KEY,
	[MyInt] [int] NOT NULL,
	[MyString] [varchar](max) NULL,
	[DataTopId] [int] NOT NULL
)
Go
ALTER TABLE $schema$.[DataChild]  
ADD  CONSTRAINT [FK_dbo.DataChild_dbo.DataTop_DataTopId] FOREIGN KEY([DataTopId])
REFERENCES $schema$.[DataTop] ([DataTopId])
ON DELETE CASCADE
GO

A couple of pointers for people that are used to EF Migrations:

  1. DbUp only does forward changes, i.e. there is no DOWN script like in EF migrations. The philosophy of DbUp is that at each stage you are transitioning the database to the new state. So going back is simply another transition.
    Considering that I have only used an EF DOWN script about once in four years then I don’t think this is a problem.
  2. There is no ‘Run Seed Every Time’ approach like EF. When you think about it you run one seed at the start why do you need to run it again? In DbUp if you want to change the data in the seed you just have a new script to update or add to the original seed.

Note: DbUp does have a way of running a script every time if you what to, see DbUp NullJournal.

One down side of using SQL scripts to apply database changes is that, unless the project has a DBA (Database Administrator), the developer will need to learn T-SQL. While I am not a DBA I have written quite a bit of T-SQL in my time. In fact I personally prefer T-SQL for configuring a database as I find using EF’s fluent interface can be quite difficult in some situations.

Conclusion

In this first article I have described the issue I have with EF’s own database migration code and shown and alternative method of applying updates to the schema of a SQL database using DbUp. However, on its own this will not work, as EF will not know you have changed things.

In the second article I will look at a method I have created to make sure that the view of the database schema that EF has does not diverge from the actual database schema.

Happy coding!

When is it not worth writing a Unit Test?

Last Updated: October 1, 2015 | Created: October 1, 2015

I’m currently working on a e-commerce site based on ASP.NET MVC 5 for a new venture (in stealth mode, so I can’t say what it is). I have been working on it for over six months and we set a target date to get a new Beta2 version of the site up by end of September (yesterday).

That puts a bit of pressure on development and I had a couple of interesting decisions around Unit Testing that I thought I would share.

I also include some quotes from one of the giants of Software Design, Martin Fowler, and at the end I give some criteria to help you decide what to test, and maybe what you might not test.

Setting the scene

I expect everybody who comes to this article will know about Unit Testing (here is link to a definition in case you are new to this). I also expect you have an opinion, ranging from “you need to have 100% test coverage” through to “Unit Testing is a waste of valuable development time”.

All my articles are based on my real-life experiences. In this one I found, just before the deadline, that a small piece of code took maybe 10 minutes to write and 1+ hours to write the Unit Tests. On the other had I realised two days earlier I had written another piece of code which took about the same time to write, but I didn’t write any Unit Tests for it.

On reflection I decided that both decisions were correct. In this article I go through my reasoning to see why I think this way. You may disagree with my reasons (please do!), but hopefully I will make you think.

Before I start let me give you my first quote from Martin Fowler. If you read my history you will see I came back to programming in 2009 after a 21 year break. I gobbled up all the reading I could find, and Martin Fowler was a great help to put me on a better track.

It is better to write and run incomplete tests than not to run complete tests.

Martin Fowler in the book ‘Refactoring’

1. Code I tested

Below is the code I wrote in about 10 minutes, which I then took over an hour to test. To save you reading it the name of the method says what it does, i.e. if the justification of a field is changed, say from Left to Centre then it changes the x position to match.

private void HandleChangeOfJustifyAndCompensatePosition(
    FieldJson fieldJson, CrudAdvancedStylesDto dto)
{
    if (fieldJson.styleInfo.justify != dto.Justify)
    {
        //The justification has changed, so we compensate the position

        if (fieldJson.styleInfo.rotation != 0 || dto.Rotation != 0)
            throw new NotImplementedException(
            "HandleChangeOfJustifyAndCompensatePosition does not currently support rotation.");
        if (fieldJson.styleInfo.shear != 0 || dto.Shear != 0)
            throw new NotImplementedException(
            "HandleChangeOfJustifyAndCompensatePosition does not currently support shear.");

        var leftX = 0.0;
        switch (fieldJson.styleInfo.justify)
        {
            case JustifyOptions.Left:
                leftX = fieldJson.xPosMm;
                break;
            case JustifyOptions.Center:
                leftX = fieldJson.xPosMm - (fieldJson.widthmm/2);
                break;
            case JustifyOptions.Right:
                leftX = fieldJson.xPosMm - fieldJson.widthmm;
                break;
            default:
                throw new ArgumentOutOfRangeException();
        }

        switch (dto.Justify)
        {
            case JustifyOptions.Left:
                fieldJson.xPosMm = leftX;
                break;
            case JustifyOptions.Center:
                fieldJson.xPosMm = leftX + (fieldJson.widthmm / 2);
                break;
            case JustifyOptions.Right:
                fieldJson.xPosMm = leftX + fieldJson.widthmm;
                break;
            default:
                throw new ArgumentOutOfRangeException();
        }
    }
}

Now, you might ask, why did it take me so long to write a test for such a small method. Well, it is a private method so I can only get to it via the business logic that uses it (Note: I really don’t want to change the natural design the the software to make this method easier to test. I have found that leads to all sorts of other problems).

So, why did the tests take so long? Here is a quick summary:

  • The new method added to an existing existing piece of business logic that already had quite a few tests, so it wasn’t a green field site.
  • The previous tests needed a significant amount of set-up, especially of the database, to  run. These needed changing.
  • Because the original tests had been written some time ago they didn’t use the new database mock I had developed, which would help with the new tests. I therefore decided to move all the tests in this group over to the mock database.
  • The Mock database didn’t have some of the features I needed so I had to add them.
  • … I could go on, but I am sure you get the picture.

This leads me onto a key point.

I find 80%+ of the work of writing Unit Tests in about getting the right set-up to allow you to exercise the feature you want to test.

So let me explore that.

Set up of the test environment

In any significant software development there is normally some sort of data hierarchy that can be quite complex. Often to test say one bit of business logic you need to set up other parts of the system that the business logic relies on. When this is stored on a database as well then it makes setting up the right environment really complex.

This might sound wrong for Unit Testing, which is about isolating sections of code and just testing that, but I find in the real world the lines are blurred. In the example code about I really don’t want to make a separate class, which I inject at runtime, just to isolate that method for testing. IMHO that leads to bad design.

Over the years I have tried lots of approaches and here is what I do:

1. Write ‘Set-up Helpers’ to create data for Unit Tests.

My test code is has a Helpers directory with a range of Helper classes (27 so far in this six-month+ project) which create data suitable for testing specific parts of the system. In the past I skimped on these, but I soon learnt that was counter-productive.

Most build the data by hand, but a few load it from json or csv files held in the Test project.

2. Use mocks where you can.

Mocks, or fakes, are pieces of code that replace a more complex subsystem or class. Mostly they replace a system library, but sometimes replace some of your own code so you can trigger thing that the normal code would rarely do, i.e. error conditions.

Mocks/fakes are great and I used them as much as I can. They can provide really good control of what goes on for a small about of work. I personally use Moq, which I find good.

However there are some limits to replicate all the feature of a big system like the Entity Framework ORM (EF), or some of the MVC Controller systems. I do have a mock for Entity Framework, which is pretty good, but it doesn’t do relational fix-up and current doesn’t support .Include().

2. Decide on how you will use a real database

I have found out the hard way that I need to use a real database for testing. There is down side to that of testing taking a lot more time to start (EF6 has a fairly long start-up time). However I have found, up to now (see comment at end of this section), the only way to truly check some code out.

There are a number of ways you can go, from clearing and reloading the database for every test up to just adding to the database. I have found clearing and reloading pretty difficult/time consuming, especially with complex foreign keys, so I use unique strings, via GUIDs, in strategic places to create a unique set of entries for a test.

Whatever way you go you need to think through a system and keep to it, otherwise thinks get messy.

Possible future options on EF?

  • Two separates readers of this blog have recommended Effort Library, which provides an in-memory EF database. That sounds like a great idea, but once I am in a project I try not to make big changes to the system so I haven’t tried it yet, although EF 7 may be better (see next).
  • Entity Framework 7 will have an in-memory database, which sound perfect for Unit Testing. I definitely plan to look at that.

Another Martin Fowler quote:

I’ve often read books on testing, and my reaction has been to shy away from the mountain of stuff I have to do to test. This is counterproductive…

Martin Fowler in the book ‘Refactoring’

The Unit Test framework has a BIG effect

I have found that the Unit Test framework is crucial. I started using NUnit many years ago and found it great, especially when run from inside Visual Studio via Resharper. However, when I started testing JavaScript I found other Unit Testing frameworks had some really useful features.

I will only summarise my thoughts on this because I wrote a long article called Reflections on Unit Testing in C# and JavaScript, which gives more detail. However let me give you the places where NUnit skews my Unit Testing approach.

NUnit does not have nested set-up calls

As I said earlier I find most of the work goes into setting things up to run a test. NUnit has a FixtureSetup/FixtureTearDown, which is run once at the start/end of a group of tests in a class, and a Setup/TearDown that is run before/after each test. These are useful, but not sufficient.

JavaScript Unit Test frameworks, like like Mocha and Jasmine , have a BeforeEach and AfterEach, which can be nested. Now why is that useful? I find that for some of the more complex methods to test I want to check multiple things when it is finished, e.g. was it successful, did it return the right data and did it change the database in the way I expected.

In Mocha and Jasmine I would have a group of tests with maybe an outer BeforeEach that sets up the environment and then an inner BeforeEach which calls the method, followed by separate tests for each of the parts I want to check. This means that a) the setup code is in one place, b) the call to the item under test is in one place and c) the tests are separately shown.

In NUnit I tend to find I either duplicate code or test lots of things in one test. (Note: If this doesn’t make sense then there is an example of nested set-ups in my article Reflections on Unit Testing in C# and JavaScript).

Possible future options:

One of my readers,

NUnit does not support async Setup/TearDowns

.NET 4.5’s async/await is a big change – see my full article on async/await for more. I find I use async/await in 90%+ of my  database calls and maybe 80% of my business logic. NUnit supports async tests, but not the FixtureSetup/FixtureTearDown or Setup/TearDown. That causes me to have to put more setup code in the actual tests.

2. The code I didn’t test

We have been off on a journey into why testing can be difficult. Now I’m back to the decision whether or not to Unit Test. Below is a fairly simple piece of business logic. It most likely took a bit longer than the first bit of code, as there was a DTO class to set up an the normal interface plumbing. What is does is find a customer order connected to something called a FOL.

public class GetFilledOutLabelLinksAsync : IGetFilledOutLabelLinksAsync
{
    private readonly IGenericActionsDbContext _db;
    private readonly IGenericLogger _logger;

    public GetFilledOutLabelLinksAsync(IGenericActionsDbContext db)
    {
        _db = db;
        _logger = GenericLibsBaseConfig.GetLogger(GetType().Name);
    }

    /// <summary>
    /// This returns information on the FOL and any associated order
    /// </summary>
    /// <param name="folId"></param>
    /// <returns></returns>
    public async Task<ISuccessOrErrors<FolAssociatedData>>
         RunActionAsync (int folId)
    {
        var status = new SuccessOrErrors<FolAssociatedData>();

        var fol = await _db.Set<FilledOutLabel>()
              .SingleOrDefaultAsync(x => x.FilledOutLabelId == folId);
        if (fol == null)
        {
            //very odd, but we fail softly
            _logger.WarnFormat(
                 "Could not find the FOL of id {0}", folId);
            return status.AddSingleError(
                  "Could not find the Label");
        }

        //Note: of FOL should only be associated with one LabelOrder,
        //but we have written this in a 'safe' way
        var associatedOrder = await _db.Set<LabelOrder>()
            .Include(x => x.Order)
            .OrderByDescending(x => x.Order.DateCreatedUtc)
            .FirstOrDefaultAsync(x => x.FilledOutLabelGuid == fol.GroupBy);
            
        return status.SetSuccessWithResult(
             new FolAssociatedData(fol, associatedOrder), "Got data");
    }
}

So why didn’t I write a Unit Test for this, and more importantly why am I happy with that decision? Well let me start my list of decison points with another quote from Martin Fowler.

 The style I follow (for Unit Testing) is to look at all the things the class should do and test each one for any condition that might cause a class to fail. This is not the same as “test every public method”, which some programmers advocate.

Testing should be risk driven; remember, you are trying to find bugs now or in the future.

Martin Fowler in the book ‘Refactoring’

My Criteria for Testing/Not Testing

I don’t have any hard and fast rules for Unit Testing, but over the years I have found, sometimes the hard way, what works for me. Here are some guiding principals, starting with the most important, that I use to decide if I need to test something.

Will it fail silently?

If the code could ‘fail’ silently, i.e. it goes wrong but doesn’t throw an Exception, then it needs watching! Any method that does maths, manipulates strings etc. can get the wrong answer and you need to check it, as it could cause problems and you don’t even know.

What is the cost of it going wrong?

It is a risk/reward thing. The more important/central the piece of code is to your application then the more you should consider Unit Testing it. (I can tell you that the payment side of my application is covered with Unit Tests!)

Will it change in the future?

If some code is likely to be changed/refactored in the future then adding Unit Tests makes sure it doesn’t get broken by accident. I love Unit Tests when it comes to Refactoring – I can change vast chunks of code and the Unit Tests will check I didn’t break it in the process.

Does it have lots of conditional paths?

Method with lots of conditional paths or loops are complex by nature. The logic could be wrong and its hard to test all the paths with functional testing. Unit Tests allow you to exercise each path and check that the logic is correct.

Does it have special error handling?

If the path through a method is only triggered on errors then Unit Testing may be the only way to check them out. If you added error handling then it must be important so maybe it should be Unit Tested.

I’m sure there are more, but I’ll stop there. You can always leave a comment and tell me what I have missed.

I should say there are some negative points, mainly around how hard it is to test. For me this isn’t the deciding factor, but it does play into the risk/reward part.

Final quote from Martin Fowler.

You get many benefits from testing even if you do a little testing. The key is to test the areas that you are most worried about going wrong. That way you get the most benefit for your testing effort.

Martin Fowler in the book ‘Refractoring’

Conclusion

So, if you look at the criteria and look back at the two pieces of code you will see that the code I tested a) could fail silently, b) will change in the future (see the NotImplementedExceptions), and c) had lots of conditional paths. It was hard work writing the tests but it was a no-brainer decision. (Note: I did find one bug around the tests for the not implemented ‘rotate’ and ‘shear’ checks).

The second piece of code wouldn’t fail silently, is unlikely to change and has one error path and one success path. You might also note the defensive style of coding as this is about a potential order a customer wants to make, so I don’t want it going wrong. Time will tell if that was a good decision or not, but I am happy for now.

Please feel feed to add your own comments.

GenericServices Masterclass: a deeper look at deleting entries

Last Updated: June 24, 2015 | Created: June 23, 2015

I have been building a new application using GenericServices and I thought it would be useful to others to reflect on the approaches I have used when working with more complex data. In this second master class article I am looking at deleting entries, i.e. data rows, in a SQL Server database using Entity Framework. The master class first article looked at creating and updating entries and can be found here.

I wrote this article because the more I used GenericServices IDeleteService in real applications I ended up using the more complex DeleteWithRelationships version of the command. This article says why and suggests some ways of tackling things, including not deleting at all!

What is GenericServices??

For those of you who are not familiar with GenericServices it is an open-source .NET library designed to simplify the interface between an ASP.NET MVC application and a database handled by Microsoft’s Entity Framework (EF) data access technology. You can read more about it at https://github.com/JonPSmith/GenericServices where the Readme file has links to two example web sites. The GenericServices’ Wiki also has lots more information about the library.

While this article talks about GenericServices I think anyone who uses EF might find it useful as it deals with the basics behind deleting entities.

An introduction to relationships and deletion

Note: If you already understand the different types of referential relations in a relational database and how they affect deletion then you can skip this part and go to the section with title Deleting with GenericServices bookmark.

On the face of it deleting rows of data, known in EF at entities, seems like a simple action. In practice in relational databases like SQL Server it can have far reaching consequences, some of which might not be obvious at first glance when using EF. Let me give you a simple example to show some different aspects to delete.

Below is a database diagram of the data used in the example application SampleMvcWebApp, which contains:

  1. A selection of Tags, e.g. Programming, My Opinion, Entertainment, etc.  that can be applied to a post.
  2. A list of Posts, where each is a article on some specific topic.
  3. A list of Bloggers, which are the authors of each of the Posts.

tagpostblog

Now this example shows the two most used type of relationships we can have in a relational database. They are:

  • One-to-many (1-*): A Post has one Blogger, and a Blogger can have from zero to many Posts.
  • Many-to-many (*-*). A Post can have zero to many Tags, and Tags may be used in zero to many Posts.

The other type of basic relationship is a one-to-one relationship. This is where one entity/table is connected to another. You don’t see this type of relationship so much as if both ends are required (see below) then they can be combined. However you do see one-on-one relationships where one end is optional.

So the other aspect to the ‘One’ part of a relationship is whether it is required or optional (required: 1, optional: 0..1). An example of a required relationship is that the Post must have a Blogger, as that defines the author. An example of an optional relationship would allowing the Blogger to add an optional ‘more about the author’ entry in another table. The Author can choose to set up that data, or not bother.

How EF models these relationships

EF has multiple ways of setting up relationships and I would refer you to their documentation. Here is my super simple explanation to help you relate the section above to Entity Framework classes:

  • The ‘One’ end of a relationship has a property to hold the key (or multiple properties if a composite key).
    • If the key property(ies) is/are nullable, e.g. int? or string without [Required] attribute, then the relationship is optional.
    • If the key property(ies) is/are not nullable, e.g. int or string with [Required] attribute, then the relationship is required. See the BlogId property in the Post class of SampleMvcWebApp.
  • The ‘Many’ end of a relationship is represented by a collection, normally ICollection<T> where T is the class of the other end of the relationship. See the Posts property in the Blog class of SampleMvcWebApp.
  • Many-to-Many relationships have Collections at each end, see the Tags property in the Post class and the Posts property in the Tag Class of SampleMvcWebApp.
    EF is very clever on many-to-many relationships and automatically creates a new table that links the two classes. See my article Updating a many to many relationship in entity framework for a detailed look at how that works.

How these relationships affect Deletion?

If you delete something in a relational database that has some sort of relationship then it is going to affect the other parts of the relationship.  Sometimes the consequences are so small that they don’t cause a problem. However, especially in one-to-one or one-to-many relationships, the effect of a delete does have consequences that you need to think about. Again, let me give you two examples you can actually try yourself on the SampleMvcWebApp web site.

  1. Delete a Many-to-Many relationship. If you go to the Tags Page of SampleMvcWebApp and delete a Tag then when you look at the list of Posts then you will see that that tag has been removed from all Posts that used it (Now press Reset Blogs Data to get it back).
  2. Delete a One-to-Many. However if we go to the list of Bloggers on SampleMvcWebApp and delete one of the Bloggers then when you look at the list of Posts you will see that all Posts by that author have gone. (Now press Reset Blogs Data to get them all back).

So, what has happened on the second one? Well, in this case the database could not keep the relationship without doing something because the Post’s Blog link wasn’t optional. There were two possibilities: either it  could delete all the Post for that Blogger or it could have refused to carry out the delete.

By default EF sets up what is called ‘cascade deletes‘, which is how SampleMvcWebApp is set up. In this case is what deleted the related Posts for that Blogger. If we turned off cascade deletes then the we would get a ‘DELETE statement conflicted with COLUMN REFERENCE’ (547) and the delete would fail.

The simplified rule is if entity A is involved in a required relationship with entity B then when A is deleted something has to happen: either B is deleted or the delete fails. Which happens depends on how you configure EF.

Bookmark

Deleting with GenericServices

GenericServices has a service called IDeleteService which has two delete methods:

  1. Delete<T>(key1, key2…) (sync and async) which deletes the row with the given key, or keys if it has a composite key, from the EF entity referred to by class T, e.g. Delete<Post>(1) would delete the Post with the key of 1.
  2. DeleteWithRelationships<T>(removeRelationshipsFunc, key1, key2…) (sync and async) which does the the same, but called the removeRelationshipsFunc as part of the delete.

I am not going to detail how to use them as the GenericServices Wiki has a good description.You can also find an example of the use of Delete<T> at line 121 in PostsController and an example of the use of DeleteWithRelationships<T> at line 218 in CustomerController.

The only other thing I would say is that deleting entries with composite keys is straightforward – just supply the keys in the order in which they occur in the database. Note: some programmer don’t like composite keys, but I do find them useful. There are places where composite keys are good at segregating data into groups: the primary key can be the group name, the secondary key is the name of the item itself.


When I first wrote GenericServices I just had a Delete<T> method. I very soon found that wasn’t enough, so I added DeleteWithRelationships<T>. Now I find I am using DeleteWithRelationships 80% of the time.

The rest of the article is about why I use DeleteWithRelationships so much, and a few pointers on alternatives to using Delete at all.

Why I use DeleteWithRelationships so much

I have used DeleteWithRelationships in three situations that I will describe:

  1. To provide better error messages when a delete would fail.
  2. To delete other associated entities that would not be caught be cascade deletes.
  3. To delete associated files etc. outside the relational database.

1. To provide better error messages when a delete would fail

I have many instances where I don’t want cascade deletes to work, so if I deleted I would get a SQL error 547. While GenericServices catches this and provides a fairly helpful error it isn’t that informative. I therefore often (actually, nearly always) use DeleteWithRelationships to provide a better error message. Let me give you an example.

In a web site I was building designers could set up designs with text fields. Each field had a ColourSpec, which is a database entity. I allowed a designer to delete a ColourSpec as long as that colour isn’t used in any of the text fields. If it is used then I output a list of designs where it is used so that the designer can decide if they want to remove those reference and try the delete again.

2. To delete other associated entities that would not be caught be cascade deletes

This happens rarely, but sometimes I have a complex relationship that needs more work. I found one in the AdvertureWorks database that I use in my example application complex.samplemvcwebapp.net. In this the customer address consists of two parts: the CustomerAddress with has a relationship to an Address. Now, if we want to delete one of the customer’s  addresses, say because they have closed that office, we want to try and delete the associated Address part, which isn’t linked by cascade deletes.

By using DeleteWithRelationships I can pick up the associated Address relationship and delete that too. In fact I might need to do a bit more work to check if I can delete it as it might be references in an old order. By calling a method which I can write specifically for this case then I insert special logic into the delete service.

NOTE: because the delete is done by GenericServices and any extra delete done in the DeleteWithRelationships method are all executed in one commit. That means if either part of the delete fails then both are rolled bakc, which is exactly what you need.

3. To delete associated files etc. outside the relational database

In a recent web application the system included image files. I chose to store then in the Azure blob storage, which I have to say works extremely well. The database stored all the information about the image, include a url to access the image from the blob, while the images, which can be very large were stored in a blob table.

Now, when I delete the database entity about the image I will end up with an orphan image in the blob storage. I could have a WebJob that runs in the background to delete orphan images, but that is complicated. What I did do was add code to the DeleteWithRelationships to delete the images as part of the delete process.

There is obviously a danger here. Because the database and the blob are not connected then there is no combined ‘commit’, i.e. I might delete the images and the SQL database delete might then fail. Then my url links don’t point to a valid image. In this case I have made a pragmatic choice: I check that the delete should work by checking all the SQL relationships before I delete the blob images. I could get a failure at the SQL end which would make things go wrong, but other parts of the system are designed to be resilient to not getting an image so I accept that small possibility for a simpler system.  Note that if I fail to delete the images from the blob I don’t stop the SQL delete – I just end up with orphan images.

An alternative to Delete

In working web applications I find it is sometimes better not to delete important entities, especially if I want to keep a history of orders or usage. In these cases, which happen a lot in live systems, I am now adding an enum ‘Status’ to my key entities which has an ‘Archive’ setting. So, instead of deleting something I set the Status to Archive.

All I have to do to make this useful is filter which entities are shown based on this Status flag. That way entries with a Status setting of ‘Archive’ are not shown on the normal displays, but can be seen in audit trails or admin screens.

I needed a Status flag anyway, so there is little extra overhead to providing this on my key entities. Also, in EF 6 you can now set a property to have a index, so the query is fairly quick. You need to think about your application, but maybe this would work for you too.

Note: See the comment below by Anders Baumann, with a link to an article called ‘Don’t Delete – Just Don’t‘ for a well formed argument for not using delete.

Conclusion

Deleting looks so simple, but my experience is that it is often more complicated than you think. Hopefully this article gives you both the background on why and the detail on how to use GenericServices to delete entities. I hope its helpful.

Happy coding!

User impersonation in MVC using ASP.NET Identity 2

Last Updated: June 22, 2015 | Created: June 19, 2015
Quick Summary
This post is about how impersonate another user in a modern ASP.NET MVC web application that uses Identity 2. It describes a system that takes advantage of MVC 5’s new AuthenticationFilter to add or change the claims on a user so that they can ‘semi-impersonate’ another user without giving them too much access.

ASP.NET Identity 2 (referred to as Identity2 from now on) is a great system for handling site membership, i.e. who can log in, referred to as authentication, and what they are allowed to do, called authorisation (UK spelling, authorization in .NET code). Identity2 has a host of new features, such as allowing social logins, e.g. login with your Goggle or Facebook account, and it also works well with modern, scaled applications because it uses a cookie for holding session information.

The problem I had was that I wanted to allow a suitably authorised person to ‘impersonate’ another user, i.e. they could gain access to certain data that was normally read-only to anyone other than the author. Identity2 changes how you do that and I went hunt for solutions.

I found a great solution by Max Vasilyev, see his article User impersonation with ASP.Net Identity 2, which I nearly went with. However my need was slightly different to Max’s so I tackled it a different way, which I call ‘semi-Impersonation’ for reasons you will see later. Like Max having got something to work I thought I would also write a blog to share a solution.

My Use-Case

Before I describe the solution I wanted to say why I needed this impersonation method, then the code should make more sense.

I am working on a web application where graphic designers can set up designs for others to buy. They have their own ‘key’ which protects their designs from being changed by anyone other than them. The problem is, sometimes they need some help on setting up a design and its often easier for the site’s chief designer, called SuperDesigner from now on, to sort it out for them.

There are all sorts of ways for solving this from the downright dangerous (i.e. sharing passwords), to not that nice (i.e. allowing the SuperDesigner to have write access to all designs all of the time). In the end I decided to use a ‘semi-impersonate’ method, i.e. the SuperDesigner could just change their ‘key’ to the ‘key’ of the designer that needed help.

Technically the ‘key’ is a Claim, which is was Identity2 uses for authorisation.  If you are not familiar with claims they are a Key/Value pairs that each user has. Some are fixed and some you can add yourself.

My semi-impersonation solution

The solution uses a semi-impersonation approach, i.e. I changes a small part of the user information which allows access to the Designer’s work. The solution  consists of four main parts:

  1. A session cookie to control when we are in ‘impersonate’ mode.
  2. Use of MVC’s 5 new AuthenticationFilter OnAuthentication method which changes the claims on the current user.
  3. Some change to my application code that provides the ‘key’ for each designer.
  4. Feedback to the SuperDesigner that they are in ‘impersonate’ mode and a button to drop out of impersonation.

The big picture of the design is as follows:

  1. The SuperDesigner looks through a list of designers and clicks ‘Impersonate’ on the designer that needs help.
  2. That creates an Impersonate session cookie which holds the ‘key’ of that designer, encrypted of course!
  3. My Impersonate AuthenticationFilter is registered at startup and runs on every request. If it finds an Impersonate session cookie it adds a new, temporary Impersonate claim with the ‘key’ from the cookie to the current ClaimPrincipal.
  4. The code that provides the unique ‘key’ to each designer was changed to check for an Impersonate claim first, and if present it uses that key instead.
  5. The _LoginPartial.cshtml code is changed to visually show the SuperDesigner is in ‘impersonate’ mode and offers a ‘Revoke’ button, which deletes the cookie and hence returns to normal.

1. The Session Cookie

In MVC cookies are found my looking in HttpRequestBase Cookies collection, and added or deleted by adding the HttpResponseBase Cookies collection. I didn’t find the MSDN documentation on Cookies very useful, so here some code snippets to help. Note that in the code snippets the ‘_request’ variable holds the MVC Request property and ‘_response’ variable holds the MVC Response property.

Adding a Cookie

public HttpCookie AddCookie(string value)
{
    var cookie = new HttpCookie(ImpersonationCookieName, Encrypt(value));
    _response.Cookies.Add(cookie);
    return cookie;
}

This code adds a Cookie to the HttpResponseBase cookie collection. A few things to note:

  • Because I don’t set a expiry date then it is a Session Cookie, i.e. it only lasts until the browser is closed, or until I set a negative Expiry date. That way it can’t hang around.
  • I encrypt the value using the MachineKey.Protect method to keep it safe.
  • I have a setting in my Web.Config to set all cookies to HTTPOnly, and Secure when running on Production systems.

Cookie Exists and GetValue

public bool Exists()
{
    return _request.Cookies[ImpersonationCookieName] != null;
}

public string GetValue()
{
    var cookie = _request.Cookies[ImpersonationCookieName];
    return cookie == null ? null : Decrypt(cookie.Value);
}

Fairly obvious. Decrypt is the opposite of Encrypt

Deleting the Cookie

public void DeleteCookie()
{
    if (!Exists()) return;
    var cookie = new HttpCookie(ImpersonationCookieName) { Expires = DateTime.Now.AddYears(-1) };
    _response.Cookies.Add(cookie);
}

Deleting a cookie proved to be a bit troublesome and I found some misleading information on stackoverflow. The best article is the MSDN one here. Watch out that the delete cookies is new, don’t use the one from the request, and the new cookie must have exactly the same attributes as the original cookie. I had a .Path constraint on the added cookie and not on the delete cookie and it didn’t work.

2. AuthenticationFilter OnAuthentication

The AuthenticationFilter is a great addition that came in with MVC version 5. It allows the Current Principal, both Thread.CurrentPrincipal and HttpContext.User, to be changed. The key to doing this is the OnAuthentication method inside the AuthenticationFilter, which I have listed in full below:

/// <summary>
/// This looks for a Impersonation Cookie. If not found then it simply returns.
/// If found it changes the filterContext.Principal to include a
/// ImpersonateClaimType with the Designer Key value taken from the cookie.
/// </summary>
/// <param name="filterContext"></param>
public void OnAuthentication(AuthenticationContext filterContext)
{
    var impersonateCookie = new ImpersonationCookie(
        filterContext.HttpContext.Request,
        filterContext.HttpContext.Response);

    if (!impersonateCookie.Exists()) return;

    //There is an impersonate cookie, so we build a new
    //ClaimsPrincipal to replace the current Principal
    var existingCp = filterContext.Principal as ClaimsPrincipal;
    if (existingCp == null)
        throw new NullReferenceException("Should be claims principal");

    var claims = new List<Claim>();
    claims.AddRange(existingCp.Claims);
    claims.Add(new Claim(ImpersonateClaimType,
         impersonateCookie.GetValue()));

    filterContext.Principal = new ClaimsPrincipal(
         new ClaimsIdentity(claims, "ClaimAuthentication"));
}

Hopefully the code is fairly obvious. The main thing to know is if you change the filterContext.Principal then MVC will change the Thread.CurrPrincipal. too, so you change gets propagated throughout your application. So, by creating a new ClaimsPrincipal with all the original claims, plus the new ImpersonateClaimType then this can be picked up by calling Thread.CurrPrincipal by casting it to a ClaimsPrincipal.

Note that the ‘new ImpersonationCookie(…’ is just my wrapper round my Cookie code shown earlier.

3. Picking up the key

This bit of code is specific to my application, but worth showing for completeness:

public static string GetDesignerKey()
{
   var claimsPrincipal = Thread.CurrentPrincipal as ClaimsPrincipal;
   if (claimsPrincipal == null) return null;  //system failure

   var impersonateClaim = claimsPrincipal.Claims
       .SingleOrDefault(x => x.Type == ImpersonateClaimType);
   if (impersonateClaim != null) return impersonateClaim.Value;

   var designerKeyClaim = claimsPrincipal
       .Claims.SingleOrDefault(x => x.Type == DesignerKeyClaimType);
   if (designerKeyClaim != null) return designerKeyClaim.Value;

   return null;   //system failure
}

You may wonder why I did not just change the DesignerKeyClaimType? I could have, but then it would have been hard for other parts of the system to find out if we were in impersonate mode, as we will see in the next section.

4. Feedback to user and ‘Revoke impersonation’

The final part is the feedback to the user that they are in ‘impersonate’ mode and offer them a ‘Revoke impersonation’ button. This I did in a Html helper method I wrote which went into the ‘if (Request.IsAuthenticated)’ path of  _loginPartial.cshtml. This helper did the following:

  1. It looked for the ImpersonateClaimType. If not there then output normal html.
  2. If ImpersonateClaimType there then:
    1. Say we are in impersonation mode
    2. Offer a ‘Revoke’ button that calls an MVC action which deletes the Impersonation cookie.

Why/how is my implementation different to Max Vasilyev solution?

It is always good to compare solutions so you know why they are different. Here are my thoughts on that (Max, please do tell me anything else you would like added).

  • Max Vasilyev’s solution is great if you want to take over all the user’s roles and claims – you become the user in total. In my case I just wanted to change one attribute, the designer key, hence the name ‘semi-impersonate’.
  • If you just need to get impersonation going quickly then use Max’s solution – it just works. My method needs more design work as you need target specific attributes/features for it to work. Therefore it is only worth considering my method if you want more control on how the impersonation works, see the points below:
    • My method only adds/changes the specific claims that are needed, so limiting the power of the impersonator.
    • My method would would with different levels/types of impersonation by having different cookies. The OnAuthentication method could then obey different rules for each cookie type.
    • My method can use the cookie attributes like Domain and Path to limit what part of my site supports impersonation. In my application I use the .Path attribute to make impersonation only work on the Designer setup area, thus restricting the power of the SuperDesigner to say edit the designer’s details  or payments.

Note: Max makes a good point in his feedback that using .Path in that way ‘is not future- or refactoring- proof’, i.e. if the routing is changed by someone who does not know about the .PATH on the cookie then it could lead to errors that are hard to find at testing time. You may want to take his advice on that and use a more obvious authentication filter attribute that checks for the cookie.

Other, more minor differences are:

  • My method allows for audit trails to work properly as only the key is changed and the rest of the user identity is left alone. Also means the user who is impersonation, SuperDesigner in my case, keeps their enhanced Authorisation Roles.
  • My method will be very slightly slower than Max’s solution as it runs the OnAuthentication method on every HTTP request.
  • My method is limited to what information you can put in  a cookie (4093 bytes). Also because I encrypt my content it can take up a lot of room so you might only get four or five big strings in an encrypted cookie.

Conclusion

This article has described a way of one user gaining access to facilities of another user in a secure and controlled way. Hopefully I have given you enough code and links to see how it works. I have also pointed you to Max Vasilyev solution, which is very good but uses a different approach, so now you have two ways to impersonate another user.

Do have a look at both and take your choice based on what you need to do, but between Max’s solution and mine I hope they help you with your web application development.

Happy coding!