围绕抛出“SQL 中不支持翻译”的嵌套查询进行设计。对于发票总计
我有一张发票表。我的服务层知道如何计算一张发票的发票Total，但如果我想按Total 过滤一组发票，LINQ 查询会失败，因为存在“No支持 SQL 中的翻译”。发生这种情况是因为无法对每个发票记录执行嵌套查询。解决这个问题的正确方法是什么？
每张发票 Total 都是通过对 InvoiceLines 表中计算出的行总计进行求和来确定的，该表涉及折扣，字面意思是：
public decimal Total
{
get
{
return Lines.Sum(l => l.LineTotal); // Lines provided by repository
}
}
我不想在我的服务中已经存在的数据库中重复业务逻辑层，涉及对影响发票总额的行项目进行折扣。但除了将发票 Total 缓存在可以直接过滤的数据库列中之外，我没有看到任何其他方法。
发票是不可变的，因此它不应该导致任何数据完整性问题，但我讨厌重复数据。我可以使用视图来显示发票总额，但随后我的存储库正在执行服务层功能。
I have a table of invoices. My service layer knows how to calculate the invoice Total for one invoice, but if I want to filter a set of invoices by Total, the LINQ query fails because there is "No Supported Translation in SQL". This happens because nested query cannot be performed for every invoice record. What is the right way to work around it?
Each invoice Total is determined by summing calculated line totals from an InvoiceLines table which involve discounts, literally:
public decimal Total
{
get
{
return Lines.Sum(l => l.LineTotal); // Lines provided by repository
}
}
I prefer not to duplicate business logic in my database that is already in my service layer, which involves discounting line items that influence the invoice total. But I don't see any way other than caching the invoice Total in a database column which I can directly filter on.
Invoices are immutable, so it shouldn't lead to any data integrity problems, but I hate duplicating data. I could use a view to display the invoice total, but then my repository is performing service-layer functions.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
Total 是 LINQ 实体的附加属性吗？如果是，则不能使用 Total 属性；这是特别不支持的。很难将重用的逻辑全部放在与数据库相反的 LINQ 查询中的一个位置...如果您执行根查询，则在执行查询后执行额外的任务（可以通过调用 ToList() 轻松完成） ，此后的所有处理都将使用 LINQ to Objects，您可以做任何您想做的事情。
HTH。
Is Total an added property to a LINQ entity? If it is, you can't use the Total property; that's specifically not supported. It's hard to put reused logic all in one place in a LINQ query that goes against the database... If you do a root query, then perform the extra task after the query is executed (which can be easily done calling ToList()), all processing after this would be with LINQ to Objects, and you can do whatever you want.
HTH.
已解决：
现在，我可以根据总数过滤数据，这些数据由数据库计算并缓存在我的模型中。
Solved:
Now I can filter my data on totals, which are calculated by the database and cached in my models.