GroupBy复合查询

Question

我为stats页面创建了一个视图模型，如下所示：

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()
};

因此，我现在面临的最大问题是，我需要在这个查询上按组操作，这样它就不会显示重复的内容。但是，当我试图按组添加时，在选择之后，我将无法理解如何执行其余的查询。如何在上面的查询中进行分组并获得所需的结果？

编辑: 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>以获取单个计数(参考)。

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可以很容易地找到播放器，方法是创建一个将player Id作为变量并使用Distinct返回不同值并避免重复的方法：

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

然后通过lambda处理属性--将播放机与stats表连接起来：

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

编辑：

我对它做了一些修改，因为您希望在第二个表达式中找到不同的值，因为第一个表达式已经找到了一个不同的值。