在 LINQ 语句中使用分部类属性 [英] Using a partial class property inside LINQ statement
问题描述
我想找出最好的方法来做我认为很容易的事情.我有一个名为 Line 的数据库模型，它表示发票中的一行.
I am trying to figure out the best way to do what I thought would be easy. I have a database model called Line that represents a line in an invoice.
大概是这样的:
public partial class Line
{
public Int32 Id { get; set; }
public Invoice Invoice { get; set; }
public String Name { get; set; }
public String Description { get; set; }
public Decimal Price { get; set; }
public Int32 Quantity { get; set; }
}
这个类是从db模型生成的.
我有另一个类，增加了一个属性:
This class is generated from the db model.
I have another class that adds one more property:
public partial class Line
{
public Decimal Total
{
get
{
return this.Price * this.Quantity
}
}
}
现在，从我的客户控制器，我想做这样的事情:
Now, from my customer controller I want to do something like this:
var invoices = ( from c in _repository.Customers
where c.Id == id
from i in c.Invoices
select new InvoiceIndex
{
Id = i.Id,
CustomerName = i.Customer.Name,
Attention = i.Attention,
Total = i.Lines.Sum( l => l.Total ),
Posted = i.Created,
Salesman = i.Salesman.Name
}
)
但我不能感谢臭名昭著的
But I can't thanks to the infamous
The specified type member 'Total' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.
重构它以使其正常工作的最佳方法是什么?
What is the best way to refactor this so that it works?
我已经尝试过 LinqKit、i.Lines.AsEnumerable()，并将 i.Lines 放入我的 InvoiceIndex 模型并让它计算视图的总和.
I have tried LinqKit, i.Lines.AsEnumerable(), and putting i.Lines in my InvoiceIndex model and having it calculate the sum for the view.
最后一个解决方案有效"，但我无法对这些数据进行排序.我最终想做的是
That last solution 'works' but I cannot sort on that data. What I want to be able to do in the end is
var invoices = ( from c in _repository.Customers
...
).OrderBy( i => i.Total )
我还想分页我的数据，所以我不想浪费时间将整个 c.Invoices 转换为带有 .AsEnumerable() 的列表
Also I want to page my data, so I do not want to waste time converting the entire c.Invoices to a list with .AsEnumerable()
赏金
我知道这对某些人来说一定是个大问题.经过几个小时的互联网搜索，我得出的结论是，没有得出满意的结论.然而，我相信对于那些试图用 ASP MVC 进行分页和排序的人来说，这一定是一个相当普遍的障碍.我知道该属性无法映射到 sql，因此您无法在分页之前对其进行排序，但我正在寻找的是一种获得所需结果的方法.
I know this must be a somewhat big problem for some people. After hours of scouring the internet I have come to the conclusion that no happy conclusion has been made. Yet I believe this must be a fairly common roadblock for those who are trying to do paging and sorting with ASP MVC. I understand that the property can not be mapped to sql and therefore you cannot sort on it before paging, but what I am looking for is a way to get my desired result.
完美解决方案的要求:
- DRY，这意味着我的总计算将存在于 1 个地方
- 同时支持排序和分页，并按顺序
- 不要使用 .AsEnumerable 或 .AsArray 将整个数据表拉入内存
我真的很高兴找到一种在我的扩展部分类中指定 Linq to entity SQL 的方法.但有人告诉我这是不可能的.请注意，解决方案不需要直接使用 Total 属性.根本不支持从 IQueryable 调用该属性.我正在寻找一种方法来通过不同的方法实现相同的结果，但同样简单和正交.
What I would be really happy to find is a way to specify the Linq to entities SQL in my extended partial class. But I have been told this is not possible. Note that a solution does not need to directly use the Total property. Calling that property from IQueryable is not supported at all. I am looking for a way to achieve the same result via a different method, yet equally simple and orthogonal.
赏金的获胜者将是最后得票最高的解决方案，除非有人发布了完美的解决方案:)
The winner of the bounty will be the solution with the highest votes at the end, unless someone posts a perfect solution :)
在阅读答案之前忽略以下内容:
{1}使用 Jacek 的解决方案，我更进一步，并使用 LinqKit 使属性可调用.这样甚至 .AsQueryable().Sum() 也包含在我们的部分类中.以下是我现在正在做的一些示例:
{1} Using Jacek's solution I took it one step further and made the properties invokable using LinqKit. This way even the .AsQueryable().Sum() is enclosed in our partial classes. Here is some examples of what I am doing now:
public partial class Line
{
public static Expression<Func<Line, Decimal>> Total
{
get
{
return l => l.Price * l.Quantity;
}
}
}
public partial class Invoice
{
public static Expression<Func<Invoice, Decimal>> Total
{
get
{
return i => i.Lines.Count > 0 ? i.Lines.AsQueryable().Sum( Line.Total ) : 0;
}
}
}
public partial class Customer
{
public static Expression<Func<Customer, Decimal>> Balance
{
get
{
return c => c.Invoices.Count > 0 ? c.Invoices.AsQueryable().Sum( Invoice.Total ) : 0;
}
}
}
第一个技巧是 .Count 检查.这些是必需的，因为我猜你不能在空集上调用 .AsQueryable .您收到有关 Null 实现的错误.
First trick was the .Count checks. Those are needed because I guess you cannot call .AsQueryable on an empty set. You get an error about Null materialization.
布置好这 3 个部分类后，您现在可以执行类似的技巧
With these 3 partial classes laid out you can now do tricks like
var customers = ( from c in _repository.Customers.AsExpandable()
select new CustomerIndex
{
Id = c.Id,
Name = c.Name,
Employee = c.Employee,
Balance = Customer.Balance.Invoke( c )
}
).OrderBy( c => c.Balance ).ToPagedList( page - 1, PageSize );
var invoices = ( from i in _repository.Invoices.AsExpandable()
where i.CustomerId == Id
select new InvoiceIndex
{
Id = i.Id,
Attention = i.Attention,
Memo = i.Memo,
Posted = i.Created,
CustomerName = i.Customer.Name,
Salesman = i.Salesman.Name,
Total = Invoice.Total.Invoke( i )
} )
.OrderBy( i => i.Total ).ToPagedList( page - 1, PageSize );
非常酷.
有一个问题，LinqKit 不支持属性的调用，您将收到有关尝试将 PropertyExpression 转换为 LambaExpression 的错误.有两种方法可以解决这个问题.首先是自己这样拉表情
There is a catch, LinqKit does not support the invocation of properties, you will get an error about trying to cast PropertyExpression to LambaExpression. There are 2 ways around this. Firstly is to pull the expression yourself like so
var tmpBalance = Customer.Balance;
var customers = ( from c in _repository.Customers.AsExpandable()
select new CustomerIndex
{
Id = c.Id,
Name = c.Name,
Employee = c.Employee,
Balance = tmpBalance.Invoke( c )
}
).OrderBy( c => c.Balance ).ToPagedList( page - 1, PageSize );
我觉得这有点傻.所以我修改了 LinqKit 以在遇到属性时取出 get{} 值.它对表达式的操作方式类似于反射，所以它不像编译器会为我们解析 Customer.Balance.我对 ExpressionExpander.cs 中的 TransformExpr 进行了 3 行更改.它可能不是最安全的代码，可能会破坏其他东西，但它现在有效，我已经将缺陷通知了作者.
which I thought was kind of silly. So I modified LinqKit to pull out the get{} value when it encounters a property. The way it operates on the expression is similar to reflection, so its not like the compiler is going to resolve Customer.Balance for us. There is a 3 line change I made to TransformExpr in ExpressionExpander.cs. Its probably not the safest code and might break other things, but it works for now and I have notified the author about the deficiency.
Expression TransformExpr (MemberExpression input)
{
if( input.Member is System.Reflection.PropertyInfo )
{
return Visit( (Expression)( (System.Reflection.PropertyInfo)input.Member ).GetValue( null, null ) );
}
// Collapse captured outer variables
if( input == null
事实上，我几乎可以保证这段代码会破坏一些东西，但目前它可以工作，而且已经足够好了.:)
In fact I pretty much guarantee that this code will break some things, but it works at the moment and that is good enough. :)
推荐答案
还有另一种方法，它有点复杂，但可以让您封装此逻辑.
There is another way, which is a bit more complex, but gives you the ability to encapsulate this logic.
public partial class Line
{
public static Expression<Func<Line,Decimal>> TotalExpression
{
get
{
return l => l.Price * l.Quantity
}
}
}
然后将查询重写为
var invoices = ( from c in _repository.Customers
where c.Id == id
from i in c.Invoices
select new InvoiceIndex
{
Id = i.Id,
CustomerName = i.Customer.Name,
Attention = i.Attention,
Total = i.Lines.AsQueryable().Sum(Line.TotalExpression),
Posted = i.Created,
Salesman = i.Salesman.Name
}
)
它对我有用，在服务器端执行查询并符合 DRY 规则.
It worked for me, performs queries server-side and complies with the DRY rule.
这篇关于在 LINQ 语句中使用分部类属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
