导航属性的总和为SQL的Linq [英] SUM on navigation property as Linq to SQL
问题描述
我正在尝试找到一种解决方案，该方法如何执行 TotalConversions =>使用Linq to SQL的数据库服务器上的Statistics.Sum（sum => sum.Conversions）。
I am trying to find a solution how to execute TotalConversions => Statistics.Sum(sum => sum.Conversions) on database server using Linq to SQL.
当前代码的问题是 Statistics是ICollection （IEnumerable）而不是IQueryable，并且SUM函数仅在对结果求和时从本地获取数据库中的所有记录。这不是我们的解决方案，因为统计信息包含成千上万的记录。
The problem with current code is that Statistics is ICollection(IEnumerable) and not IQueryable and SUM function fetch all records from database locally and only when SUM the results. This is not solution for us because Statistics contain thousands of records.
public class User : Entity
{
public int Id { get; set; }
public virtual ICollection<Statistic> Statistics { get; set; }
[NotMapped]
public int TotalConversions => Statistics.Sum(sum => sum.Conversions);
}
我们尝试的另一种解决方案是访问 DbContext 在模型中并执行 Linq to SQL 这样的查询
Another solution which we tried is to access DbContext inside Model and execute Linq to SQL query like this
[NotMapped]
public int TotalConversions
{
get
{
if (_totalConversions == null)
{
var databaseContext = GetDbContextFromEntity(this);
_totalConversions = databaseContext.Statistic.Where(s => s.UserId == Id).Sum(s => s.Conversions);
}
return (int)_totalConversions;
}
}
它可以正常工作，但另一个问题是这种属性不能在这样的linq查询中使用
It works fine but another problem comes here that such property can't be used inside linq query like this
_context.User.Where(w=>w.Id==1 && w.Id == 2).OrderBy(o=>o.TotalConversions)
如何在 Model 内部的计算属性中执行 SUM ，该操作将在数据库服务器上执行，也可在select内部使用查询。 EF 完全可以吗？
How to perform SUM in calculated property inside Model which would be executed on database server and also could be used inside select queries. Is that possible at all with EF?
推荐答案
问题。 / h2>
The problem.
[NotMapped]
public int TotalConversions => Statistics.Sum(sum => sum.Conversions);
将属性标记为 [NotMapped] ，您告诉EF此属性在数据库级别上不应该存在。它将仅在您的代码中可用（即，当数据在内存中时，而不是在数据库中时）。
When you mark a property as [NotMapped], you're telling EF that this property should not exist on the database level. It will only be available in your code (i.e. when the data is in-memory, not when it is in-database).
将此属性标记为 [NotMapped] 本质上使您无法使用 TotalConversions 属性使用数据库操作（排序依据）。
Marking this property as [NotMapped] inherently precludes you from using a database operation (order by) using the TotalConversions property.
我在这里看到两个解决方案。
I see two solutions here. One is simple, the other less so.
在第二个示例中您已经做了很多，但是您可以做到简洁一点，避免使用自定义属性。
You've already pretty much done this in your second example, but you can do it a bit more concisely, avoiding use of a custom property.
您要执行的操作是：
_context.User.Where(w=>w.Id==1 && w.Id == 2).OrderBy(o => o.TotalConversions)
就像我提到的那样，你不能这样做。您可以要做的是：
You can't do this, as I mentioned. What you can do is:
_context.User.Where(w=>w.Id==1 && w.Id == 2).OrderBy(o => o.Statistics.Sum(sum => sum.Conversions))
Sum（）具有SQL等效项，因此Linq2SQL能够对其进行转换。本质上，您已将请求从基于此.Net属性订购集合（这是不可能的）更改为基于此与SQL兼容的评估订购订购。
Sum() has an SQL equivalent, so Linq2SQL is able to convert it. Essentially, you have changed your request from "order the collection based on this .Net property" (which is impossible) to "order the collection based on this SQL-compatible evaluation".
但是，我认为您正在尝试使用自定义属性，以避免必须复制/在整个代码库中粘贴相同的计算。如果那是您的目标，那么我完全同意您的意图，这就是尝试更复杂的替代方法的充分理由。
However, I assume that you're trying to use a custom property in order to avoid having to copy/paste that same calculation all over your codebase. If that is your goal, I fully agree with your intentions, and that's a good enough reason to try the more complex alternative.
您可以参数化 OrderBy 参数。首先， OrderBy 方法是具有两种泛型类型的泛型方法：
You can parametrize the OrderBy parameter. First things first, The OrderBy method is a generic method with two generic types:
IOrderedQueryable<A> OrderBy<A,B>(Expression<Func<A,B>> expression) { }
注意：为了方便示例，此签名得到了简化，以向您展示 OrderBy 方法的基本输入和输出。
Note: This signature is simplified for the sake of example, to show you the basic input and output of an OrderBy method.
A 是您的实体类型，在当前情况下为 User 。
A is your entity type, in your current case that is User.
B 是排序参数的类型。您按整数排序，因此B是 int 。
注意：几乎总是可以由编译器推断B（基于在您使用的lambda方法上），而无需显式声明。但是，对于此解决方案，您将需要知道其类型。
B is the type of your sorting argument. You're sorting by an integer, so B is int.
Note: Almost always, B can be inferred by the compiler (based on the lambda method you use) and doesn't need to be declared explicitly. However, for this solution you will need to know its type.
这意味着对于您当前的情况，我们知道 OrderBy 将采用类型 Expression< Func< User，int>> 的参数。您可以通过将鼠标悬停在 Orderby 上来确认这一点，IntelliSense会告诉您当前定义的通用参数的类型。
This means that for your current case, we know that the OrderBy will take a parameter of type Expression<Func<User,int>>. You can confirm this by hovering over your Orderby, IntelliSense will tell you the types of the generic parameters as they are currently defined.
不是像这样定义 int 属性：
[NotMapped]
public int TotalConversions => Statistics.Sum(sum => sum.Conversions);
您可以定义一个表达式属性：
You can define an expression property like this:
[NotMapped]
public static Expression<Func<User,int>> TotalConversionsLambda = (user => user.Statistics.Sum(sum => sum.Conversions));
请注意，我将其设为静态，因为此lambda并不与特定的 User 。
这是一个关键的区别。 int属性基本上说这是您如何获得此 User 的总转化次数，而expression属性则表明这是 检索任何给定的用户 的总转化次数的方法。而不是定义 value ，我们定义的方法将为我们提供所需的值。
Notice that I made it static, because this lambda is not tied to a specific User object, we want to access it without relying on an existing User.
This is a key difference. The int property basically says "this is how you get the total conversions for this User", whereas the expression property says "this is the method for retrieving the total conversions for any given User". Instead of defining the value, we are defining the method that will give us the needed value.
这意味着您可以在调用 OrderBy 时使用此预定义的lambda：
This means that you can use this predefined lambda when you call OrderBy:
_context.User.Where(w=>w.Id==1 && w.Id == 2).OrderBy(User.TotalConversionsLambda)
对于编译器（和EF），这等效于较简单方法的解决方案，因此将以相同的方式工作。但是，这样做还有一个好处，就是定义了lambda 一次（DRY），而不是将其复制/粘贴到代码库中的任何地方。
To the compiler (and EF), this is equivalent to the solution from the simpler method and will therefore work the same way. However, this has the added benefit of defining the lambda once (DRY) instead of copy/pasting it everywhere in the codebase.
参数化表达式可能会有些令人困惑。至少当我开始使用它们时，情况就是如此。
Parametrizing expressions can be a bit bewildering. That was the case for me when I started using them, at least. So maybe a simpler explanation is in order.
请注意，我们可以交换文字值：
Notice that we can swap a literal value:
DoSomething(5);
对于包含值的变量：
int myValue = 5;
DoSomething(myValue);
此示例使用整数，但是我们可以针对任何类型（字符串，布尔，...应该很明显）。这也适用于引用类型：
This example uses an integer, but we can do this for any type (string, bool, ... should be obvious). This also works for reference types:
DoSomething(new User() { Name = "John Doe" });
对：
User john = new User() { Name = "John Doe" };
DoSomething(john);
Expression<> 有点令人费解（由于其复杂的泛型键入和lambda表示法），但其工作原理完全相同：
An Expression<> is a bit convoluted (due to its complex generic typing and the lambda notation), but it works exactly the same:
DoSomething(foo => foo.BarValue);
对：
Expression<Func<Foo,Bar>> myValue = (foo => foo.BarValue);
DoSomething(myValue);
这就是全部。类型更复杂，但基本原理完全相同。
And that's all there is to it. The type is more complex, but the underlying principle is exactly the same.
您可以创建其他 int 属性以在代码中使用 。
You can create an additional int property to use in code.
[NotMapped]
public static Expression<Func<User,int>> TotalConversionsLambda = (user => user.Statistics.Sum(sum => sum.Conversions));
[NotMapped]
public int TotalConversions
{
get
{
return TotalConversionsLambda.Compile().Invoke(this);
}
}
老实说，我确实认为与简单地定义一个独立于表达式属性的属性相比，表达式可能最终会损害您的性能。
Honestly, I do think that the repeated compilation of the expression may end up hurting you more performance-wise, compared to simply defining a property that works independently of the expression property:
[NotMapped]
public static Expression<Func<User,int>> TotalConversionsLambda = (user => user.Statistics.Sum(sum => sum.Conversions));
[NotMapped]
public int TotalConversions
{
get
{
return this.Statistics.Sum(sum => sum.Conversions);
}
}
选择是您的。如果您想（专门地）坚持DRY，可以使用第一个。但是这样做的性能成本可能最终会伤害您，而坚持DRY会使您受益更多。
The choice is yours. You can use the first one if you want to (pedantically) adhere to DRY; but the performance cost of doing so may end up hurting you more than adhering to DRY will benefit you.
我对编译的性能成本没有确切的数字一个表达式，我不知道您的优先级在哪里（性能优于DRY？DRY优于性能？），所以我无法为您做出决定。
I don't have the exact numbers on the performance cost of compiling an expression, and I don't know where your priorities lie (performance over DRY? DRY over performance?), so I can't make the decision for you.
这篇关于导航属性的总和为SQL的Linq的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
