使用LINQ语句中的部分类属性 [英] Using a partial class property inside LINQ statement
问题描述
我试图找出要做最好的办法是什么我以为会很容易。
我有一个数据库模型被称为行重新presents发票中的一条线。
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; }
}
这个类是从数据库模型生成。结果
我还有一个类，新增了一项特性：
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.
一个完美的解决方案要求：
Requirements for a perfect solution:
- 干燥，这意味着我的总的计算中1处将存在
- 同时支持排序和分页，并且按照这个顺序
- 不拉数据的整个表到内存.AsEnumerable或.AsArray
我会很高兴地发现是指定的LINQ to SQL实体在我的扩展部分类的方法。但有人告诉我，这是不可能的。注意，一个解决方案并不需要直接使用总计属性。从调用该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 :)
下面忽略，除非直到你读的答案（S）：
{1}
使用Jacek的解决方案，我把它一步，并提出可调用使用LinqKit的属性。这样，即使是.AsQueryable（）。总和（）被包围在我们的部分课程。这里是什么我现在所做的一些例子：
{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一个空集。你得到关于空物化的错误。
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不支持的属性的调用，你会得到一个错误约尝试投放PropertyEx pression到LambaEx pression。周围有此2种方式。首先是给自己拉离pression像这样
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 {}值，当它遇到一个属性。它的前pression的运作方式类似于反射，所以它不是像编译器是要解决Customer.Balance我们。有一个3线的变化我在Ex pressionExpander.cs到TransformExpr的。它可能不是最安全code和可能会破坏其他的东西，但它工作现在，我已经通知了作者关于不足。
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
其实我pretty很多保证，这code将打破一些东西，但它工作在此刻，这是不够好。 ：）
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
}
)
这为我工作，执行查询服务器端，并与干燥的原则规定。
It worked for me, performs queries server-side and complies with the DRY rule.
这篇关于使用LINQ语句中的部分类属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
