实体框架中LINQ投影的可重用计算（代码优先） [英] Reusable Calculations For LINQ Projections In Entity Framework (Code First)

问题描述

我的域模型有许多复杂的财务数据，这是由各种实体的多个属性计算相当复杂的结果。我通常将这些作为<？c $ c> [NotMapped] 属性包含在适当的域模型中（我知道，我知道 - 关于将业务逻辑放在实体中有很多争议 - 务实，它使用AutoMapper可以很好地使用，并允许我定义可重复使用的 DataAnnotations - 讨论这是否是好的不是我的问题）。

只要我想通过 .Include（） LINQ调用或通过其他查询来实现整个实体（以及任何其他依赖实体）实现后），然后在查询后将这些属性映射到视图模型。当尝试通过投影到视图模型而不是实现整个实体来优化问题查询时，问题出现。

考虑以下域模型（显然简化）：

  public class Customer 
 {
 public virtual ICollection< Holding>控股{get;私人集合
 
 [NotMapped] 
 public decimal AccountValue 
 {
 get {return Holdings.Sum（x => x.Value）; 
} 
} 
 
 public class Holding 
 {
 public virtual Stock stock {get;组; } 
 public int数量{get;组; 
 
 [NotMapped] 
 public decimal值
 {
 get {return Quantity * Stock.Price; } 
} 
} 
 
 public class Stock 
 {
 public string Symbol {get;组; } 
 public decimal价格{get;组; } 
} 
  

以下视图模型：

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

如果我试图直接投影如下：

 列表< CustomerViewModel> customers = MyContext.Customers 
 .Select（x => new CustomerViewModel（）
 {
 AccountValue = x.AccountValue 
}）
 .ToList（）; 
  

我最终得到以下 NotSupportedException ： 附加信息：LINQ to Entities不支持指定的类型成员AccountValue。只支持初始化，实体成员和实体导航属性。

我得到它 - 实体框架不能将属性getter转换为有效的LINQ表达式。但是，如果我使用完全相同的代码来投影，但在投影中，则可以正常工作：

 列表与LT; CustomerViewModel> customers = MyContext.Customers 
 .Select（x => new CustomerViewModel（）
 {
 AccountValue = x.Holdings.Sum（y => y.Quantity * y.Stock.Price ）
}）
 .ToList（）; 
  

所以我们可以得出结论，实际逻辑可转换为SQL查询（也就是说，没有什么异国情调，从磁盘读取，访问外部变量等）。

所以这里的问题是：有什么办法完全可以将应该的逻辑转换为可重用于LINQ中的SQL到实体预测？

考虑到这种计算可能在许多不同的视图模型中使用。将其复制到每个动作中的投影是麻烦和容易出错的。如果计算改变为包括乘数怎么办？我们必须手动找到并更改它在任何地方使用。

我曾尝试过的一件事是将逻辑封装在一个 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）
}）; 
} 
  

可以这样使用：

 列表< CustomerViewModel> customers = MyContext.Customers 
 .WithAccountValue（）
 .ToList（）; 
  

在这样一个简单的设计案例中工作得很好，但不能组合。因为扩展的结果是一个 IQueryable< CustomerViewModel> 而不是一个 IQueryable< Customer> 他们在一起如果我在一个视图模型中有两个这样的属性，其中一个在另一个视图模型中，然后另一个在第三个视图模型中，我将无法对所有三个视图模型使用相同的扩展名，这将会击败整个目的。通过这种方法，这是全部或全部。每个视图模型必须具有完全相同的计算属性集（这是很少的情况）。

对于长期存在的问题，对不起。我更愿意提供尽可能多的细节，以确保人们了解这个问题，并可能帮助别人走下坡路。我只是觉得我在这里缺少一些东西，这将使所有这一切成为焦点。

解决方案

我做了很多最近几天对此进行了研究，因为在构建高效的实体框架查询方面已经有一点痛苦。我发现了几种不同的方法，这些方法基本上都归结为相同的基本概念。关键是采取计算的属性（或方法），将其转换为表达式，查询提供程序知道如何转换为SQL，然后将其提供到EF查询提供者。

我发现以下试图解决这个问题的库/代码：

LINQ表情投影

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

此库允许您直接将可重用的逻辑写入 Expression ，然后提供转换，以便将 Expression 转换到LINQ查询中（因为查询不能直接使用表达式）。有趣的是，它将被查询提供者翻译成 Expression 。您的可重用逻辑的声明如下所示：

  private static Expression&FunC< Project，double>> projectAverageEffectiveAreaSelector = 
 proj => proj.Subprojects.Where（sp => sp.Area< 1000）.Average（sp => sp.Area）; 
  

您使用如下：

  var proj1AndAea = 
 ctx.Projects 
 .AsExpressionProjectable（）
 .Where（p => p.ID == 1）
 。选择（p => new 
 {
 AEA = Utilities.projectAverageEffectiveAreaSelector.Project< double>（）
}）; 
  

请注意 .AsExpressionProjectable（）设置投影支持。然后，您在 Expression 定义之一中使用 .Project< T>（）扩展名来获取表达式到查询中。

LINQ翻译

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

这种方法与LINQ表达式投影非常相似概念，除了它更灵活一些，并有几点扩展。折衷的是使用它也更复杂一些。实质上，您仍然将可重用逻辑定义为表达式，然后依靠库将其转换为查询可以使用的内容。

DelegateDecompiler

http://lostechies.com/jimmybogard/2014/ 05/07 / projection-calculated-properties-with-linq-and-automapper / 和 https：// github.com/hazzik/DelegateDecompiler

我通过Jimmy Bogard博客上的博文发现了DelegateDecompiler。它一直是救生员。它的工作原理很好，架构很好，而且需要更少的仪式。它不需要您将可重用计算定义为表达式。相反，它通过使用 Mono.Reflection 来构建必要的表达式来即时反编译代码。它知道哪些属性，方法等需要通过使用 ComputedAttribute 或使用 .Computed（）扩展名：

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

这也可以很容易地扩展，这是一个很好的感觉。例如，我设置它来查找 NotMapped 数据注释，而不必显式地使用 ComputedAttribute 。 / p>



设置实体后，您只需使用 .Decompile（）扩展名即可触发反编译： / p>



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




My domain model has a lot of complex financial data that is the result of fairly complex calculations on multiple properties of various entities. I generally include these as [NotMapped] properties on the appropriate domain model (I know, I know - there's plenty of debate around putting business logic in your entities - being pragmatic, it just works well with AutoMapper and lets me define reusable DataAnnotations - a discussion of whether this is good or not is not my question).

This works fine as long as I want to materialize the entire entity (and any other dependent entities, either via .Include() LINQ calls or via additional queries after materialization) and then map these properties to the view model after the query. The problem comes in when trying to optimize problematic queries by projecting to a view model instead of materializing the entire entity.

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 view model:

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

If I attempt to project directly like this:

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

I end up with 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.

Which is expected. I get it - Entity Framework can't convert the property getters into a valid LINQ expression. However, if I project using the exact same 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 convertible to a SQL query (I.e., there's nothing exotic like reading from disk, accessing external variables, etc.).

So here's the question: is there any way at all to make logic that should be convertible to SQL reusable within LINQ to entity projections?

Consider that this calculation may be used within many different view models. Copying it to the projection in each action is cumbersome and error prone. What if the calculation changes to include a multiplier? We'd have to manually locate and change it everywhere it's used.

One thing I have tried is encapsulating the logic within 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)
 });
}

Which can be used like this:

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

That works well enough in a simple contrived case like this, but it's not composable. Because the result of the extension is an IQueryable<CustomerViewModel> and not a IQueryable<Customer> you can't chain them together. If I had two such properties in one view model, one of them in another view model, and then the other in a third view model, I would have no way of using the same extension for all three view models - which would defeat the whole purpose. With this approach, it's all or nothing. Every view model has to have the exact same set of calculated properties (which is rarely the case).

Sorry for the long-winded question. I prefer to provide as much detail as possible to make sure folks understand the question and potentially help others down the road. I just feel like I'm missing something here that would make all of this snap into focus.

解决方案

I did a lot of research on this the last several days because it's been a bit of a pain point in constructing efficient Entity Framework queries. I've found several different approaches that all essentially boil down to the same underlying concept. The key is to take the calculated property (or method), convert it into an Expression that the query provider knows how to translate into SQL, and then feed that into the EF query provider.

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

LINQ Expression Projection

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

This library allows you to write your reusable logic directly as an Expression and then provides the conversion to get that Expression into your LINQ query (since the query can't directly use an Expression). The funny thing is that it'll be translated back to an Expression by the query provider. The declaration of your reusable logic looks like this:

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

And you use it like this:

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

Notice the .AsExpressionProjectable() extension to set up projection support. Then you use the .Project<T>() extension on one of your Expression definitions to get the Expression into the query.

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 pretty similar to the LINQ Expression Projection concept except it's a little more flexible and has several points for extension. The trade off is that it's also a little more complex to use. Essentially you still define your reusable logic as an Expression and then rely on the library to convert that into something the query can use. See the blog post 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 via the blog post on Jimmy Bogard's blog. It has been a lifesaver. It works well, is well architected, and requires a lot less ceremony. It does not require you to define your reusable calculations as an Expression. Instead, it constructs the necessary Expression by using Mono.Reflection to decompile your code on the fly. It knows which properties, methods, etc. need to be decompiled by having you decorate them with ComputedAttribute or by using the .Computed() extension within the query:

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

This can also be easily extended, which is a nice touch. For example, I set it up to look for the NotMapped data annotation instead of having to explicitly use the ComputedAttribute.

Once you've set up your entity, you just trigger decompilation by using the .Decompile() extension:

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

这篇关于实体框架中LINQ投影的可重用计算（代码优先）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！