Code a SQL projection and mapping at the same time?

Question

This works to carve out a DDL object from an Address object from our database:

public class DDL {
    public int?   id   { get; set; }
    public string name { get; set; }
}

List<DDL> mylist = Addresses
    .Select( q => new DDL { id = q.id, name = q.name })
    .ToList();

However, we'd like to keep our POCO to ViewModel mappings in a single place outside of our MVC controller code. We'd like to do something like this:

List<DDL> mylist = Addresses
    .Select( q => new DDL(q))  // <-- constructor maps POCO to VM
    .ToList();

But SQL cannot use the constructor function. The object initializer above doesn't use functions to map fields. Of course you could do .AsEnumerable().Select( q => new DDL(q)), but this selects all the fields in SQL (including the data), sends it to C#, then C# carves out the fields we need (terribly inefficient to transfer data we don't need.)

Any suggestions? We happen to be using Entity Framework 6 to pull data.

Answer 1

You can use anonymous types to restrict what to select from the DB and then use those fields to construct your object :

List<DDL> mylist = Addresses
    .Select( q => new { id, name })
    .AsEnumerable()
    .Select(i => new DDL(i.id, i.name) // <- On the Enumerable and not on the Queryable
    .ToList();

Answer 2

All you need is to define the expression somewhere and use it. For example, in your ViewModel as a static read-only field.

public class SomeViewModel
{
    public static readonly Expression<Func<SomeEntity, SomeViewModel>> Map = (o) => new SomeViewModel
    {
        id = o.id,
        name = o.name
    }

    public int id { get; set; }
    public string name { get; set; }
}

// Somewhere in your controller
var mappedAddresses = Addresses.Select(SomeViewModel.Map);

I personally made myself a little static Mapper that keeps all the maps and use them for me. The maps are declared in a static initializer in all my ViewModels. The result gives me something that feels like AutoMapper, yet doesn't require the lib or the complicated mapping code (but also won't do any magic for you).

I can write something like this:

MyCustomMapper.Map<Entity, ViewModel>(entity);

and it's overloaded to accept IEnumerables, IQueryables and a single ViewModel. I also added overloads with only 1 generic type (the entity) that accept a type parameter. This was a requirement for me.

Answer 3

Are you against using 3rd party libraries? Automapper's QueryableExtensions does exactly what you want.

List<DDL> mylist = Addresses
    .Project().To<DDL>()
    .ToList();

It even has nice features like being able to filter on the transformed object and that filter being performed server side.

List<DDL> mylist = Addresses
    .Project().To<DDL>()
    .Where(d => d.name = "Smith") //This gets translated to SQL even though it was performed on DDL.
    .ToList();