MiniProfiler

I’m going to switch gears here for a post or two and talk about software profiling.  For those of you who have never used a profiler before, or have no idea what I’m about to talk about, here’s a quick run-down:

A profiler is an add-on program that will measure the performance of your code.  Typically, this software allows the developer to drill-down into slow sections of code to see what is hogging all the CPU cycles.  One package I’ve used in the past, and is an excellent profiler, is Redgate’s Ants profiler.  It’s worth every penny.  However, if you’re like me, you have a limited number of pennies to throw around (yes, Ants is a bit on the pricey side).  So I’m always on the lookout for something cheap but easy to use.  This is where MiniProfiler comes in.

Miniprofiler is easy to install.  It took me about 15 minutes to install it into a demonstration MVC4 application.  If you go to the MiniProfiler website: http://miniprofiler.com/, you can follow the directions step-by-step.  Use the NuGet package manager to get the versions of MiniProfiler that you need.

For a generic MVC4 program, you’ll need the following setup steps:

1. Install the NuGet packages for MiniProfiler (PM> Install-Package MiniProfiler), the MVC package (PM> Install-Package MiniProfiler.Mvc4), and if you are using Entity Framework 6 (PM> Install-Package MiniProfiler.EF6 -Pre).  There is also a package for NHibernate (which I will be testing in a future post).  I discovered the NHibernate version when I typed “miniprofiler” in the search box of the NuGet package install window.

2. Next, just like the instructions show you on the MiniProfiler.com website:

@using StackExchange.Profiling;
  ...

@MiniProfiler.RenderIncludes()

Obviously, in an MVC application, the head and body tags are not shown.  Just declare 
the using statement at the top of the view and put the RenderIncludes() method at 
the bottom.

3. Add this to the global.asax file:

using StackExchange.Profiling;

protected void Application_BeginRequest()
{
if (Request.IsLocal)
{
MiniProfiler.Start();
}
}

I also added this:

protected void Application_EndRequest()
{
MiniProfiler.Stop();
}

4. One last thing for the setup, open your Web.config and add this:

  <system.webServer>    
<validation validateIntegratedModeConfiguration="false" />
<handlers>

<add name="MiniProfiler" path="mini-profiler-resources/*" verb="*
           type="System.Web.Routing.UrlRoutingModule" resourceType="Unspecified"  
           preCondition="integratedMode" />

Now you’ll need to add some “using” statements to your code if you expect to profile sections of your code.


var profiler = MiniProfiler.Current; // it’s ok if this is null
using (profiler.Step(“Title to show for this code“))
{
… your code
}
OK, now run your MVC4 program, and you should see something like this:
You’ll notice in the upper left corner of your browser will be a tiny button with the time it took to execute your code.  Click on this button and you’ll see something like this:


You can click on the “more columns” to see more detailed information about the profile.  In my example MVC4 application, I used a controller named Home with a view named “Index”.  I also put “Read person list” inside my profiler step title, which took 20.2 ms according to the information above.
OK, now, you can decorate your program with “using” statements to wrap each granular section of code in a profiler step.  Then you can turn off your profiler by controlling the MiniProfiler.Start() method in the global asax.   
Adding Entity Framework Support

Install the MiniProfiler using NuGet 
(PM> Install-Package MiniProfiler.EF6 -Pre)

Now open your Global.asax file and add the following using statement to the top:


using StackExchange.Profiling.EntityFramework6;

Now add the initialize method to your Application_Start() method (this method should already exist):

protected void Application_Start()
{
  …
  MiniProfilerEF6.Initialize();
}
Now run your program and you should see an additional column of numbers for your sql calls (assuming you setup an EF6 data context and called a query that is wrapped in a miniprofiler step):
Notice the links in the sql column?  Click on one of these and you’ll get a break-down of what was executed:
ReadAllrecords is a method name that I created to read records from a table called “person”.  The first entity framework query looks like this:

var query = (from p in db.people 
             select p.first).ToList();

and the second query looks like this:

var query = (from p in db.people 
             select p).ToList();

The profiler spits out the actual SQL that was generated by my LINQ queries, which can be convenient to determine what is going on at the SQL server level.  You can also copy the SQL text and execute it in the MS SQL server console window to verify what is going on.

Next time, I’m planning to try out the NHibernate version.  I’ll be setting up a demo NHibernate project, populate with a large list of data (maybe I’ll copy one of my previous NHibernate projects) and I’ll show how the MiniProfiler behaves.



 

Entity Framework 6 vs. NHibernate smackdown!

It’s time for a performance test between EF-6 and NHibernate.  Previously I did a smackdown between EF-6 and LINQ-to-SQL (see this article: Entity Framework 6 vs. LINQ-toSQL smackdown!).  This time I’m going to show the performance of inserts between EF-6 and NHibernate.

I used the same tables and data as in the previous blog posting.  The code I used for NHibernate to insert records looks like this (I also created code for select, update and delete):

using (db.BeginTransaction())
{
    for (int j = 0; j < 10; j++)
    {
        for (int i = 0; i < 1000; i++)
        {
            Person personRecord = new Person()
            {
                first = firstnames[i],
                last = lastnames[i],
                department = 1
            };

            db.Save(personRecord);
        }
    }
    db.Transaction.Commit();
}

Here are the results from the CRUD tests comparing EF-6 with NHibernate (I also threw in the latest test run of LINQ-to-SQL for comparison):


OK, now for a few caveats:

1. I ran each test 5 times and chose the fastest time.
2. LINQ-to-SQL is not optimized for maximum speed.
3. These times are based on my machine at the time that I ran these programs.
4. These tests are only for general cases.
5. I put the select query in a loop to run 1000 times to obtain a measurement.

Generally speaking it appears that NHibernate is quite a bit faster than either EF-6 or LINQ-to-SQL for CRUD operations.  The select query is not a very good test.  In fact, the bulk of the time could be attributed to the looping code itself since my guess is that a select is all done within the ORM once the data has been cached.  A more effective test would involve 5 to 10 large tables of data using multiple types of select cases (like subqueries and outer joins, order by’s and group by’s).  Another test that should be performed as a baseline, would be a direct sql query without the aid of an ORM.  This should be faster than all of these ORMs.

To obtain the code, download this zip file containing all 3 test projects:

ORMSpeedTestProjects.zip

If you download and use these projects, don’t forget to change the configuration settings for each project to point to your database!


 

EF-6 Unit testing (Part 2)

Introduction

I’ve covered some unit testing in a previous blog post.  This time I want to expand on my earlier post and show how to mock two or more tables.  I will also demonstrate a weakness I discovered in mocking a database using the mock and a method to get around it.


Two or more Tables

You might remember this code:

using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Moq;


namespace DatabaseTestConsole
{
    [TestClass]
    public class UnitTests
    {
        [TestMethod]
        public void TestQuery()
        {
            var data = new List<account>
            {
                new account { username = “test“,pass=”testpass1” },
                new account { username = “ZZZ“,pass=”testpass2” },
                new account { username = “AAA“,pass=”testpass3” },
            }.AsQueryable();


            var mockSet = new Mock<DbSet<account>>();
            mockSet.As<IQueryable<account>>().Setup(m => m.Provider)

                   .Returns(data.Provider);
            mockSet.As<IQueryable<account>>().Setup(m => m.Expression)

                   .Returns(data.Expression);
            mockSet.As<IQueryable<account>>().Setup(m => m.ElementType)

                   .Returns(data.ElementType);
            mockSet.As<IQueryable<account>>().Setup(m => m.GetEnumerator())

                   .Returns(data.GetEnumerator());

            var mockContext = new Mock<DatabaseContext>();
            mockContext.Setup(c => c.accounts).Returns(mockSet.Object);


            UserRights rights = new UserRights(mockContext.Object);

            Assert.AreEqual(“testpass1”, rights.LookupPassword(“test“),

                  “password for account test is incorrect“);
            Assert.AreEqual(“testpass2”, rights.LookupPassword(“ZZZ“),

                  “password for account ZZZ is incorrect“);
            Assert.AreEqual(“testpass3”, rights.LookupPassword(“AAA“),

                  “password for account AAA is incorrect“);
        }
    }
}


I used it to demonstrate how to mock a table and unit test it.  Now I’m going to add a table to the mix.  First, I’m going to add an object that uses two tables (departments and accounts).  Then I’ll show how to mock the database that it uses:

public class PersonnelPerDepartment
{
    private DepartmentContext _DeptContext;

    public PersonnelPerDepartment(DepartmentContext deptContext)
    {
        _DeptContext = deptContext;
    }

    public int TotalPersonnel()
    {
        var personnelDeptQuery = (
            from d in _DeptContext.departments
            join p in _DeptContext.people on d.id equals p.department
            select p).ToList();

        return personnelDeptQuery.Count();
    }
}

I specifically setup the object to require two tables to query from.  Then I altered the unit test code as thus:

[TestMethod]
public void TestTwoTables()
{
    var deptData = new List<department>
    {
        new department {id=1, name=”Operations“},
        new department {id=2, name=”Sales“}
    }.AsQueryable();

    var deptMockSet = new Mock<DbSet<department>>();
    deptMockSet.As<IQueryable<department>>().Setup(m => m.Provider)
        .Returns(deptData.Provider);
    deptMockSet.As<IQueryable<department>>().Setup(m => m.Expression)
        .Returns(deptData.Expression);
    deptMockSet.As<IQueryable<department>>().Setup(m => m.ElementType)
        .Returns(deptData.ElementType);
    deptMockSet.As<IQueryable<department>>().Setup(m => m.GetEnumerator())
        .Returns(deptData.GetEnumerator());

    // department table
    var persData = new List<person>
    {
        new person {id=1, first=”Joe“,last=”Smith“,department=1},
        new person {id=2, first=”Jane“, last=”Summers“,department=1},
        new person {id=2, first=”Bob“, last=”Anders“,department=1},
    }.AsQueryable();

    var personMockSet = new Mock<DbSet<person>>();
    personMockSet.As<IQueryable<person>>().Setup(m => m.Provider)
        .Returns(persData.Provider);
    personMockSet.As<IQueryable<person>>().Setup(m => m.Expression)
        .Returns(persData.Expression);
    personMockSet.As<IQueryable<person>>().Setup(m => m.ElementType)
        .Returns(persData.ElementType);
    personMockSet.As<IQueryable<person>>().Setup(m => m.GetEnumerator())
        .Returns(persData.GetEnumerator());

    var mockContext = new Mock<DepartmentContext>();
    mockContext.Setup(c => c.departments).Returns(deptMockSet.Object);
    mockContext.Setup(c => c.people).Returns(personMockSet.Object);

    PersonnelPerDepartment persDept = new PersonnelPerDepartment(mockContext.Object);
    int total = persDept.TotalPersonnel();

    Assert.AreEqual(3, total);
}
Each table will need it’s own mockSet, which I admit, is ugly.  The mockContext has two setups, one for each table (and you can add more tables to the context as needed).  Now the test runs and returns the three personnel listd in the person table.  You can experiment with different data sets and queries to test this for yourself.  I wanted to give an example of how the code would look for two tables.
I’ve been writing unit tests for a real application that I’m working on for DealerOn.  This application has dozens of tables and, as you might have guessed, this method gets lengthy.  Of course, you can setup all this stuff in an initialization method before running your tests, but there are other problems.  One problem I discovered was that you cannot add to your tables in your methods under test.  This mock object doesn’t support adding or deleting records.  For that, we need a different technique.
Using Test Doubles
This article at microsoft (Testing with your own test doubles (EF6 onwards)) demonstrates how to use test doubles with EF-6.  The advantages to using this technique is that you can add and delete from your tables and the unit testing code is more compact (most of the code is in the fake object).  
How to do it

First, create a solution with a console application project and and a test project.  Make sure you include your console app in your references inside your unit test project and include your using statement so you can unit test objects in your console application.
Next create an EF-6 edmx file in your console application and add your tables (my example uses my demo database that I’ve been using for quite a few blog posts.  This MS SQL server sample data has a department and person table in it).
Go to the link above (testing doubles) and copy the code under the header “Creating the in-memory test doubles”, then paste it into it’s own cs file in your unit testing project.  You’ll need to change the top class to match your EF-6 database:
public class TestContext : ISampleDataContext
{
    public TestContext()
    {
        this.people = new TestDbSet<person>();
        this.departments = new TestDbSet<department>();
    }

    public DbSet<person> people { get; set; }
    public DbSet<department> departments { get; set; }
    public int SaveChangesCount { get; private set; }
    public int SaveChanges()
    {
        this.SaveChangesCount++;
        return 1;
    }
}
You’ll need to add a bunch of usings to make the errors go away.  You should end up with these:
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Linq.Expressions;
using System.Threading;
using System.Threading.Tasks;
using EF6UnitTestingUsingDoubles; //your console project
In your console project, create an interface.  It’ll look something like this:
using System.Data.Entity;

namespace EF6UnitTestingUsingDoubles
{
    public interface ISampleDataContext
    {
        DbSet<person> people { get; set; }
        DbSet<department> departments { get; set; }
        int SaveChanges();
    }
}
You’ll need to add this interface to your EF context.cs file constructor:
public partial class sampledataEntities : DbContext, ISampleDataContext
{
    public sampledataEntities()
        : base(“name=sampledataEntities“)
    {
    }
    
}
It’s best to put the “ISampleDataContext” (and the comma) in the T4 (“tt”) script file, so auto-generated code will not wipe out your changes:
<#=Accessibility.ForType(container)#> partial class <#=code.Escape(container)#> : DbContext, ISampleDataContext


Next create your object in the main console app:
using System.Linq;

namespace EF6UnitTestingUsingDoubles
{
    public class PersonnelPerDepartment
    {
        private ISampleDataContext _DeptContext;

        public PersonnelPerDepartment(ISampleDataContext deptContext)
        {
            _DeptContext = deptContext;
        }

        public int TotalPersonnel()
        {
            var personnelDeptQuery = (
                from d in _DeptContext.departments
                join p in _DeptContext.people on d.id equals p.department
                select p).ToList();

            return personnelDeptQuery.Count();
        }
    }
}

One thing to note: You will be passing the interface definition to your classes instead of the Entity Framework context object like normal.  When your program executes normally, it will use the interface just like the fake context does when performing your unit tests.

Last, but not least, your unit test:

[TestMethod]
public void TestMethod1()
{
    var context = new TestContext();

    var deptTable = new department()
    {
        id=1,
        name=”Sales
    };
    context.departments.Add(deptTable);
    context.SaveChanges();

    var personTable = new person()
    {
        id=1,
        first = “Joe“,
        last = “Smith“,
        department = 1
    };
    context.people.Add(personTable);
    personTable = new person() 
    { 
        id = 2, 
        first = “Jane“, 
        last = “Summers“, 
        department = 1 
    };
    context.people.Add(personTable);
    context.SaveChanges();

    PersonnelPerDepartment persDept = new PersonnelPerDepartment(context);
    int total = persDept.TotalPersonnel();

    Assert.AreEqual(2, total);
}
Notice how much cleaner the test method is.  The first line creates a test context, then one record is added to the department table.  Next two records are added to the person table and that leaves the remaining lines to call the method under test (persDept) and do an assert.  The fake context object and the interface should be setup to contain all the tables in your context.  You will then be able to add data to your tables inside your unit tests as needed.  Your methods under test can perform queries on all the tables in the context without an issue when under tests.
Downloadable Sample Project
You can download the entire sample project here: EF6 Unit Testing Doubles Project.  Be aware that this project does not include the database and you’ll need to alter the connection to your database and possibly manipulate the edmx file to make it work with your database.  If you have an MS SQL server setup on your PC, then you can use these two scripts to generate the tables used in this sample (after creating a database called “sampledata”):
USE [sampledata]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[person](
[id] [int] IDENTITY(1,1) NOT NULL,
[first] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[last] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[department] [int] NOT NULL,
 CONSTRAINT [PK_person] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
USE [sampledata]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[department](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_department] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
 

Unit Testing Object with Two EF-6 Contexts

Introduction

As I’ve mentioned before, I work for DealerOn.  Our development environment is organized by some very intelligent software developers who are pushing the envelope on technological advances in our software.  One of the development strategies is to use as much automated testing as possible.  So we’re all learning what the Microsoft test environment is capable of and what its limitations are.  One problem that was encountered recently by a new developer of ours is how to test a method that contains two EF-6 queries to two different contexts.

So I opened up my Visual Studio project that I used to create the EF-6 unit testing blog post about a month ago and started copying and pasting code until I got it to work the way I expected it to.

Shut-up and Show Me an Example!

OK, here’s an object and one method I created using two EF-6 frameworks (I’ll leave it to the reader to create two EF-6 edmx files with context names called AccountingContext with a table called account and another called DepartmentContext with a table called department). 

public class DepartmentsAndAccounts
{
    private DepartmentContext _DeptContext;
    private AccountingContext _AccountContext;


    public DepartmentsAndAccounts(
           DepartmentContext deptContext,
           AccountingContext accountContext)
    {
        _DeptContext = deptContext;
        _AccountContext = accountContext;
    }


    public int TotalDeptAndAccountRecords()
    {
        var deptQuery = (from dept

                         in _DeptContext.departments
                         select dept.name).ToList();

        var accountQuery = (from acct
                            in _AccountContext.accounts
                            select acct.username).ToList();

        int result = deptQuery.Count() + accountQuery.Count();
        return result;
    }
}

As you can see, I needed to pass the two contexts into the initializer.  These contexts are two separate objects so they must be passed in separately and saved for use with their own tables.  If any reader has a more elegant method of handling this, please leave me a message and I’ll do a follow-up post.

Now it’s time to setup the unit test.  Basically, I just mocked two separate tests and called the DepartmentsAndAccounts object at the end of the test.  Here’s the entire unit test method:

[TestMethod]
public void TestTwoContexts()
{
    //account table
    var accountData = new List<account>
    {
        new account { username = “test“,pass=”testpass1” },
        new account { username = “ZZZ“,pass=”testpass2” },
        new account { username = “AAA“,pass=”testpass3” }
    }.AsQueryable();


    var accountMockSet = new Mock<DbSet<account>>();
    accountMockSet.As<IQueryable<account>>().Setup(m => m.Provider)
        .Returns(accountData.Provider);
    accountMockSet.As<IQueryable<account>>().Setup(m => m.Expression)
        .Returns(accountData.Expression);
    accountMockSet.As<IQueryable<account>>().Setup(m => m.ElementType)
        .Returns(accountData.ElementType);
    accountMockSet.As<IQueryable<account>>().Setup(m => m.GetEnumerator())
        .Returns(accountData.GetEnumerator());


    var accountMockContext = new Mock<AccountingContext>();
    accountMockContext.Setup(c => c.accounts).Returns(accountMockSet.Object);


    // department table
    var deptData = new List<department>
    {
        new department {name=”Operations“},
        new department {name=”Sales“}
    }.AsQueryable();


    var deptMockSet = new Mock<DbSet<department>>();
    deptMockSet.As<IQueryable<department>>().Setup(m => m.Provider)

        .Returns(deptData.Provider);
    deptMockSet.As<IQueryable<department>>().Setup(m => m.Expression)
        .Returns(deptData.Expression);
    deptMockSet.As<IQueryable<department>>().Setup(m => m.ElementType)
        .Returns(deptData.ElementType);
    deptMockSet.As<IQueryable<department>>().Setup(m => m.GetEnumerator())
        .Returns(deptData.GetEnumerator());


    var deptMockContext = new Mock<DepartmentContext>();
    deptMockContext.Setup(c => c.departments)

        .Returns(deptMockSet.Object);

    DepartmentsAndAccounts deptAndAccts =
        new DepartmentsAndAccounts(deptMockContext.Object, accountMockContext.Object);
    int total = deptAndAccts.TotalDeptAndAccountRecords();


    Assert.AreEqual(5, total, “total does not equal expected value“);
}


As you can see from the code, I mocked up two different contexts by using a copy and paste and renamed the variables (these came from code used in the previous blog article).  The result should be the total count of both tables, which should be 5 (3 records in account and 2 records in department).  You can alter your assert to test for the result you’re expecting from the method you have under test.