c# - Reusable Calculations For LINQ Projections In Entity Framework (Code First) -

my domain model has lot of complex financial data result of complex calculations on multiple properties of various entities. include these [notmapped] properties on appropriate domain model (i know, know - there's plenty of debate around putting business logic in entities - being pragmatic, works automapper , lets me define reusable dataannotations - discussion of whether or not not question).

this works fine long want materialize entire entity (and other dependent entities, either via .include() linq calls or via additional queries after materialization) , map these properties view model after query. problem comes in when trying optimize problematic queries projecting view model instead of materializing entire entity.

consider following domain models (obviously simplified):

public class customer {  public virtual icollection<holding> holdings { get; private set; }   [notmapped]  public decimal accountvalue  {   { return holdings.sum(x => x.value); }  } }  public class holding {  public virtual stock stock { get; set; }  public int quantity { get; set; }   [notmapped]  public decimal value  {   { return quantity * stock.price; }  } }  public class stock {  public string symbol { get; set; }  public decimal price { get; set; } } 

and following view model:

public class customerviewmodel {  public decimal accountvalue { get; set; } } 

if attempt project directly this:

list<customerviewmodel> customers = mycontext.customers  .select(x => new customerviewmodel()  {   accountvalue = x.accountvalue  })  .tolist(); 

i end following notsupportedexception: additional information: specified type member 'accountvalue' not supported in linq entities. initializers, entity members, , entity navigation properties supported.

which expected. - entity framework can't convert property getters valid linq expression. however, if project using exact same code within projection, works fine:

list<customerviewmodel> customers = mycontext.customers  .select(x => new customerviewmodel()  {   accountvalue = x.holdings.sum(y => y.quantity * y.stock.price)  })  .tolist(); 

so can conclude actual logic convertible sql query (i.e., there's nothing exotic reading disk, accessing external variables, etc.).

so here's question: there way @ make logic should convertible sql reusable within linq entity projections?

consider calculation may used within many different view models. copying projection in each action cumbersome , error prone. if calculation changes include multiplier? we'd have manually locate , change everywhere it's used.

one thing have tried encapsulating logic within iqueryable extension:

public static iqueryable<customerviewmodel> withaccountvalue(  iqueryable<customer> query) {  return query.select(x => new customerviewmodel()  {   accountvalue = x.holdings.sum(y => y.quantity * y.stock.price)  }); } 

which can used this:

list<customerviewmodel> customers = mycontext.customers  .withaccountvalue()  .tolist(); 

that works enough in simple contrived case this, it's not composable. because result of extension iqueryable<customerviewmodel> , not iqueryable<customer> can't chain them together. if had 2 such properties in 1 view model, 1 of them in view model, , other in third view model, have no way of using same extension 3 view models - defeat whole purpose. approach, it's or nothing. every view model has have exact same set of calculated properties (which case).

sorry long-winded question. prefer provide detail possible make sure folks understand question , potentially others down road. feel i'm missing here make of snap focus.

i did lot of research on last several days because it's been bit of pain point in constructing efficient entity framework queries. i've found several different approaches boil down same underlying concept. key take calculated property (or method), convert expression query provider knows how translate sql, , feed ef query provider.

i found following libraries/code attempted solve problem:

linq expression projection

http://www.codeproject.com/articles/402594/black-art-linq-expressions-reuse , http://linqexprprojection.codeplex.com/

this library allows write reusable logic directly expression , provides conversion expression linq query (since query can't directly use expression). funny thing it'll translated expression query provider. declaration of reusable logic looks this:

private static expression<func<project, double>> projectaverageeffectiveareaselector =  proj => proj.subprojects.where(sp => sp.area < 1000).average(sp => sp.area); 

and use this:

var proj1andaea =  ctx.projects   .asexpressionprojectable()   .where(p => p.id == 1)   .select(p => new    {      aea = utilities.projectaverageeffectiveareaselector.project<double>()    }); 

notice .asexpressionprojectable() extension set projection support. use .project<t>() extension on 1 of expression definitions expression query.

linq translations

http://damieng.com/blog/2009/06/24/client-side-properties-and-any-remote-linq-provider , https://github.com/damieng/linq.translations

this approach pretty similar linq expression projection concept except it's little more flexible , has several points extension. trade off it's little more complex use. still define reusable logic expression , rely on library convert query can use. see blog post more details.

delegatedecompiler

http://lostechies.com/jimmybogard/2014/05/07/projecting-computed-properties-with-linq-and-automapper/ , https://github.com/hazzik/delegatedecompiler

i found delegatedecompiler via blog post on jimmy bogard's blog. has been lifesaver. works well, architected, , requires lot less ceremony. not require define reusable calculations expression. instead, constructs necessary expression using mono.reflection decompile code on fly. knows properties, methods, etc. need decompiled having decorate them computedattribute or using .computed() extension within query:

class employee {  [computed]  public string fullname  {   { return firstname + " " + lastname; }  }  public string lastname { get; set; }  public string firstname { get; set; } } 

this can extended, nice touch. example, set notmapped data annotation instead of having explicitly use computedattribute.

once you've set entity, trigger decompilation using .decompile() extension:

var employees = ctx.employees  .select(x => new  {   fullname = x.fullname  })  .decompile()  .tolist();