Autoprojecting LINQ queries

Something I’ve been looking at adding to AutoMapper was the idea of doing automatic query projection in the Select query projection in LINQ statements.  One downside of AutoMapper is that projection from domain objects still forces the entire domain object to be queried and loaded.  For a lot of read-only scenarios, loading up a tracked, persistent entity is a bit of a waste.  And unless you’re doing CQRS with read-specific tables, you’re doing projection somehow from the write tables.

But many LINQ query providers help with this by parsing expression trees to craft specific SQL queries projecting straight down at the SQL layer.  Additionally, projecting in to these DTOs skips loading persistent, tracked entities into memory.  Unfortunately, we’re then forced to write our boring LHS-RHS code when we drop to this layer:

return Session.Linq<Conference>()
    .Select(c => new ConferenceShowModel
        {
            Name = c.Name,
            AttendeeCount = c.AttendeeCount,
            SessionCount = c.SessionCount
        }
    )
    .ToArray();

It’s this pointless, repetitive mapping code that AutoMapper was intended to avoid.  Underneath the covers, the Select clause is just a simple expression:

public static IQueryable<TResult> Select<TSource, TResult>(
    this IQueryable<TSource> source, 
    Expression<Func<TSource, TResult>> selector)

So our Select clause from the query earlier is really built from building up an expression tree.  Instead of building up an expression tree through the C# compiler, why don’t we just automatically build up one ourselves, based on the source-destination type?  That’s what I set out to do, at least in a very simplified, non-optimized model.  First, I created an extension method to IQueryable that lets me start to build a projection:

public static class QueryableExtensions
{
    public static IProjectionExpression Project<TSource>(
        this IQueryable<TSource> source)
    {
        return new ProjectionExpression<TSource>(source);
    }

The IProjectionExpression objects lets me then specify a model to project to:

public interface IProjectionExpression
{
    IQueryable<TResult> To<TResult>();
}

The idea being that I’ll be able to Project().To<MyDto>().  I couldn’t do the projection in one fell swoop with Project<MyDto>().  Generic type parameter inference doesn’t let you do partial parameter inference, so this chaining lets me get away with not having to redundantly specify the original type.  The ProjectionExpression implementation then builds out our expression tree dynamically:

public class ProjectionExpression<TSource>
    : IProjectionExpression
{
    private readonly IQueryable<TSource> _source;

    public ProjectionExpression(IQueryable<TSource> source)
    {
        _source = source;
    }

    public IQueryable<TResult> To<TResult>()
    {
        Expression<Func<TSource, TResult>> expr = BuildExpression<TResult>();

        return _source.Select(expr);
    }

    public static Expression<Func<TSource, TResult>> BuildExpression<TResult>()
    {
        var sourceMembers = typeof(TSource).GetProperties();
        var destinationMembers = typeof(TResult).GetProperties();

        var name = "src";

        var parameterExpression = Expression.Parameter(typeof(TSource), name);

        return Expression.Lambda<Func<TSource, TResult>>(
            Expression.MemberInit(
                Expression.New(typeof(TResult)),
                destinationMembers.Select(dest => Expression.Bind(dest,
                    Expression.Property(
                        parameterExpression,
                        sourceMembers.First(pi => pi.Name == dest.Name)
                    )
                )).ToArray()
                ),
            parameterExpression
            );
    }
}

It’s not very optimized, as it builds out the expression tree every time.  But that’s an easy enhancement, as once the expression tree is built from TSource –> TDestination, it could be statically cached and re-used.  But once I have this in place, my LINQ query becomes greatly simplified:

public ConferenceShowModel[] List()
{
    return Session.Linq<Conference>()
        .Project().To<ConferenceShowModel>()
        .ToArray();
}

Just one Project().To() method call, and the expression for the projection statement is automatically built up, assuming that all properties match by name.  This is a simplified version of what happens in AutoMapper, so you don’t see all of the things that the underlying LINQ query provider supports with projections.

But it’s a start.

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, C#, LINQ. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • http://blog.mikeobrien.net mob

    So if I understand you correctly, the NH3 Linq provider will skip creating/hydrating an entity when it sees that the expression tree contains a projection?

  • http://www.lostechies.com/members/bogardj/default.aspx bogardj

    @mob

    Yep, the underlying SQL is changed to project straight into this DTO.

  • http://eric.polerecky.com Eric

    This does not run the projection through AutoMapper correct?

  • http://www.lostechies.com/members/bogardj/default.aspx bogardj

    @Eric

    Nope, it passes the expression tree straight to the underlying query provider. So it actually works against any query provider, NH, EF etc

  • Ryan

    This is a feature that I’ve always wanted, especially with the release NH3! It would be very nice to see this in AutoMapper with the same abilities as regular mappings, like flattening.

  • Tim

    How would you handle referential data off of the Conference table? Say there’s a collection of Sessions associated with a Conference, is there a way to pull them back also as part of the projection to ConferenceShowModel?

  • http://www.lostechies.com/members/bogardj/default.aspx bogardj

    @Tim

    It’s not handled now, this is just a start. I’m looking at alllll that stuff though :)

  • http://blog.hitchman.net Andy Hitchman

    We were having exactly this discussion even as your post was sitting in my unread queue!

    Good stuff. We’re on board.

    Here are some mapping override enhancements: https://gist.github.com/824585

  • http://lunaverse.wordpress.com Tim Scott

    I’ve been favoring flattening projections straight from the DB instead of in memory a good while now (NHib 3, before that NHbernate.Linq, before that AliasatoBeanTransformer) for all the reasons you mention. Sucks to hand roll the mapping, but it’s the best way to keep the domain out of the read side of things short of CQRS. I knew if I waited long emough some one else would figure out how to streamline this approach and write all that sticky expression buildng code. Go Jimmy!!!

  • Sam Critchley

    Has anyone tried this (successfully) with LLBL?

  • Pingback: Efficient transactional processing | Jimmy Bogard

  • http://nikosbaxevanis.com/bonus-bits/ Nikos Baxevanis

    Great idea! Btw, does it work with collections?

    • Anonymous

      Does it? No. Could it? Sure!

  • http://www.devtrends.co.uk/blog Paul Hiles

    Hope that you do not mind. I have extended your code adding caching and basic flattening capabilities. See the (provocatively-named :-) ) post at  http://devtrends.co.uk/blog/stop-using-automapper-in-your-data-access-code

  • http://twitter.com/PostHopeLive Paul Barton

    Great work! Is this something you are going to put on github or codeplex?

    • Anonymous

      I was planning on rolling it in to AutoMapper in a future version, but right now, this is all I have.

  • Vasiliy Ruzanov

    Ni

    • Jimmy Bogard

      Ah yes, thanks for the clarification! Queries, views, tables, all SQL-specific. Thanks!

      • http://profiles.google.com/nitin.jaysing Nitin Sawant

        Thanks, Does this feature is available now in Automapper?

  • http://profiles.google.com/nitin.jaysing Nitin Sawant

    When Implementing this
    I am getting errors in my asp.net 3.5 site
    http://stackoverflow.com/questions/8166776/cannot-convert-from-memberassignment-to-memberbinding

    -NJS

    • Anonymous

      Can you file an issue on GitHub? Find out how here: http://automapper.org – I’ll need the source/destination types and your configuration code.

  • Pingback: Mapeando simple objects e complex objects com AutoMapper « Rafael Zaccanini .NET

  • Arturo Hernandez

    It’s been a while since this was posted. Did you decide against adding this on to Automapper? Do you know of any alternatives?

    • jbogard

      Nah, this is part of AutoMapper now!

  • Brad Rembielak

    I was reading the release notes for Automapper 3.0 about Linq. Is .Project.To still necessary and does Automapper now do this automatically for us now?

    • jbogard

      Yes, but I’m not sure how I would do it automatically. Ideas?

  • Pingback: [.NETWorld] Entity Framework projections to Immutable Types (IEnumerable vs IQueryable) | sudo man