Using AutoMapper to perform LINQ aggregations

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>
}

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

image

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; }
}

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()
    };

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

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; }
}

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()
    )
);

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]

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()
    };

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.

Related Articles:

Post Footer automatically generated by Add Post Footer Plugin for wordpress.

About Jimmy Bogard

I'm a technical architect with Headspring in Austin, TX. I focus on DDD, distributed systems, and any other acronym-centric design/architecture/methodology. I created AutoMapper and am a co-author of the ASP.NET MVC in Action books.
This entry was posted in AutoMapper, Entity Framework, NHibernate. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • gilligan_MH

    I am pretty sure NHibernate does not support half of these…

    • jbogard

      It supports some of these, but not all.

      • gilligan_MH

        Thanks for the post btw. These kinds of things make me want to reconsider my usage of NHibernate over Entity Framework because of the productivity I can gain being able to project to Automapper. OF course that is assuming I cannot get my hands on a nosql solution :-)

  • Richard

    Hi Jimmy, great work. I use AutoMapper in just about every project I work on now.
    A question for you that’s kind of related to your post – I’ve encountered an issue when using AutoMapper to map between two models, and trying to translate LINQ queries from the top-level model down to the underlying data model. Currently I’m not aware of any way to automatically handle this, instead I’d have to resort to walking the LINQ expression tree and re-writing it according to the mapping between the two models.
    A few questions:
    - firstly, have you encountered a similar issue before and do you have any recommendations?
    - Seeing as AutoMapper already knows all mappings between the two models, is there a way to access these?
    - Is the auto-translation of these LINQ queries something you’d consider adding to AutoMapper?

    • jbogard

      Hmmm, could you post this to a GH issue, along with an example of what you’re trying to do?

  • Pingback: Using AutoMapper to perform LINQ aggregations |...

  • khalidabuhakmeh

    If I validate the mapping config using the configuration you have above will it pass or throw an exception?

  • Calvin

    Wicked awesome indeed!!