Query and map complex objects

Question

My goal is to query and map complex objects with as little overhead as possible. I am working with a large database with lots of related tables. I am trying to use LINQ select and projection to select only the necessary information i need to make the object.

This is the original query I had which was fast and worked great.

List<ClientDTO> clientList = dbClients.Select(client =>
new ClientDTO
{
    ID = client.ClientID,
    FirstName = client.FirstName,
    LastName = client.LastName,
    //etc....
    Products = client.Products
        .Select(prod => new ProductDTO
        {
            ID = prod.ID,
            DateOfTransaction = prod.Date,
            //etc...
        }).ToList(),
    Items = client.Items
        .Select(item => new ItemDTO
        {
            ID = item.ID,
            Date = item.Date,
            //etc...
        }
});

Keep in mind the Client table has over 50 related tables, so this query worked great in that it only selected the fields I needed to make the object.

Now what I needed to do is make mappers for these Objects and try to build the same query statement but using the mappers this time. Here is what I ended up with.

List<ClientDTO> clients = dbClients.ProjectToClientDTO();

Using these Mappers

public static List<ClientDTO> ProjectToClientDTO(this IQueryable<Clients> query)
{
    var clientList = query.Select(client => new
    {
        ID = client.ClientID,
        FirstName = client.FirstName,
        LastName = client.LastName,
        //etc...
        Products = client.Products.AsQueryable().ProjectToProductDTO().ToList(),
        Items = client.Items.AsQueryable().ProjectToItemDTO().ToList()
    }

    List<ClientDTO> dtoClientList = new List<ClientDTO>();
    foreach (var client in clientList)
    {
        ClientDTO clientDTO = new ClientDTO();

        clientDTO.EncryptedID = EncryptID(client.ID, client.FirstName, client.LastName);
        //etc...
        clientDTO.Products = client.Products;
        clientDTO.Items = client.Items;
    }
    return dtoClientList;
}

public static IQueryable<ProductDTO> ProjectToProductDTO(this IQueryable<Products> query)
{
    return query.Select(prod => new ProductDTO
    {
        ID = prod.ID,
        DateOfTransaction = prod.Date,
        //etc...
    });
}

public static IQueryable<ItemDTO> ProjectToItemDTO(this IQueryable<Items> query)
{
    return query.Select(item => new ItemDTO
    {
        ID = item.ID,
        Date = item.Date,
        //etc...
    });
}

After trying to run this I get the following error.

LINQ to Entities does not recognize the method 'ProjectToProductDTO(IQueryable[Products])', and this method cannot be translated into a store expression."}

Can I make LINQ invoke these methods to build the query? Or is there a better way to query and map these objects without grabbing 50+ tables of unnecessary data for hundreds of clients?

UPDATE

User Tuco mentioned that I could try looking into expression trees. After reading up on them for a bit I came up with this.

public static Expression<Func<Product, ProductDTO>> test = prod =>
        new ProductDTO()
        {
            ID= prod.ID,
            Date= prod.Date,
            //etc...
        };

And use it as such.

Products = client.Products.Select(prod => test.Compile()(prod)),

But running this I receive this error.

The LINQ expression node type 'Invoke' is not supported in LINQ to Entities

EF doesn't return all the graph by default, you have to use Include for each navigation property — The One, Jul 25 '17 at 15:29
Isn't this called lazy loading, and isn't it enabled by default? — Moe, Jul 25 '17 at 15:36
Why do you "need" to use mappers? You can't put user-defined functions into other queries in general, but you can use something like LINQKit to do lambda expansions from user-defined functions. — NetMage, Jul 25 '17 at 18:54

score 2 · Accepted Answer · answered Jul 27 '17 at 16:57

You are very close with your 2nd approach!

Let's say you define the projection of the product entity to the DTO (the mapper as you call it) like you did:

Expression<Func<Product, ProductDTO>> productProjection = prod => new ProductDTO
{
    ID = prod.ID,
    DateOfTransaction = prod.Date
    // ...
};

and the projection of the client entity to it's DTO like this (slightly simpler, but logically equivalent to what you did):

Expression<Func<Client, ClientDTO>> clientProjection = client => new ClientDTO
{
    ID = client.ClientID,
    FirstName = client.FirstName,
    // ...
    Products = client.Products.Select(productProjection.Compile()).ToList(),
    // ...
};

The compiler let's you do so, but the queryable will not understand that. However what you have achieved is that the productProjection is somehow contained in the expression tree. All you have to do is some expression manipulation.

If you look at the subtree the compiler builds for the argument to .Select you'll find a MethodCallExpression - the call to .Compile(). It's .Object expression - the thing that is to be compiled - is a MemberExpression accessing a field named productProjection(!) on an ConstantExpression containing an instance of an oddly named compiler generated closure class.

So: Find .Compile() calls and replace them with what would be compiled, ending up with the very expression tree you had in your original version.

I'm maintaining a helper class for expression stuff called Express. (See another answer that deals with .Compile().Invoke(...) for a similar situation).

clientProjection = Express.Uncompile(clientProjection);
var clientList = dbClients.Select(clientProjection).ToList();

Here's the relevant snipped of the Express class.

public static class Express
{
    /// <summary>
    /// Replace .Compile() calls to lambdas with the lambdas themselves.
    /// </summary>
    public static Expression<TDelegate> Uncompile<TDelegate>(Expression<TDelegate> lambda)
    => (Expression<TDelegate>)UncompileVisitor.Singleton.Visit(lambda);

    /// <summary>
    /// Evaluate an expression to a value.
    /// </summary>
    private static object GetValue(Expression x)
    {
        switch (x.NodeType)
        {
            case ExpressionType.Constant:
                return ((ConstantExpression)x).Value;
            case ExpressionType.MemberAccess:
                var xMember = (MemberExpression)x;
                var instance = xMember.Expression == null ? null : GetValue(xMember.Expression);
                switch (xMember.Member.MemberType)
                {
                    case MemberTypes.Field:
                        return ((FieldInfo)xMember.Member).GetValue(instance);
                    case MemberTypes.Property:
                        return ((PropertyInfo)xMember.Member).GetValue(instance);
                    default:
                        throw new Exception(xMember.Member.MemberType + "???");
                }
            default:
                // NOTE: it would be easy to compile and invoke the expression, but it's intentionally not done. Callers can always pre-evaluate and pass a member of a closure.
                throw new NotSupportedException("Only constant, field or property supported.");
        }
    }

    private sealed class UncompileVisitor : ExpressionVisitor
    {
        public static UncompileVisitor Singleton { get; } = new UncompileVisitor();
        private UncompileVisitor() { }

        protected override Expression VisitMethodCall(MethodCallExpression node)
        {
            if (node.Method.Name != "Compile" || node.Arguments.Count != 0 || node.Object == null || !typeof(LambdaExpression).IsAssignableFrom(node.Object.Type))
                return base.VisitMethodCall(node);
            var lambda = (LambdaExpression)GetValue(node.Object);
            return lambda;

            // alternatively recurse on the lambda if it possibly could contain .Compile()s
            // return Visit(lambda); // recurse on the lambda
        }
    }
}

score 1 · Answer 2 · answered Jul 25 '17 at 18:55

1

Use LINQKit to expand user-defined lambda functions into the lambdas needed in the query:

https://github.com/scottksmith95/LINQKit

answered Jul 25 '17 at 18:55

NetMage

26,163
3
34
55

Query and map complex objects

2 Answers2