CODEWITHSUNDEEP
linq
Mronzer
Mronzer

Reputation: 439

Select from mulitle tables with count in Linq

I am busy with a small online voting web app, now I struggling to get the total number of votes for each party that I stored in a different table. Here is what I have tried, this method gets each party from the votes table named [dbo].[VoterCandidateMapping]

public List<int> GetAllPartIDs()
{
    List<int> partieIDs = new List<int>();
    var parties = (from votes in voteDB.VoterCandidateMappings
                   select votes.PartyID).Distinct().ToList();
    partieIDs = parties;
    return partieIDs;
}

Then I want to use this method to count each vote associated with a particular part, here is the code

public IQueryable<ResultsViewModel> GetResults()
{
    int numberOfVotes = 0;
    foreach (int IDs in GetAllPartIDs())
    {
        numberOfVotes = (from votes in voteDB.VoterCandidateMappings
                         where votes.PartyID == IDs ? true : false
                         select votes.VoterID).Count();
    }
    return (
        from results in voteDB.VoterCandidateMappings
        join parties in voteDB.Parties
        on results.PartyID equals parties.Id
        select new ResultsViewModel
        {
            PartyName = parties.Name,
            TotalVotes = numberOfVotes
        });
}

It runs and return almost every data but the total number of votes is the same

Upvotes: 0

Views: 40

Answers (1)

Kaspars Ozols
Kaspars Ozols

Reputation: 7017

The reason why it does not work is that you are trying to store multiple values in a single numberOfVotes variable.

Let's go through code what you have now.

First foreach loop calculate votes for each party and assigns to numberOfVotes variable. Each time value is assigned, existing value in numberOfVotes is overwritten. In the end of loop numberOfVotes contains number of votes for the last party. This is value you are seeing in your results as you use the same variable to return results.

Here is one way to do it correctly:

public IQueryable<ResultsViewModel> GetResults()
{
    var groupedVotes = voteDB.VoterCandidateMappings
        .GroupBy(x => x.PartyID)
        .Select(x => new { PartyId = x.Key, NumberOfVotes = x.Count());

    return voteDB.Parties
        .Select(x => new ResultsViewModel
        {
            PartyName = x.Name,
            TotalVotes = groupedVotes
                .Where(y => y.PartyId == x.Id)
                .Select(y => y.NumberOfVotes)
                .FirstOrDefault()
        });
}

Upvotes: 1

Related Questions