How to generate a LINQ query for your DTOs

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 mapping from one to another that can be used by NHibernate LINQ provider.

 

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

 

Queryable Extensions

Automapper provides a solution to this proble: (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.

Example

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

4. Query by hand (see query above)

   var postDto =      session.Query
         
          ().Where(post => post.Id == id) .Project().To
          
           () .Single();
          
         

5. Observe the generated SQL:

   select      blog1_.Name as col_0_0_, post0_.Title as col_1_0_, post0_.Body as col_2_0_ from Post post0_ left outer join Blog blog1_ on post0_.Blog=blog1_.Id where post0_.Id=@p0; @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 .

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