CODEWITHSUNDEEP
asp.netvb.netlinqentity-framework
Kevin Wasie
Kevin Wasie

Reputation: 45

Linq to Entities - using group by for many-to-many tables

I would appreciate any assistance in determining the proper way to utilize Linq to Entities for my application. Specifically, when working with multiple many-to-many tables, I am having difficulty understanding grouping.

The simplified database design is:

http://imageshack.us/a/img443/5863/entitydesignerdiagram.png

DB Design Concept:

People: individual people that have access to edit multiple statistics. Seasons: Time period to group statistics by, such as 2012 baseball, or 2012 . Players: individual or gorup of people involved in the statistic. All that is needed here is a name for the entity. Such as Kevin Wasie, or Varsity Team Statistics: Holds individual statistic information belonging to 1 season, such as Touchdowns, or Home-Runs.

Code Behind:

    Dim stats = From st In pl.statistics From per In st.people
      Where per.id.Equals(personID)
      Select New With
            {.season_name = st.season.name, .statistic_name = st.name, .season_id = st.season.id,
             .players = From play In pl.players
                             Where play.seasonId = st.season.id
                             Select New With {.player_name = play.name}}

    rep_statistics.DataSource = stats
    rep_statistics.DataBind()

.aspx:

<asp:Repeater ID="rep_actions" runat="server">
<HeaderTemplate>
</HeaderTemplate>
<ItemTemplate>
<b><%# Eval("season_name")%></b> - <%# Eval("statistic_name")%><br />
    <asp:Repeater ID="rep_part" runat="server" DataSource='<%# Eval("players")%>'>
    <itemtemplate>
    <%# Eval("participantname")%>
    </itemtemplate>
    <SeparatorTemplate>,</SeparatorTemplate>
    </asp:Repeater>
</ItemTemplate>
</asp:Repeater>

The desired outcome is to have the page displayed grouped by the season as such:

Season 1
   Statistic 1
      Season1 Players
   Statistic 2
      Season1 Players
   Statistic 3
      Season1 Players
Season 2
   Statistic 4
      Season 2 Players
...

The current outcome is:

Season 1
   Statistic 1
      Season1 Players
Season 1
   Statistic 2
      Season1 Players
Season 1
   Statistic 3
      Season1 Players
Season 2
   Statistic 4
      Season 2 Players
...

How do I modify the linq to get the desired outcome?

Any assistance, including links to tutorials or other information, would be greatly appreciated. Thank you in advance!

Upvotes: 1

Views: 2925

Answers (1)

RePierre
RePierre

Reputation: 9566

Below is C# code, but I hope it will give you a hint on what you need to do. The query filters the people with the given Id and uses SelectMany() to select the statistics for those persons.

Afterwards, it groups the selected statistics by SeasonId and populates the collection of the seasons to be outputed.

var data = st.People
             .Where(p => p.Id == personID)
             .SelectMany(p => p.Statistics)
             .GroupBy(s => s.SeasonId)
             .Select(g => new
             {
                 SeasonId = g.Key,
                 SeasonName = g.Fist().Season.Name,
                 Statistics = g.Select(s => new
                               {
                                   StatisticId = s.Id,
                                   StatisticName = s.Name,
                                   Players = s.Season.Players
                               })
             });

Upvotes: 3

Related Questions