Using AutoMapper to prevent SELECT N+1 problems

Back in my post about efficient querying with AutoMapper, LINQ and future queries, one piece I glossed over was how View Models and LINQ projection can prevent SELECT N+1 problems. In the original controller action, I had code like this:

public ActionResult Index(int? id, int? courseID)
{
    var viewModel = new InstructorIndexData();
 
    viewModel.Instructors = db.Instructors
        .Include(i => i.OfficeAssignment)
        .Include(i => i.Courses.Select(c => c.Department))
        .OrderBy(i => i.LastName);
 
    if (id != null)
    {
        ViewBag.InstructorID = id.Value;
        viewModel.Courses = viewModel.Instructors.Where(
            i => i.ID == id.Value).Single().Courses;
    }
 
    if (courseID != null)
    {
        ViewBag.CourseID = courseID.Value;
        viewModel.Enrollments = viewModel.Courses.Where(
            x => x.CourseID == courseID).Single().Enrollments;
    }
 
    return View(viewModel);
}

See that “Include” part? That’s because the view shows information from navigation and collection properties on my Instructor model:

public class Instructor : Person
{
    [DataType(DataType.Date)]
    [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
    [Display(Name = "Hire Date")]
    public DateTime HireDate { get; set; }

    public virtual ICollection<CourseInstructor> Courses { get; set; }
    public virtual OfficeAssignment OfficeAssignment { get; set; }
}

public abstract class Person
{
    public int ID { get; set; }

    [Required]
    [StringLength(50)]
    [Display(Name = "Last Name")]
    public string LastName { get; set; }
    [Required]
    [StringLength(50, ErrorMessage = "First name cannot be longer than 50 characters.")]
    [Column("FirstName")]
    [Display(Name = "First Name")]
    public string FirstMidName { get; set; }

    [Display(Name = "Full Name")]
    public string FullName
    {
        get
        {
            return LastName + ", " + FirstMidName;
        }
    }
}

If I just use properties on the Instructor/Person table, only one query is needed. However, if my view happens to use other information on different tables, additional queries are needed. If I’m looping through a collection association, I could potentially have a query issued for each loop iteration. Probably not what was expected!

ORMs let us address this by eagerly fetching associations, via JOINs. In EF this can be done via the “Include” method on a LINQ query. In NHibernate, this can be done via Fetch (depending on the query API you use). This is addresses the symptom, but is not a good long-term solution.

Because our domain model exposes all data available, it’s easy to just show extra information on a view without batting an eye. However, unless we keep a database profiler open at all times, it’s not obvious to me as a developer that a given association will result in a new query. This is where AutoMapper’s LINQ projections come into play. First, we have a View Model that contains only the data we wish to show on the screen, and nothing more:

public class InstructorIndexData
{
    public IEnumerable<InstructorModel> Instructors { get; set; }

    public class InstructorModel
    {
        public int ID { get; set; }

        [Display(Name = "Last Name")]
        public string LastName { get; set; }
            
        [Display(Name = "First Name")]
        public string FirstMidName { get; set; }

        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
        [Display(Name = "Hire Date")]
        public DateTime HireDate { get; set; }

        public string OfficeAssignmentLocation { get; set; }

        public IEnumerable<InstructorCourseModel> Courses { get; set; } 
    }

    public class InstructorCourseModel
    {
        public int CourseID { get; set; }
        public string Title { get; set; }
    }
}

At this point, if we used AutoMapper’s normal Map method, we could still potentially have SELECT N+1 problems. Instead, we’ll use the LINQ projection capabilities of AutoMapper :

var viewModel = new InstructorIndexData();
 
viewModel.Instructors = db.Instructors
    .OrderBy(i => i.LastName)
    .Project().To<InstructorIndexData.InstructorModel>()
;
 

Which results in exactly one query to fetch all Instructor information, using LEFT JOINs to pull in various associations. So how does this work? The LINQ projection is quite simple – it merely looks at the destination type to build out the Select portion of a query. Here’s the equivalent LINQ query:

from i in db.Instructors
orderby i.LastName
select new InstructorIndexData.InstructorModel
{
    ID = i.ID,
    FirstMidName = i.FirstMidName,
    LastName = i.LastName,
    HireDate = i.HireDate,
    OfficeAssignmentLocation = i.OfficeAssignment.Location,
    Courses = i.Courses.Select(c => new InstructorIndexData.InstructorCourseModel
    {
        CourseID = c.CourseID,
        Title = c.Title
    }).ToList()
};

Since Entity Framework recognizes our SELECT projection and can automatically build the JOINs based on the data we include, we don’t have to do anything to Include any navigation or collection properties in our SQL query – they’re automatically included!

With AutoMapper’s LINQ projection capabilities, we eliminate any possibility of lazy loading or SELECT N+1 problems in the future. That, I think, is 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.
  • khalidabuhakmeh

    Wait a minute…. I thought the N+1 problem meant you could pull ALL of a collection without knowing it right? So I’m not seeing how AutoMapper prevents that from occurring. I actually think I still see two N+1 issues.

    The first instance.

    // I am pulling ALL the instructors…
    viewModel.Instructors = db.Instructors
    .OrderBy(i => i.LastName)
    .Project().To();

    The second instance is here. (Although I understand this is using the LINQ Projection feature in EF, so this compounds into one query).

    from i in db.Instructors
    orderby i.LastName
    select new InstructorIndexData.InstructorModel
    {
    ID = i.ID,
    FirstMidName = i.FirstMidName,
    LastName = i.LastName,
    HireDate = i.HireDate,
    OfficeAssignmentLocation = i.OfficeAssignment.Location,
    // You may have a lot of courses as a PHD Student
    Courses = i.Courses.Select(c => new InstructorIndexData.InstructorCourseModel
    {
    CourseID = c.CourseID,
    Title = c.Title
    }).ToList() // oops, should limit # of courses
    };

    You could potentially pull back All the instructors, compounded by All their courses (ouch). The only thing that can stop a bad N+1, is a good N+1 with a gun (just kidding). Actually the thing that stops N+1 problems is limiting the number of records you pull back by using Skip and Take.

    Still, I think the Project feature is pretty awesome. Great work on that. Definitely use it next time I use Entity Framework.

    • jbogard

      That’s what this page shows – all instructors with all courses. Select N+1 is still about lazy-loading and issuing a query for each item. I’ve replaced what would be a single query for all instructors, then queries for each instructor’s courses with a single query for all instructors and all courses.

      SELECT N+1 isn’t about limiting results, it’s about lazy loading features compounded with for loops. Check out that link to Ayende’s blog for more info.

      • khalidabuhakmeh

        Ah I must have mixed up my issues. I am reading this post on the RavenDB knowledge base and he states there are two issues with EF.

        1. Unbounded Result Sets (what I am referring to)
        2. Unbounded Number of Requests (What you are referring to)

        You are right about “Select N+1″ being the idea of Unbounded Number of Requests. My apologies there. I can admit when I’m wrong :)

        http://ravendb.net/docs/2.5/intro/safe-by-default

        Still a very cool feature. You might want to note the difference in the post, since the code could still technically lead to issues in people’s code.

        • jbogard

          TBH I cannot _stand_ the unbounded result set “Feature” of RavenDB. The only places in my apps where we need to bound results are search pages. Everywhere else, limiting results is just confusing as hell for users, forcing me to implement paging in places where I didn’t expect

          • khalidabuhakmeh

            Yeah it is one of the big gotchas for most developers coming into RavenDB for the first time. You can always up it, but when is enough “enough”: 200, 400, 1000, 10000.

            To get back to EF, I’ve found that doing two queries sometimes speeds up performance. The way the data comes back from queries can be gross, normally a big fused table that needs to be parsed. So I would suggest people “think” about why they are doing a projection.

            Projections are as much about limiting data retrieval as much as they are about reducing request to the database. Devs should take both of those into consideration.

  • Mike Cole

    1) Is it safe to assume this works the same in NHibernate, since you included the NHibernate tag?

    2) I never thought about using ViewBags to store the IDs rather than storing them in the ViewModel and using hidden fields to persist. I never quite felt right doing that since a malicious user could potentially modify a hidden field. Nice!

    • Joe

      What am I missing on #2? Don’t you still have to move the ID from the ViewBag to a form field to get it to POST?

      • jbogard

        That’s why I use the strongly-typed views and avoid ViewBag altogether.

        • Mike Cole

          On line 11 and line 17 of your first code sample you are assigning IDs to a ViewBag. I assumed you were doing it to avoid saving the IDs to a hidden field, but then Joe schooled me on the ViewBag’s lifetime.

  • Pingback: Using AutoMapper to perform LINQ aggregations | Jimmy Bogard's Blog

  • Pingback: Migrating from NHibernate to Entity Framework | Jimmy Bogard's Blog

  • http://six.dev-heaven.net Sickboy

    Thanks for the headsup regarding auto include when accessing lazy navigation properties in the select queries, and that it works with AutoMapper.
    And for providing AutoMapper with Projection support ;-)

  • Mike Cole

    I used this recently and was surprised to find that Project().To() returns an IQueryable instead of an populated result. Definitely cool because you could potentially combine with the Future() functionality from EntityFramework.Extended. It’s a fun time to be an EF user.