Overview

Download the code on GitHub

Install with NuGet.

Entity Framework provides a very versatile data-persistence API, but like any ORM, it’s not without drawbacks. Recently, during a performance review of a .NET-based web application, I noticed several drawbacks attributable to EF.

EF doesn’t provide batch-processing support. Let’s say I have a class as follows:

class Person {
    public string FirstName { get; set; }
    public string Surname { get; set; }
}

class Team {
    public List<Person> Members { get; set; }
}

class MyClass {
    public void Run() {
        var team = new Team {
            Members = new List<Person> {
                new Person {FirstName = "Paul", Surname = "Mooney"},
                new Person {FirstName = "Some", Surname = "OtherGuy"}
            }
        };
    }
}

Entity Framework will invoke a separate SQL command for each Team Member – teams with thousands of members will incur a round trip to the DB for each member. The solution is provided in the SQLBatchBuilder class. This class allows you to batch such commands in a single command, persisting your data to the DB while maintaining optimal performance by minimising the number of round-trips to 1. The framework also contains a handy LINQ-style SQL command builder, adhering to the functional programming model.

Consider the above example. Let’s say we wanted to create a Team object, which consists of a single entry in a SQL table. The generated SQL would look something like this:

insert into dbo.team (id, name) values (1, ‘My Team’);

Nothing unusual here. But what if the team has 50 Members? That’s 50 round-trips to the DB to create each Member, plus 1 round-trip to create the Team. This is quite excessive.

What if we were building an application that saved customer invoices, or sombody’s Facebook friends? This could potentially result in thousands of round-trips to the DB for a single unit-of-work.

SQLBuilder provides a simple, but effective solution to this:

class Person {
    public string FirstName { get; set; }
    public string Surname { get; set; }
}

class Team {
    public List<Person> Members { get; set; }
}

class MyClass {
    public void Run() {
        var team = new Team {
            Members = new List<Person> {
                new Person {FirstName = "Paul", Surname = "Mooney"},
                new Person {FirstName = "Some", Surname = "OtherGuy"}
            }
        };

		List<SQLBuilder> builders = new List<SQLBuilder>();

		foreach(var member in Team.Members) {
		    var sqlBuilder = new SQLBuilder(connectionString, SQLCommandType.Scalar);
			sqlBuilder.Insert(@"dbo.member", new List<string> {@"FirstName", @"Surname"}, member.FirstName, member.Surname);
			builders.Add(sqlBuilder);
		}

		sqlBatchBuilder = new SQLBatchBuilder(connectionString, SQLCommandType.Scalar, builders);
		sqlBatchBuilder.Execute();

		Console.WriteLine(sqlBatchBuilder.Result);
    }
}

In the above sample, we initialise a list of SQLBuilders. Each builder encapsulates a single insertion statement associated with each Member. Once we’ve looped through all members, we attach the list of SQLBuilders to a SQLBatchBuilder instance, which parses each SQLBuilder’s raw SQL and formats a single command comprised of each insertion statement. This is then executed as a single transaction, resulting in a single round-trip.

The SQLBuilder class itself supports the SQL language itself, allowing clauses, parameters, etc., to be applied:

    builder = new SQLBuilder(connectionString, SQLCommandType.Reader);

    builder.Select(@"Member.FirstName", @"Member.Surname")
           .From(@"Member");
		   .InnerJoin(@"Team", @"Member", @"Member.TeamId", @"Team.TeamId")
		   .Where(@"Member", @"Surname")
		   .EqualTo(@"Mooney");

	builder.Execute();

More updates to follow, including an in-depth analysis into the LINQ-style SQLBuilder.

Connect with me: