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.

Long running processes and RESTful APIs.

I have to create a RESTful API that can accept a request for a resource that may take some time to find as it’s the result of a complex deterministic model. The ASP.NET Web API is hosted on an Azure Web Role and exposes a RESTful interface to the clients. The clients are not web browsers, in this case, but other applications.

On the initial GET or “fetch” of a particular resource the very long querystring that contains all of the data the model run requires is handled with a 202 Accepted. Then a 304 Not Modified is returned on each subsequent request for the same resource until finally we have the new resource in our server’s cache, placed there by a background process that monitors a queue that in turn is fed by the model engine. Finally we will return the new resource with a 200 OK. As the result already exists, in a way, and we are just having to take some time to fetch it for the client, then the cache is not being updated with a result but rather it is caching the resource from the application.

In REST the resource has an id. In this case the id is the URI or the ETag (the Entity Tag being the hash of the URI). This is not a “process”, like a POST or “insert” of a resource, nothing in the data is being changed, it’s just a very slow request.

Request Response Ping Ping

The initial request immediately returns a 202 Accepted. This is better than holding the connection open for up to the 4 minutes allowed by the Azure Load Balancer as that would be expensive and we would run the risk of overloading our application.

The 202 response carries with it an ETag (Entity Tag) which is the token that the client can use to make another request to the application. An ETag represents a resource and is intended to be used for caching. We are caching our resource and it’s current state is empty.

The client will then present the ETag in the If-None-Match header value. This is the specified way to check for any changes to a resource. If the state of the resource has not changed then the application will return a 304 Not Modified. If the resource has changed, which in practice means the application has completed its run and dumped the result in the cache, then the application will return the current state of the resource. The ETag is also returned, should the client wish to request the resource again, and a 200 OK to indicate the end of the request.

Note that the above is only example code that has been stripped of some conditional checks and guard clauses for readability.

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.

Unit testing Expressions with Moq

When setting up a mock object with the Moq framework you can specify what parameters may be passed to the mock and thus what to return when the mock encounters those specific parameters.

This falls down in the odd instance when you’re trying to pass a lambda expression to an optional parameter. This occurs, for example, on IRepository Find() as the where: and the orderby: are both optional. You can’t pass an expression tree to an optional parameter as it’s not compiled yet.

Moq gets around this by allowing It.IsAny() so at least we can specify the type of the expression to accept. How do we know, though, whether our mocked interface was called correctly? Quite different expressions, and any parameters, could have been used.

Fortunately, you can access the actual expression used in the .Returns() callback on the mock. Here you can create an anonymous function that will test the signature, the parameters used and still return the object mothers you’ve specified.

Here’s what we’ve got so far.

       public void ShouldFindUser()
           Expression<Func> expected = 
               x => 
               x.Name == this.sut.Name && 
               x.Memberships[0].Password == 
           // note: optional parameters that are passed 
           // expression trees can't be compiled in .NET 4.0 
           // but Moq's It.IsAny saves the day.
               r =>
                       (Expression<Func> where, 
                        IOrderByClause[] order) =>
                               // note: before the expressions can be 
                               // compared they must
                               // be partially evaluated.
                               return suts;

           service = new UserService(repository.Object);

           bool isValidUser = 

           Assert.IsTrue(isValidUser, "Expected to find user.");

Comparing the expressions, however, introduces more problems. The expressions have not been compiled yet so they have unevaluated references to closed variables. The expressions will differ between the actual expression and any expected expression you may have defined using your object mother for the parameters.

You need to partially evaluate the expressions to create constants from the references before you can compare them (the comparison is essentially comparing the two .ToString() products). Finally, you can wrap an unit test assertion around the equality comparison.

       private void ExpressionMatch(Expression actual, Expression expected)
           var isEqual = 
               ExpressionEqualityComparer.ExpressionEqual(actual, expected);

           Assert.IsTrue(isEqual, "Expected the expressions to match.");

Now if someone alters the code that calls to the interface the test will fail. Otherwise it would have been joyfully returning object mothers for any old query passed to the interface.


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.

MSBuildCommunityTasks and Versioning

Auto-versioning our assemblies in TeamCity using MSBuild was more fiddly than I expected but ultimately a very clean implementation.

I’m using TeamCity for continuous integration and MSBuild to run our project’s solution file (.sln) as the build script. I wanted each of our builds to have a version number on the assembly (.exe) so that testers would know what they were dealing with. The format for the version number is the familiar dotted quad of Major.Minor.Build.Revision where the Build would be the build number from TeamCity and the Revision would be our version control system (VCS) revision number (our VCS is Perforce).

I use Scrum for Agile software construction, in an OpenUP project management process, so I’ve decided our Major number is the number of the release to the customer and the Minor is the iteration (Sprint) that produced the build. For example, my first build with this system was which means: we’ve yet to make a release to the customer (0); the build was from the eighth two-week Sprint (8); TeamCity has completed 282 builds; and Perforce is up to revision 11066.

MSBuildCommunityTasks includes an AssemblyInfo task. These are the steps I used to get this working:
(1) added MSBuildCommunityTasks to our “ExternalTools” folder in Perforce and submitted the MSBuild.Community.Tasks.dll and MSBuild.Community.Tasks.Targets files.
(2) integrated the MSBuildCommunityTasks items from step 1 into the solution’s “tools” folder.
(3) updated the MSBuild.Community.Tasks.Targets file to the correct path to the MSBuildCommunityTasksLib (in our “tools” folder from step 2).
(4) imported the Targets file from step 3 into the project file (.csproj).

<Import Project="$(MSBuildCommunityTasksTargets)" />

(5) created the properties for the parts of the version number so it is easy to update for releases and works both on the developer’s machine, where the Build number and Revision number are set to 0, and on TeamCity which provides environment variables for the TeamCity build number and the VCS revision number.

    <!-- Release -->
    <!-- Iteration -->
    <Build Condition="'$(BUILD_NUMBER)' != ''">$(BUILD_NUMBER)</Build>
    <Revision Condition="'$(BUILD_VCS_NUMBER)' != ''">$(BUILD_VCS_NUMBER)</Revision>

(6) deleted the current AssemblyInfo.cs from the source and the VCS.
(6) add the AssemblyInfo task that will autogenerate the AssemblyInfo.cs for each build

  <Target Name="BeforeBuild">
     <AssemblyInfo CodeLanguage="CS" 
                AssemblyDescription="My Product"
                AssemblyCompany="MyCompany Ltd"
                AssemblyCopyright="Copyright © MyCompany Ltd 2009"
                AssemblyVersion="$(Version)" />

Now I need to create a WiX project to build a release version and expose it as an artifact in TeamCity so our testers can easily pick up a build themselves.

Technorati Tags: