c# - Query and map complex objects -

my goal query , map complex objects little overhead possible. working large database lots of related tables. trying use linq select , projection select necessary information need make object.

this original query had fast , 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 client table has on 50 related tables, query worked great in selected fields needed make object.

now needed make mappers these objects , try build same query statement using mappers time. here ended 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 run following error.

linq entities not recognize method 'projecttoproductdto(iqueryable[products])', , method cannot translated store expression."}

can make linq invoke these methods build query? or there better way query , map these objects without grabbing 50+ tables of unnecessary data hundreds of clients?

update

user tuco mentioned try looking expression trees. after reading on them bit came this.

public static expression<func<product, productdto>> test = prod =>         new productdto()         {             id= prod.id,             date= prod.date,             //etc...         }; 

and use such.

products = client.products.select(prod => test.compile()(prod)), 

but running receive error.

the linq expression node type 'invoke' not supported in linq entities

you close 2nd approach!

let's define projection of product entity dto (the mapper call it) did:

expression<func<product, productdto>> productprojection = prod => new productdto {     id = prod.id,     dateoftransaction = prod.date     // ... }; 

and projection of client entity it's dto (slightly simpler, logically equivalent 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 so, queryable not understand that. have achieved productprojection somehow contained in expression tree. have expression manipulation.

if @ subtree compiler builds argument .select you'll find methodcallexpression - call .compile(). it's .object expression - thing compiled - memberexpression accessing field named productprojection(!) on constantexpression containing instance of oddly named compiler generated closure class.

so: find .compile() calls , replace them would compiled, ending expression tree had in original version.

i'm maintaining helper class expression stuff called express. (see answer deals .compile().invoke(...) similar situation).

clientprojection = express.uncompile(clientprojection); var clientlist = dbclients.select(clientprojection).tolist(); 

here's relevant snipped of express class.

public static class express {     /// <summary>     /// replace .compile() calls lambdas lambdas themselves.     /// </summary>     public static expression<tdelegate> uncompile<tdelegate>(expression<tdelegate> lambda)     => (expression<tdelegate>)uncompilevisitor.singleton.visit(lambda);      /// <summary>     /// evaluate expression 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: easy compile , invoke expression, it's intentionally not done. callers can pre-evaluate , pass member of 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 lambda if possibly contain .compile()s             // return visit(lambda); // recurse on lambda         }     } }