来自LINQ的结果，SUM for TimeSpan，GROUP和JOIN [英] Results from LINQ with SUM for TimeSpan, GROUP and JOIN

问题描述

我有以下EF模型：

我想获取以下视图中的数据：

I want to get data in following view:

在数据实体中，时间是以毫秒为单位的Timespan。因此， PlaceInStep 是成员之间的 StepId 。

In Data entity Time is Timespan with milliseconds. In result, PlaceInStep is place between members by StepId.

如何使这个硬查询？

编辑1 ：

我的一些代码：

var query = from data in ctx.Data
                            join member in ctx.Members
                            on data.MemberId equals member.MemberId
                            join team in ctx.Teams
                            on member.TeamId equals team.TeamId
                            group member by member.TeamId into g
                            select new {TeamId = g.Key, TeamName = g.Select(t=>t.Teams.TeamName), TotalTime = ???};

推荐答案

您不应该在查询中使用加入。您的模型具有导航属性，请改用它们。您可以阅读Craig Stuntz的 blogpost 。

Advice at first. You should not use join in your queries. You have your model with navigation properties, use them instead. You can read Craig Stuntz's blogpost about it.

您可以选择所有团队和关于内部列表中成员的信息。尝试一下：

You can select all teams and information about members in inner list. Try this out:

var query = ctx.data.ToList().OrderBy(d => d.Time).
            GroupBy(d => d.Members.StepId).
            SelectMany(g => g.Select((d, place) => new { Time = d.Time, Members = d.Members, PlaceInStep = place + 1 })).
            GroupBy(d => d.Members.TeamId).
            Select(g => new 
            {
               TeamId = g.Key, 
               Name = g.Select(d => d.Members.Teams.TeamName).First(),  
               Members = g.Select(d => new {Time = d.Time, PlaceInStep = d.PlaceInStep, MemberName = d.Members.MemberName}),                    
               TotalTime = g.Aggregate(new TimeSpan(), (sum, nextData) => sum.Add(nextData.Time))
            });

这篇关于来自LINQ的结果，SUM for TimeSpan，GROUP和JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！