GroupBy复杂查询

Question

我为统计页面创建了一个视图模型，如下所示:

public class StatsSeasonViewModel
{
    public int player_id { get; set; }
    public string player_name { get; set; }
    public int games_played { get; set; }
    public int total_first { get; set; }
    public int total_second { get; set; }
    public int total_third { get; set; }
    public int total_wickets { get; set; }
    public double avg_wickets { get; set; }
    public int total_points { get; set; }
    public double avg_points { get; set; }
}

我有一个复杂的LINQ语句来填充模型。我觉得这可以更简单，但我不知道怎么做:

const int first_place = 5;
const int second_place = 3;
const int third_place = 1;
var model =
from s in _db.Stats
join p in _db.Players
on s.player_id equals p.player_id
where s.season_id == current_season
select new StatsSeasonViewModel
{
    player_id = p.player_id,
    player_name = p.name,
    games_played = (from st1 in _db.Stats
                    where st1.player_id == s.player_id
                    select st1).Count(),
    total_first = (from st2 in _db.Stats
                    where st2.player_id == s.player_id && st2.place == 1
                    select st2).Count(),
    total_second = (from st3 in _db.Stats
                    where st3.player_id == s.player_id && st3.place == 2
                    select st3).Count(),
    total_third = (from st4 in _db.Stats
                    where st4.player_id == s.player_id && st4.place == 3
                    select st4).Count(),
    total_wickets = (from st5 in _db.Stats
                        where st5.player_id == s.player_id
                        select st5.wickets).Sum(),
    avg_wickets = (from st5 in _db.Stats
                    where st5.player_id == s.player_id
                    select st5.wickets).Sum() /
                    (from st1 in _db.Stats
                    where st1.player_id == s.player_id
                    select st1).Count(),
    total_points = (from st5 in _db.Stats
                    where st5.player_id == s.player_id
                    select st5.wickets).Sum() +
                    (
                        (from st2 in _db.Stats
                            where st2.player_id == s.player_id && st2.place == 1
                            select st2).Count()
                    ) * first_place +
                    (
                        (from st3 in _db.Stats
                            where st3.player_id == s.player_id && st3.place == 2
                            select st3).Count()
                    ) * second_place +
                    (
                        (from st4 in _db.Stats
                            where st4.player_id == s.player_id && st4.place == 3
                            select st4).Count()
                    ) * third_place,
    avg_points = (
                    (from st5 in _db.Stats
                        where st5.player_id == s.player_id
                        select st5.wickets).Sum() +
                    (
                        (from st2 in _db.Stats
                            where st2.player_id == s.player_id && st2.place == 1
                            select st2).Count()
                    ) * first_place +
                    (
                        (from st3 in _db.Stats
                            where st3.player_id == s.player_id && st3.place == 2
                            select st3).Count()
                    ) * second_place +
                    (
                        (from st4 in _db.Stats
                            where st4.player_id == s.player_id && st4.place == 3
                            select st4).Count()
                    ) * third_place
                ) /
                (from st1 in _db.Stats
                    where st1.player_id == s.player_id
                    select st1).Count()
};

所以我现在最大的问题是，我需要在这个查询上做一个组by，这样它就不会显示重复。但是，当我尝试添加组by时，我会迷失在SELECT之后如何执行查询的其余部分。我如何做一个组上的查询，并得到我需要的结果?

编辑:FWIW这是我得到的结果:http://ecl.moyl.com/Home/Stats

第二个问题当然是查询本身的复杂性。有更简单的方法吗?

Answer 1

要回答您的主要问题，有几种方法可以简化:

var search = from st2 in _db.Stats
    where st2.player_id = s.player_id
    group st2 by st2.player_id

您将遍历每个组以获得IGrouping<TKey, TElement>以获得单个计数(ref)。

for (var playerGroup in search)
{
     Console.WriteFormat("{0}: {1}n", playerGroup.Key, playerGroup.Count());
}

如果你用一种稍微不同的方式来写计数/求和，你的代码可能会更容易读。

的例子中,这个:

games_played = (from st1 in _db.Stats
                where st1.player_id == s.player_id
                select st1).Count(),
total_wickets = (from st5 in _db.Stats
                    where st5.player_id == s.player_id
                    select st5.wickets).Sum()

可以变成这样:

var filter = st => st.player_id == s.player_id; // reuse this over and over
games_played = _db.Stats.Where(filter).Count(),
total_wickets = _db.Stats.Where(filter).Sum(st5 => st5.wickets)

---

事实上，为了"鱼与熊掌兼得"，当你使用group by语句时，整个过滤器就变得没有必要了。您必须更改创建模型的方式，以便可以将IGrouping<int,Stat>(假设类型是什么)传递给构造函数。在这种方法中，您的总体查询看起来像这样:

const int first_place = 5;
const int second_place = 3;
const int third_place = 1;
var model =
    from s in _db.Stats
    join p in _db.Players
    on s.player_id equals p.player_id
    where s.season_id == current_season
    group st by st.player_id into group
    select new StatsSeasonViewModel(group)

现在你的StatsSeasonViewModel负责根据组

中的值填充其统计数据

 public StatsSeasonViewModel(IGrouping<int,Stat> playerStats)
 {
     player_id = playerStats.Key;
     games_played = playerStats.Count();
     total_wickets = playerStats.Sum(st=>st.wickets);
     // ....
 }

Answer 2

你正在使用一种过时的MVC中编写Linq的方式。使用dbContext可以通过创建一个方法轻松地找到播放器，该方法将播放器Id作为变量，并使用Distinct来返回不同的值并避免重复:

var player = _db.Players.Where(p => p.player_id == Id);

然后通过lambda-join播放器与统计表来处理属性:

var st1 in _db.Stats.Where(st1 => st1.player_id == player.player_id).Distinct().Count();

编辑:

我对它做了一点改变，因为您可能需要第二个表达式中的distinct，因为第一个表达式已经找到了一个distinct值