Bulk Insert and Entity Framework

Entity Framework (EF), as of v. 6, still has no method for dealing with bulk inserts. If you have, for instance, 750,000 records that need inserting then you will end up sending 750,000 insert statements to the database. In this instance you may also find yourself creating an enourmous object graph, too, so your application may not even get to the point of performing the required inserts without running out of memory.

Here’s a simple workaround. Take a copy of the part of your object model that’s going to cause the problem and then null it out. Perform the rest of the database insert using EF 6 and grab whatever identities you may need for the remaining objects’ foreign keys. Then perform a Bulk SQL Insert of the remaining data.

Here’s a cut-down entity relationship diagram for an example object hierarchy, in this case an investment fund that is a wrap product for further funds (a fund of funds). In the wrap there are multiple years in the forecast and in each year the percentage of the total sum invested varies across the funds. A single configuration contains multiples of these wraps. What’s important here is the hierarchy can grow to have a great many percentages in the final table and thus a great many inserts.

Entity Relationship diagram

The solution I’ve used relies on getting the DbContext from EF and using the underlying database connection to create Commands (plain old SQL) for the intermediary tables (Fund and Year). It then creates a DataTable for the final table, Percent, and uses SqlBulkCopy() with the context’s db connection and the underlying transaction (nice). The whole thing is wrapped in a try catch block that is within the context’s db transaction so any exceptions can cause a rollback.

You can follow along this quite simple workaround in this demo code. Note the use AutoDetectChangesEnabled = false and ValidateOnSaveEnabled = false both of which will improve performance without any other issues.

Ninject Configurer

What’s a nice way to set up configuration for add-in libraries for your project? They can’t have app.config, that has to be centralised in your web.config. So wrap up the config you’re going to need into a nice class and then pass that in when you need it.

If you’re using Ninject as your IoC you’ll want it to manage the dependency for you. Hey, while it’s at it, couldn’t it just fetch out those config values, too?

That’s what Ninject.Configurer does. It’s available as nuget package but that’s for .NET 3.5 so you may need to compile your own. I add a ‘Configuration’ folder to the class library and then just need three classes.

Windows Azure Cloud Storage using the Repository pattern

Repository pattern instead of an ORM but with added Unit of Work and Specification patterns

When querying Azure Tables you will usually use the .NET client to the RESTful interface. The .NET client provides a familiar ADO.NET syntax that is easy to use and works wonderfully with LINQ. To prevent the access code becoming scattered through your code you should be collecting it into some kind of DAL. You should also be thinking about testability of your code and the simplist way to provide this is to have interfaces to your data access code. Okay, so there’s nothing earth-shattering here but getting the patterns together and learning to use Azure Tables to their best is probably new to you or your project.


What do you want to provide to every object that needs a backing store? I’d suggest searching and saving so here are the two methods every repository is going to need.

public interface IRepository<TEntity> where TEntity : TableServiceEntity
  IEnumerable<TEntity> Find(params Specification<TEntity>[] specifications);

  void Save(TEntity item);


What about getting back a particular entity, making changes and saving that back? The first thing to note is that in Azure Tables an entity is stored in the properties of a Table row *but* other entities may also be stored in the same Table. So think entity and not table, which is different to how you would normally think of a repository.

Let’s say for this example I want to be able to get a single entity, a range of entities, to be able to delete a given entity and even to page through a range of entities.

To keep the code cleaner I’m going to pass in the parameters as already formed predicates for my where clause. There’s little advantage to using the Specification pattern here other than I think it makes the code a little more explicit.

public interface IEntityRepository : IRepository<Entity>
    void Delete(Entity item);

    Entity GetEntity(params Specification<Entity>[] specifications);

    IEnumerable<Entity> GetEntities(
        params Specification<Entity>[] specifications);

    IEnumerable<Entity> GetEntitiesPaged(
        string key, int pageIndex, int pageSize);


public class EntityRepository : RepositoryBase, IEntityRepository
    public EntityRepository(IUnitOfWork context) 
        : base(context, "table")

    public void Save(Entity entity)
        // Insert or Merge Entity aka Upsert (>= v.1.4).
        // In case we are already tracking the entity we must 
        // first detach for the Upsert to work.
        this.Context.AttachTo(this.Table, entity);

    public void Delete(Entity entity)

    public Entity GetEntity(
        params Specification<Entity>[] specifications)
        return this.Find(specifications).FirstOrDefault();

    public IEnumerable<Entity> GetEntities(
        params Specification<Entity>[] specifications)
        // new ByKeySpecification("partitionKey")
        return this.Find(specifications);

    public IEnumerable<Entity> GetEntitiesPaged(
        string partitionKey, int pageIndex, int pageSize)
        var results = this.Find(
            new ByPartitionKeySpecification("partitionKey"));

        return results.Skip(pageIndex * pageSize).Take(pageSize);

    public IEnumerable<Entity> Find(
        params Specification<Entity>[] specifications)
        IQueryable<Entity> query = 

        query = specifications.Aggregate(
            query, (current, spec) => 

        return query.ToArray();

It’s easy enough to pass in a context for your repository following the Unit of Work pattern. You can create this quite simply (see TableStorageContext following). You have to define which Table your entity is stored in and you want that and your context as properties of your class. I find it cleaner to manage (and easier for the next developer to implement) if that work is done in a base class, RepositoryBase.

public class RepositoryBase
    public RepositoryBase(IUnitOfWork context, string table)
        if (context == null)
            throw new ArgumentNullException("context");

        if (string.IsNullOrEmpty(table))
            throw new ArgumentNullException(
                "table", "Expected a table name.");

        this.Context = context as TableServiceContext;
        this.Table = table;

        // belt-and-braces code - 
        // ensure the table is there for the repository.
        if (this.Context != null)
            var cloudTableClient = 
                new CloudTableClient(

    protected TableServiceContext Context { get; private set; }

    protected string Table { get; private set; }

So now we actually get to the meat of the matter and implement our TableServiceContext methods for the CRUD functionality we need. In this example I’ve a single Save method that uses the ‘Upsert’ (InsertOrMerge) functionality available in Azure since v.1.4 (2011-08). The Find method is there for convience – if it doesn’t suit your query then simply don’t use it.


public class TableStorageContext : TableServiceContext, IUnitOfWork
    // Constructor allows for setting up a specific 
    // connection string (for testing).
    public TableStorageContext(string connectionString = null)
        : base(

    // NOTE: the implementation of Commit may vary depending on 
    // your desired table behaviour.
    public void Commit()
            // Insert or Merge Entity aka Upsert (>=v.1.4) uses 
            // SaveChangesOptions.None to generate a merge request.
        catch (DataServiceRequestException exception)
            var dataServiceClientException =       
                exception.InnerException as 
            if (dataServiceClientException != null)
                if (
                    dataServiceClientException.StatusCode == 
                    // a conflict may arise on a retry where it
                    // succeeded so this is ignored.


    public void Rollback()
        // TODO: clean up context.

    private static string BaseAddress(string connectionString)
        return CloudStorageAccount(connectionString)

    private static StorageCredentials CloudCredentials(
        string connectionString)
        return CloudStorageAccount(connectionString).Credentials;

    private static CloudStorageAccount CloudStorageAccount(
        string connectionString)
        var cloudConnectionString = 
            connectionString ?? 
        var cloudStorageAccount =     
        return cloudStorageAccount;

    private void SetupContext()
            * this retry policy will introduce a greater delay if 
            * there are retries than the original setting of 3 retries 
            * in 3 seconds but it will then show up a problem with 
            * the system without the system failing completely.
        this.RetryPolicy = 

        // don't throw a DataServiceRequestException when 
        // a row doesn't exist.
        this.IgnoreResourceNotFoundException = true;

In my ServiceDefinition config I have a CloudConnectionString. This has to be parsed to get the endpoint and account details before I can create the TableServiceContext. A couple of static methods do the job. This object also implements the Commit and Rollback methods for the Unit of Work. My Commit is implementing ‘Upsert’ so you may want it to be different or you may want to have different implementations of TableStorageContext that you can pass in to your Repository class depending on how it needs to talk to storage.

Further Architectural Options

I favour Uncle Bob’s Clean Architecture and as such I wouldn’t expose my Repository classes to other modules. I would wrap them in a further service layer that would receive and pass back Model objects. Cloud Table Storage is much more flexible than relational database storage but you have to think about it quite differently and the structure of your code will be very different to what you may be used to.

I’ve placed the Repository project on github: WindowsAzureRepository.


Acceptance Tests for User Stories allow the Product Owner to more easily say whether or not they accept a Story as ‘Done’. Also, Acceptance Tests can be used in Behaviour Driven Development (BDD) to provide an “outside in” development process that complements the “inside out” coding style of Test Driven Development (TDD).

SpecFlow brings Cucumber BDD to .NET without the need for a Ruby intermediary like IronRuby.

In your Tests project add a Features folder. SpecFlow installs some templates into VS.NET so add a new SpecFlowFeature and fill it out like the following example, InterestRatings.feature :

Feature: Interest Ratings
	In order to manage the Interest Ratings
	As a Trader
	I want an Interest Ratings screen

Scenario: View the Interest Ratings
	Given a repository of Interest Rating records
	When I view the Interest Rating screen 
	Then the full list is created

Scenario: Create an Interest Ratings record
	Given a repository of Interest Rating records
	When I create an Interest Rating with name Test 
	And Interest Rating code 4
	Then the Interest Rating is saved to the repository with 
                 name Test and code 4

The scenarios are the tests. The format is a clear Given-When-Then description. As you create the scenario the .feature.cs will be updated for you.

Now you need to link up the statements in your scenario to steps that the unit test framework can execute. Create a Steps folder under Features and add a SpecFlowStepDefinition. You’ll find the generated file has some useful placeholders to get you started. Here, for example, is InterestSteps.cs :

    public class InterestSteps
        private IInterestRatingService interestService;
        private InterestRatingViewModel interestRatingViewModel;
        private InterestRating rating;
        private Mock<IValidationService> validationService 
                      = new Mock<IValidationService>();
        private Mock<ILoadScreen> loadScreen 
                      = new Mock<ILoadScreen>();
        private Mock<IServiceLocator> serviceLocator 
                      = new Mock<IServiceLocator>();
        private Mock<IRepository<InterestRating>> interestRepository 
                      = new Mock<IRepository<InterestRating>>();
        private List<InterestRating> interestRatings;

        [Given("a repository of Interest Rating records")]
        public void GivenARepositoryOfInterestRatingRecords()
            Mock<IValidator> validator = new Mock<IValidator>();
                     .Setup(s => s.GetInstance<IValidationService>())
                     .Setup(v => v.GetValidator(
                     .Setup(s => s.GetInstance<IEventAggregator>())
                     .Returns(new EventAggregator());
                     .Setup(s => s.GetInstance<ILoadScreen>())
            ServiceLocator.SetLocatorProvider(() => this.serviceLocator.Object);
            this.interestRatings = 
                     .Setup(s => s.GetAll())
                  =  new InterestRatingService(

        [When("I view the Interest Rating screen")]
        public void WhenIViewTheInterestRatingScreen()
                  = new InterestRatingViewModel(this.interestService);

        [When("I create an Interest Rating with name (.*)")]
        public void WhenICreateAnInterestRatingWithName(string name)
                  = new InterestRatingViewModel(this.interestService);
                  = this.interestRatingViewModel
                                        .InterestRatings.Count - 1];
            this.rating.InterestRatingName = name;

        [When("Interest Rating code (.*)")]
        public void AndInterestRatingCode(int code)
            this.rating.InterestRatingCode = code;

        [Then("the full list is created")]
        public void ThenTheFullListIsCreated()
                      == this.interestRatingViewModel

        [Then("the Interest Rating is saved to the repository 
         with name (.*) and code (.*)")]
        public void ThenTheInterestRatingIsSavedToTheRepository(
                           string name, int code)
            InterestRating rating 
                = (from m in this.interestRatingViewModel.InterestRatings
                   where m.InterestRatingName.Equals(name)
                   select m).Single();

                "The interest rating name was not saved.");

                rating.InterestRatingCode == code,
                "The interest rating code was not saved.");

In particular, notice the reuse of steps, for example GivenARepositoryOfInterestRatingRecords(), and the use of variable placeholders like (.*) to allow the passing of variables into the tests.

BDD wraps TDD. A reasonable flow would be to start with the Story, write up the Acceptance Tests and sketch out some of the steps. As you sketch out the steps you can see what unit tests you need so you go and develop the code using TDD. Once your code is ready and all the unit tests are passing you can integrate the layers with the BDD tests and when those are passing you have fulfilled your Acceptance Test.

Gherkin parsers for SpecFlow are on the way as are VS.NET language plugins (Cuke4VS – currently this crashes my VS.NET 2008).

Cuke4Nuke is another Cucumber port that is worth looking at.

The readability of the Features makes it easy to take Acceptance Tests from User Stories so that the Product Owner and Stakeholders can see what the system is doing. The “outside in” nature of the creating the code gives focus to fulfilling the User Story.


Setting up PartCover for .NET unit test code coverage takes a little work.

Trying to running PartCover.exe on x64

“Retrieving the COM class factory for component with CLSID {FB20430E-CDC9-45D7-8453-272268002E08} failed due to the following error: 80040153.”

This is because the COM class requested is 32-bit only and PartCover is running as a 64-bit app.

Setting environment for using Microsoft Visual Studio 2008 x86 tools.

C:\> "%VS90COMNTOOLS%\vsvars32"

Microsoft (R) .NET Framework CorFlags Conversion Tool.

C:\> CORFLAGS /32BIT+ /FORCE path\to\PartCover\PartCover.exe

Microsoft (R) .NET Framework Strong Name Utility

C:\> sn -k PartCover.Console.snk

-k [<keysize>] <outfile>
Generate a new key pair of the specified size and write it into <outfile>.

C:\> sn -R PartCover.exe PartCover.Console.snk

-R[a] <assembly> <infile>
Re-sign signed or partially signed assembly with the key pair in <infile>. If -Ra is used, hashes are recomputed for all files in the assembly.

Running PartCover

This is an example command line to create an Xml report of code coverage.

--target .\tools\NUnit\nunit-console-x86.exe
--target-work-dir .\src\Tests\
--target-args Alpha.Modules.Sam.Tests\bin\Alpha.Modules.Sam.Tests.dll
--output .\docs\PartCoverReport.xml
--include [Alpha*]*
--exclude [*Tests]*

Viewing the report

A new viewer for the reports is required (the stylesheets aren’t very good) – ReportGenerator.



A command script with argument placeholders.

--target .\tools\NUnit\nunit-console-x86.exe
--target-work-dir %1
--target-args %2
--output .\docs\PartCoverReport.xml
--include %3
--exclude [*Tests]*


Using PartCover from VS.NET

VS.NET > Tools > ExternalTools…

Title: PartCover
Command: $(SolutionDir)..\PartCover.cmd
Arguments: $(BinDir) $(BinDir)$(TargetName)$(TargetExt) [*Module*]*
Initial directory: $(SolutionDir)..
Use Output window [check]
Prompt for arguments [check]

Now there is a “PartCover” option in the Tools menu. Select a Test project and select Tools > PartCover. In the displyed command arguments change “Module” to the name of the project, i.e. “Sam”, and run.

The report is written out to: .\docs\PartCover\index.htm

(I would launch IE automatically but haven’t added that to the command script yet.)

Adding metadata to Enums

A good enumeration in your C# code isn’t going to look like the values in your lookup table in your database and neither is it likely to have great descriptions of the items that you could display to your users. So how to extend the enumeration?

We’ve used attributes and called them EnumDescription (to distinguish it from the ComponentModel defined DescriptionAttribute) and EnumRepositoryValue. We then used extension methods on the enumeration values so they could easily display a good description or submit their equivalent value to the database.

One problem is that the .NET Enum type is not extensible so you can’t easily create a FromRepository() extension method without decorating the base Type. For translating database values to enums we created a helper method instead.

Now you can say this:

EnumMother enumMother =

And this:

string description = EnumMother.Some.GetDescription();

And this:

string repositoryValue = EnumMother.Some.ToRepositoryValue();

Given this:

    public enum EnumMother
        [EnumDescription("Nothing at all")]
        None = 0,

        [EnumDescription("Some of it")]
        Some = 1,

        [EnumDescription("Everthing, ever")]

A couple more extensions provide ToList() functionality as a bonus for creating user interface select lists.