问题描述
我正在尝试建立一个查询,该查询将作为IQueryable而不是内存(IEnumerable)针对数据库执行.
I'm trying to build a query that will execute against the database as an IQueryable, and not in memory (IEnumerable).
该查询将用于几种不同的目的,并且每种目的的Total属性计算方式都略有不同.
The query will be used for several different purposes and each purpose has a slightly different way in which the Total property is calculated.
因为我正在使用Func来计算总数,所以我收到一条错误消息,通知我sql不知道如何处理Func的Invoke方法,这是可以理解的.
Because I'm using a Func for calculating the total, i get an error advising me that sql doesn't know how to deal with the Invoke method of my Func, which is understandable.
要解决这个问题,我不得不通过调用ToList()将这些分组列出来,这对性能不利.
To get past the problem, i have had to list the groupings into memor by calling ToList() which is not good for performance.
有没有一种方法可以将我的查询作为IQueryable来执行?否则,我将不得不多次计算该查询20次以上
Is there a way that i can execute this query as an IQueryable? Otherwise im going to have to write this query 20+ times with a calculation variance
Func<IGrouping<object, MyType>, double?> calculateTotal= (group) => @group.Sum(x => x.PassengerTotal);
Dictionary<object, double?> weekValues = queryable.GroupBy(o => new
{
Year = SqlFunctions.DatePart("yyyy", o.DateCreated),
Week = SqlFunctions.DatePart("ww", o.DateCreated),
Source = o.SourceId,
})
.ToList() //NEED TO REMOVE THIS CALL
.Select(ac => new WeeklyGraphGroup()
{
Year = ac.Key.Year,
Week = ac.Key.Week,
SourceId = ac.Key.Source,
Total = calculateTotal(ac)
})
.ToDictionary(dict =>
new
{
Year = dict.Year,
Week = dict.Week,
Source = dict.SourceId
}, grp => grp.Total);
推荐答案
创建部分类,如下所示:
Create a partial class as follows:
public partial class WeeklyGraphGroup
{
public int ? Year { get; set; }
public int ? Week { get; set; }
public int Source { get; set; }
}
public partial class WeeklyGraphGroup
{
private int ? _Total;
public int ? Total
{
get
{
this._Total = CalculateTotal(this.Year, this.Week, this.Source);
return this._Total;
}
}
public int ? CalculateTotal(int ? Year, int ? Week, int Source)
{
// do your calculation and return the value of total
// use whatever formula you want here. I guess you are calculating
// total based on any of the parameters(year, week or source);
return value;
}
}
然后执行以下查询:
var list = db.Stores.GroupBy(o => new WeeklyGraphGroup
{
Year = SqlFunctions.DatePart("yyyy", o.DateCreated),
Week = SqlFunctions.DatePart("ww", o.DateCreated),
Source = o.SourceId,
})
.Select ( u => new WeeklyGraphGroup
{
Year = u.Key.Year,
Week = u.Key.Week,
Source = u.Key.Source
}
).ToList();
总计将自动更新
这篇关于在不执行内存调用的情况下,将c#Func用作IQueryable的一部分的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!