Tag Archives: NHibernate

AutoMapper queryable extensions

How to generate a LINQ query for your DTOs

AutoMapper is a really cool library that allows us to map one object to another, e.g. when passing objects through layers of our application, where we work with different objects in different layers of our app and we have to map them from one layer to another, e.g. from business object to viewmodel.

All is good and well for POCO, not so much for entity objects. The automapper tries to map everything using reflection, so properties like Project.Code can turn to ProjectCode, but that is troublesome with ORM, where querying an object means loading another entity from the database.

I am using a NHibernate linq provider that only gets columns we actually ask from the database, so it would be nice to have a DTO type, entity type and magically create a linq expression mapping from one to another that can be used by NHibernate LINQ provider.

// Blog entity
public class Blog {
  public virtual int Id {get;set;}
  public virtual string Name {get;set;}

// Post entity
public class Post {
  public virtual int Id {get;set;}
  public virtual string Title {get;set;}
  public virtual DateTime Created {get;set;}
  public virtual string Body {get;set;}
  public virtual Blog Blog {get;set;} 

public class PostDto {
  public string BlogName {get;set;} 
  public string Title {get;set;}
  public string Body {get;set;}

public class BlogRepository {
  private readonly ISession session;

  public PostDto GetPost(int id)
    return (
      from post in session.Query<Post>()
      where post.Id == id
        new PostDto                  // This is an expression I want to generate
        {                            // in this case, I have 3 properties,
          BlogName = post.Blog.Name, // in my project, I have 5-30 in each entity
          Title = post.Title,        // and many entities. Repeatable code that
          Body = post.Body           // should be generated.
        }                            //

Remember, such expression will require only necessary fiels, so Id or Created won’t be part of SQL query (see NHibernate Linq query evaluation process for more info).


Queryable Extensions

Automapper provides a solution to this proble: queryable extensions (QE). They allow us to create such expression and they even solve SELECT N+1 problem. It is no panacea, but it solves most of my trouble.

Notice the key difference, normal automapping will traverse object graph and return a mapped object, QE will only generate a mapping expression.


I will provide an example using the entities above:

  1. NuGet package for AutoMapper, the QueryableExtensions are part of the package and are in AutoMapper.QueryableExtensions namespace
  2. Create a test
  3. Create a mapping
    Mapper.CreateMap<Post, PostDto>();
  4. Query by hand (see query above)
    var postDto = 
       session.Query<Post>().Where(post => post.Id == id)
  5. Observe the generated SQL:
        blog1_.Name as col_0_0_,
        post0_.Title as col_1_0_,
        post0_.Body as col_2_0_ 
        Post post0_ 
    left outer join
        Blog blog1_ 
            on post0_.Blog=blog1_.Id 
    @p0 = 1 [Type: Int32 (0)]

    It is no different that the SQL generated by the hand made query. It only queries what is necessary without boilerplate code.

  6. Remove boilerplate code from your app.

You can also do a more difficult transformations, although QE are slightly more limited than in-memory AutoMapper capabilities, go and read the wiki.

This is really cool extension that will remove quite a lot of boilerplate code, so give it a try!

Further reading