Results from LINQ with SUM for TimeSpan, GROUP and

Question

I've following EF model:

I want to get data in following view:

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

How to make this hard query?

Edit 1:

My some code:

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 = ???};

Answer 1

Try this:

var query = ctx.Data.Join(ctx.Members, d => d.MemberId, m => m.MemberId, (d,m)=>new {data=d, member = m})
                .Join(ctx.Teams, dm=>dm.member.TeamId, t=>t.TeamId, (dm, t)=>new{data=dm.data, member=dm.member, team = t})
                .GroupBy(dmt=>new {dmt.team.TeamId, dmt.team.TeamName})
                .Select(g=>new 
                           {
                               TeamId = g.Key.TeamId, 
                               TeamName = g.Key.TeamName, 
                               TotalTime = g.Aggregate(0, (sum, gr)=>sum.Add(gr.data.Time))
                           });

Answer 2

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