Reusable calculations for LINQ predictions in an entity structure (first code)

Question

My domain model has a lot of complex financial data, which is the result of fairly complex calculations on several properties of various objects. I usually include them as properties of [NotMapped] in a suitable domain model (I know, I know there is a lot of controversy about placing business logic in your entities), being pragmatic, it just works well with AutoMapper and allows me to define reusable DataAnnotations - discussion whether this is good or not is not my question).

This works fine as long as I want to materialize the entire object (and any other dependent objects either through .Include() LINQ calls, or through additional queries after materialization), and then map these properties to the view model after the query. The problem arises when trying to optimize problematic queries by projecting into the presentation model instead of materializing the entire object.

Consider the following domain models (obviously simplified):

 public class Customer { public virtual ICollection<Holding> Holdings { get; private set; } [NotMapped] public decimal AccountValue { get { return Holdings.Sum(x => x.Value); } } } public class Holding { public virtual Stock Stock { get; set; } public int Quantity { get; set; } [NotMapped] public decimal Value { get { return Quantity * Stock.Price; } } } public class Stock { public string Symbol { get; set; } public decimal Price { get; set; } } 

And the following presentation model:

 public class CustomerViewModel { public decimal AccountValue { get; set; } } 

If I try to do it right this way:

 List<CustomerViewModel> customers = MyContext.Customers .Select(x => new CustomerViewModel() { AccountValue = x.AccountValue }) .ToList(); 

The result is the following NotSupportedException : Additional information: The specified type member 'AccountValue' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported. Additional information: The specified type member 'AccountValue' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

Expected. I get this: Entity Framework cannot convert getters properties to a valid LINQ expression. However, if I project the exact code, but within the projection, it works fine:

 List<CustomerViewModel> customers = MyContext.Customers .Select(x => new CustomerViewModel() { AccountValue = x.Holdings.Sum(y => y.Quantity * y.Stock.Price) }) .ToList(); 

So, we can conclude that the actual logic is converted to an SQL query (for example, there is nothing exotic like reading from disk, accessing external variables, etc.).

So here's the question: is there any way to make logic that should be convertible to SQL reusable in LINQ for entities?

Note that this calculation can be used in many different look models. Copying it onto a projection in each action is cumbersome and error prone. What if the multiplier is included in the calculation? We must manually find and modify it wherever it was used.

One thing I tried is encapsulating logic in an IQueryable extension:

 public static IQueryable<CustomerViewModel> WithAccountValue( this IQueryable<Customer> query) { return query.Select(x => new CustomerViewModel() { AccountValue = x.Holdings.Sum(y => y.Quantity * y.Stock.Price) }); } 

What can be used as follows:

 List<CustomerViewModel> customers = MyContext.Customers .WithAccountValue() .ToList(); 

This works well in a simple far-fetched case, but it is not complicated. Since the result of the extension is IQueryable<CustomerViewModel> and not a IQueryable<Customer> , you cannot bind them together. If I had two of these properties in one presentation model, one in the other presentation model, and then the other in the third presentation model, I would have no way to use the same extension for all three presentation models - to defeat whole goal. With this approach, all this or nothing. Each presentation model must have exactly the same set of calculated properties (which is rarely the case).

Sorry for the long question. I prefer to provide as much detail as possible so that people can understand the issue and potentially help others in the future. I just feel like I'm missing something that would make all of this in focus.

Answer 1

I have done a lot of research over the past few days because it was a small point in creating effective Entity Framework queries. I found several different approaches that essentially boil down to the same basic concept. The key is to take the computed property (or method), convert it to Expression , which the query provider knows how to translate into SQL, and then pass it to the EF query provider.

I found the following libraries / code that tried to solve this problem:

Projecting a LINQ Expression

http://www.codeproject.com/Articles/402594/Black-Art-LINQ-expressions-reuse and http://linqexprprojection.codeplex.com/

This library allows you to write reuse logic directly as Expression , and then provides a conversion to get that Expression into your LINQ query (since the query cannot directly use Expression ). The funny thing is that it will be transferred back to Expression by the request provider. The declaration of your reusable logic is as follows:

 private static Expression<Func<Project, double>> projectAverageEffectiveAreaSelector = proj => proj.Subprojects.Where(sp => sp.Area < 1000).Average(sp => sp.Area); 

And you use it as follows:

 var proj1AndAea = ctx.Projects .AsExpressionProjectable() .Where(p => p.ID == 1) .Select(p => new { AEA = Utilities.projectAverageEffectiveAreaSelector.Project<double>() }); 

Take a look at the .AsExpressionProjectable() extension for custom projection support. Then you use the .Project<T>() extension in one of the Expression definitions to get Expression in the request.

LINQ Translations

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

This approach is quite similar to the concept of projecting a LINQ expression, except that it is slightly more flexible and has several points for expansion. The tradeoff is that it is also a bit more difficult to use. Essentially, you still define your reuse logic as Expression , and then rely on the library to transform it into something that the query can use. See the blog for more details.

DelegateDecompiler

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

I found DelegateDecompiler through a blog post on Jimmy Bogard's blog. It was a lifeguard. It works well, is well structured, and requires much less ceremonies. This does not require you to define your reuse calculations as Expression . Instead, it creates the necessary Expression using Mono.Reflection to decompile your code on the fly. He knows what properties, methods, etc. You need to decompile if you decorate them with ComputedAttribute or with the .Computed() extension in the request:

 class Employee { [Computed] public string FullName { get { return FirstName + " " + LastName; } } public string LastName { get; set; } public string FirstName { get; set; } } 

It can also be easily expanded, which is nice. For example, I configured it to search for NotMapped data NotMapped instead of explicitly using ComputedAttribute .

Once you have set up your object, you simply start decompiling with the .Decompile() extension:

 var employees = ctx.Employees .Select(x => new { FullName = x.FullName }) .Decompile() .ToList(); 

Answer 2

You can encapsulate the logic by creating a class that contains the original Entity and an additional computed property. Then you create helper methods that execute in the class.

For example, if we tried to calculate the tax for the object Employee and Contractor , we could do this:

---

 //This is our container for our original entity and the calculated field public class PersonAndTax<T> { public T Entity { get; set; } public double Tax { get; set; } } 

---

 public class PersonAndTaxHelper { // This is our middle translation class // Each Entity will use a different way to calculate income private class PersonAndIncome<T> { public T Entity { get; set; } public int Income { get; set; } } 

---

Income Calculation Methods

  public static IQueryable<PersonAndTax<Employee>> GetEmployeeAndTax(IQueryable<Employee> employees) { var query = from x in employees select new PersonAndIncome<Employee> { Entity = x, Income = x.YearlySalary }; return CalcualateTax(query); } public static IQueryable<PersonAndTax<Contractor>> GetContratorAndTax(IQueryable<Contractor> contractors) { var query = from x in contractors select new PersonAndIncome<Contractor> { Entity = x, Income = x.Contracts.Sum(y => y.Total) }; return CalcualateTax(query); } 

---

Tax calculation is determined in one place

  private static IQueryable<PersonAndTax<T>> CalcualateTax<T>(IQueryable<PersonAndIncome<T>> personAndIncomeQuery) { var query = from x in personAndIncomeQuery select new PersonAndTax<T> { Entity = x.Entity, Tax = x.Income * 0.3 }; return query; } } 

---

Our view model projections using the Tax property

  var contractorViewModel = from x in PersonAndTaxHelper.GetContratorAndTax(context.Contractors) select new { x.Entity.Name, x.Entity.BusinessName x.Tax, }; var employeeViewModel = from x in PersonAndTaxHelper.GetEmployeeAndTax(context.Employees) select new { x.Entity.Name, x.Entity.YearsOfService x.Tax, };