Using AutoMapper to perform LINQ aggregations

8 April, 2014. It was a Tuesday.

In the last post I showed how AutoMapper and its LINQ projection can prevent SELECT N+1 problems and other lazy loading problems. That was pretty cool, but wait, there’s more! What about complex aggregation? LINQ can support all sorts of interesting queries, that when done in memory, could result in really inefficient code.

Let’s start small, what if in our model of courses and instructors, we wanted to display the number of courses an instructor teaches and the number of students in a class. This is easy to do in the view:

	@foreach (var item in Model.Instructors)
	{
	<tr>
	<td>
	@item.Courses.Count()
	</td>
	</tr>
	}
	<!-- later down -->
	@foreach (var item in Model.Courses)
	{
	<tr class="@selectedRow">
	<td>
	@item.Enrollments.Count()
	</td>
	</tr>
	}

view raw foo.html hosted with ❤ by GitHub

But at runtime this will result in another SELECT for each row to count the items:

We could eager fetch those rows ahead of time, but this is also less efficient than just performing a SQL correlated subquery to calculate that SUM. With AutoMapper, we can just declare this property on our ViewModel class:

	public class CourseModel
	{
	public int CourseID { get; set; }
	public string Title { get; set; }
	public string DepartmentName { get; set; }
	public int EnrollmentsCount { get; set; }
	}

view raw viewmodel.cs hosted with ❤ by GitHub

AutoMapper can recognize extension methods, and automatically looks for System.Linq extension methods. The underlying expression created looks something like this:

	courses =
	from i in db.Instructors
	from c in i.Courses
	where i.ID == id
	select new InstructorIndexData.CourseModel
	{
	CourseID = c.CourseID,
	DepartmentName = c.Department.Name,
	Title = c.Title,
	EnrollmentsCount = c.Enrollments.Count()
	};

view raw projection.cs hosted with ❤ by GitHub

LINQ providers can recognize that aggregation and use it to alter the underlying query. Here’s what that looks like in SQL Profiler:

	SELECT
	[Project1].[CourseID] AS [CourseID],
	[Project1].[Title] AS [Title],
	[Project1].[Name] AS [Name],
	(SELECT
	COUNT(1) AS [A1]
	FROM [dbo].[Enrollment] AS [Extent5]
	WHERE [Project1].[CourseID] = [Extent5].[CourseID]) AS [C1]
	FROM --etc etc etc

view raw result.sql hosted with ❤ by GitHub

That’s pretty cool, just create the property with the right name on your view model and you’ll get an optimized query built for doing an aggregation.

But wait, there’s more! What about more complex operations? It turns out that we can do whatever we like in MapFrom as long as the query provider supports it.

Complex aggregations

Let’s do something more complex. How about counting the number of students whose name starts with the letter “A”? First, let’s create a property on our view model to hold this information:

	public class CourseModel
	{
	public int CourseID { get; set; }
	public string Title { get; set; }
	public string DepartmentName { get; set; }
	public int EnrollmentsCount { get; set; }
	public int EnrollmentsStartingWithA { get; set; }
	}

view raw CourseModel.cs hosted with ❤ by GitHub

Because AutoMapper can’t infer what the heck this property means, since there’s no match on the source type even including extension methods, we’ll need to create a custom mapping projection using MapFrom:

	cfg.CreateMap<Course, InstructorIndexData.CourseModel>()
	.ForMember(m => m.EnrollmentsStartingWithA, opt => opt.MapFrom(
	c => c.Enrollments.Where(e => e.Student.LastName.StartsWith("A")).Count()
	)
	);

view raw CourseModelMapping.cs hosted with ❤ by GitHub

At this point, I need to make sure I select the overloads for the aggregation methods that are supported by my LINQ query provider. There’s another overload of Count() that takes a predicate to filter items, but it’s not supported. Instead, I need to chain a Where then Count. The SQL generated is now efficient:

	SELECT
	[Project2].[CourseID] AS [CourseID],
	[Project2].[Title] AS [Title],
	[Project2].[Name] AS [Name],
	[Project2].[C1] AS [C1],
	(SELECT
	COUNT(1) AS [A1]
	FROM [dbo].[Enrollment] AS [Extent6]
	INNER JOIN [dbo].[Person] AS [Extent7]
	ON ([Extent7].[Discriminator] = N''Student'')
	AND ([Extent6].[StudentID] = [Extent7].[ID])
	WHERE ([Project2].[CourseID] = [Extent6].[CourseID])
	AND ([Extent7].[LastName] LIKE N''A%'')) AS [C2]

view raw CustomCount.sql hosted with ❤ by GitHub

This is a lot easier than me pulling back all students and looping through them in memory. I can go pretty crazy here, but as long as those query operators are supported by your LINQ provider, AutoMapper will pass through your MapFrom expression to the final outputted Select expression. Here’s the equivalent Select LINQ projection for the above example:

	courses =
	from i in db.Instructors
	from c in i.Courses
	where i.ID == id
	select new InstructorIndexData.CourseModel
	{
	CourseID = c.CourseID,
	DepartmentName = c.Department.Name,
	Title = c.Title,
	EnrollmentsCount = c.Enrollments.Count(),
	EnrollmentsStartingWithA = c.Enrollments
	.Where(e => e.Student.LastName.StartsWith("A")).Count()
	};

view raw projection.cs hosted with ❤ by GitHub

As long as you can LINQ it, AutoMapper can build it. This combined with preventing lazy loading problems is a compelling reason to go the view model/AutoMapper route, since we can rely on the power of our underlying LINQ provider to build out the correct, efficient SQL query better than we can. That, I think, is wicked awesome.

← Using AutoMapper to prevent SELECT N+1 problems

AutoMapper 3.2.0 released →