How to i display my total count for each user members in a repeater

Question

I have an asp.net membership application. This is how it works: Various organization can sign up on the portal and in turn the can then register members under themselves. For example, when Organization A registers, he then go on to create members under his organization. I have everything running but i want myself as the superAdmin to be able to know the number of members of each organization. This is how i want to do it, on page load, i want to bind all organizations(From organization table) to a repeater and then get the number of each members from members table

private bool BindConfirmedUsers()
        {
            var approved = cooperative.Coops.Where(m => m.UserStatus == "Approved").OrderByDescending(m => m.CoopId).ToList();

            try
            {
                if (!approved.Any())
                {
                    RepeaterAccepted.DataSource = new List<Coop>();
                    RepeaterAccepted.DataBind();
                    return false;
                }

                RepeaterAccepted.DataSource = approved;
                RepeaterAccepted.DataBind();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

the above code will get all the organizations for me but i need to get the count of each members belonging to an organization. Let me do a demo of my DB structure

   table Organization

ID NAME USERNAME

1 OrgA myusername 2 OrgB otherUsername

Members Table

ID ORGId Username 1 2 user 2 2 user2 3 1 user3

The above is just how i designed the table.(OrgId is a foreign key from Organization table)

<table id="example2" class="table table-bordered table-hover">
                  <thead>
                  <tr>
                    <th>Coop ID</th>
                    <th>Organization Name</th>
                      <th>Address</th>
                      <th>Phone Number</th>
                    <th>Registered Members</th>
                  </tr>
                  </thead>
                    <asp:Repeater ID="RepeaterAccepted" runat="server" OnItemDataBound="RepeaterAccepted_ItemDataBound">
                     <HeaderTemplate></HeaderTemplate>
                        <ItemTemplate>
                  <tbody>
                      <div class="col-lg-12 col-md-12 col-xs-4">
                  <tr>
                    <td><span><%# Eval("Coopcode") %></span></td>
                    <td><span><%#Eval("CoopName") %></span></td>
                    <td><span><%#Eval("CompanyAddress") %></span></td>
                      <td><span><%#Eval("PhoneNumber") %></span></td>
                      <td><span><asp:Label ID="labelMembers" runat="server"></asp:Label></span></td>

                  </tr>
                        </div>
                  </tbody>
                            </ItemTemplate>
                        </asp:Repeater>
                </table>

All that is left is to pass 2 to the label(labelMembers) indicating that OrgB has two registered members. I hope i have my things clear enough

Answer 1

Since your Members table has a foreign key referencing Organization table, I assume your Coop class (which I assume is a EF class for Organization table) should have a property like this:

public IQueryable<Member> Members { get; set; }

to use for master-detail relationship with Members table. so you can use Linq to count number of members for each organization

var approved = cooperative.Coops.Where(m => m.UserStatus == "Approved").OrderByDescending(m => m.CoopId).Select(m =>
                new
                {
                    m.Coopcode,
                    m.CoopName,
                    CoopAddress = m.CompanyAddress,
                    m.PhoneNumber,
                    MembersCount = m.Members.Count()
                });

And use Eval('MembersCount') on your page.