sql - In Entity Framework is it more efficient / preferred to create projections rather than selecting full entities? -

i working on application, trying improve performance. doing own profiling , testing, know if there "consensus" or known best practice.

in old sql days, 1 of main things improve efficiency not select data aren't going consume. i'm trying go down route ef6.

in particular case, have master-detail-detail relationship need render data parent, child, , grandchild on screen.

my application n-tier mvc front end, , web-api rest backend. these entities going serialized json, sent on rest connection mvc controller, rendered screen. in case not updating entities flow, don't need worry merging partial entities repository (in cases, send on full entity ease of maintenance)

so, original straightforward ef code wrote looks this

repository.getall()           .asnotracking()           .include("children")           .include("children.grandchildren")           .tolist(); 

however, consuming subset of properties of these entities, , of unused properties can rather large (big chunks of xml, etc)

here first pass @ trying project out fields need (for example here, have cut out , renamed of fields select improve readability, in general i'm using lets 5-20% of full entities)

var projection = repository.getall()             .asnotracking()             .select(r => new             {                 r.id,                 r.randomid,                 r.state,                 r.requesttype,                 r.createddate,                 r.createdby,                 children = r.children.select(r2 => new                 {                     r2.id,                     r2.status,                     grandchildren = r2.grandchildren.select(r3 => new                     {                         r3.id,                         r3.status,                         r3.grandchildtype                     })                 }),             }             ).tolist(); 

this using anonymous types (i believe required in ef there not way project named type?) (edit : apparently can project non-mapped named type, in case, return type of query mapped type. create dto, that's more code maintain)

so have concrete types. generate dtos had properties needed, don't think changes fundamental logic used, nor performance characteristics.

i tried standbys of automapper , valueinjecter, neither 1 seemed fit bill here (deep clone of heterogeneous types matching names) went dirty

var json = projection.select(jsonconvert.serializeobject).tolist();  var mapped = json.select(jsonconvert.deserializeobject<parent>).tolist(); 

this lame since going serialized again part of rest call. there way can override webapi calls returning serialized data, let me skip rehydration entity type (as of property names match, rest client should able rehydrate anonymous type if real type, same way snippet above does)

but seems lot of work, less maintainable code, more possible places have bugs, etc use case entity framework not seem want support. old school instincts can't let go of idea i'm selecting, serializing, , transferring whole lot of data i'm not going consume.

does produce sane sql under covers? worth double serialization? (assuming don't figure out how override webapi let me hand data)

i suppose other choice refactor entities unused properties in different sub entities can not include, lot of rework throughout system (versus being able surgically improve performance @ critical points) , seems poor choice design entities around orm happen using vs standard normalization rules etc.

use table splitting allows split table more 1 entity without modifying underlying table. "less accessed" properties can lazy loaded on demand or loaded eagerly, behaving other navigation property. note key here additional entities use pk fk primary entity