Fetch records with highest count from each group uniquely in Linq

Question

I want only the records with max count from each group. The result must be grouped according to the constituency having maximum record count Here is the code.

protected void Page_Load(object sender, EventArgs e)
{
   var query1 = from m in db.Votes 
                group m by m.CandidateID
                into g
                let w= g.ToArray().Count()
                orderby w descending
                select new {CFN=
                                (from a in db.Persons where a.PersonID==((from j in db.Candidates
                                 from u in db.Persons
                                 where j.PersonID==u.PersonID && j.CandidateID==g.Key
                                 select u.PersonID).Single())select a.PersonFirstName ).Single(),
                            CMN =
                                (from a in db.Persons
                                 where a.PersonID == ((from j in db.Candidates
                                                       from u in db.Persons
                                                       where j.PersonID == u.PersonID && j.CandidateID == g.Key
                                                       select u.PersonID).Single())
                                select a.PersonMiddleName).Single(),
                           CLN =
                                (from a in db.Persons
                                 where a.PersonID == ((from j in db.Candidates
                                                       from u in db.Persons
                                                       where j.PersonID == u.PersonID && j.CandidateID == g.Key
                                                       select u.PersonID).Single())
                                select a.PersonLastName).Single(),
                           PName=
                                 (from b in db.Parties
                                   where b.PartyID==((from c in db.Candidates
                                                      from d in db.Parties
                                                      where c.PartyID==d.PartyID && c.CandidateID==g.Key
                                                      select d.PartyID).Single())
                                   select b.Name).Single(),
                           ConName=
                                (from d in db.Candidates
                                 where d.CandidateID==g.Key
                                 select d.ConstituencyName).Single()
                            ,VC=w};

    foreach (var pair in query1)
    {
        TableRow row = new TableRow();
        TableCell cell1 = new TableCell();
        cell1.Style.Value = "text-align:center";
        cell1.Text = pair.CFN+" "+pair.CMN+" "+pair.CLN;
        TableCell cell2 = new TableCell();
        cell2.Style.Value = "text-align:center";
        cell2.Text = pair.PName;
        TableCell cell3 = new TableCell();
        cell3.Style.Value = "text-align:center";
        cell3.Text = pair.VC.ToString();
        TableCell cell4 = new TableCell();
        cell4.Style.Value = "text-align:center";
        cell4.Text=pair.ConName;
        row.Cells.Add(cell1);
        row.Cells.Add(cell2);
        row.Cells.Add(cell3);
        row.Cells.Add(cell4);

        table1.Rows.Add(row);

    }

}

I am getting the following output

Candidate Name  Party Name  Votes   Constituency
C1                     P1            12       C1
C2                     P2             5       C2
C3                     P1             3       C1

I want following output

Candidate Name  Party Name  Votes   Constituency
C1                     P1            12       C1
C2                     P2             5       C2

Last record should not appear as it belongs to earlier Constituency.

Answer 1

It seems to me that you should aren't currently grouping by constituency at all. The query below should get you started.

var query1 = from vote in db.Votes
             group vote by vote.CandidateID into g
             select new { CandidateID = g.Key, Count = g.Count() } into voteCount
             join candidate in db.Candidates
             on voteCount.CandidateID equals candidate.CandidateID
             group voteCount by candidate.Constituency into constituencies
             select constituencies.OrderByDescending(v => v.Count).First()
             // Rest of query

That first chunk first counts the votes for each candidate, then groups those candidate/count pairs by constituency, and selects just the first one. So that's then a sequence of candidate/count pairs, with just the top pair for each constituency.