LINQ - finding a join between a two tables using the UserId's and Guid's and retuning the Vote Count

Question

tblQuestion
-----------
questionId     question       userId
--------------------------------------
1              question 1     1
2              question 2     2

tblVotes
--------
voteId         userId        questionId
----------------------------------------
1              1             1
2              2             1
3              3             1
4              1             2 

As you can see above i have two tables, question table and a votes table. Each question can have a number of votes that is represented in the votes table as the votes table is linked to the questionId.

If i had a function on the server end like so:

public IActionResult GetPollsVoted(string userId)

I want the userId passed so that the results should be that if the userId passed in equals "1":

tblResults
----------
questionId      question      userId    voteCount
-------------------------------------------------
1               question 1    1         3
2               question 2    1         1

I know im still trying to get my head around this scenario as im trying to use linq in c# to produce the results above, so how can i achieve this using link to get the reusltgs of the tblResults answer?

I tried this but still cant seem to get this linq command working:

polls1 = from c in ctx.TblPolls
         join o in ctx.TblVotes on c.UserId equals o.UserId into g
         group c.UserId by c.Guid into gg
         from d in ctx.TblPolls
         join m in ctx.TblVotes on d.Guid equals m.PollGuid into ggg
         select new { d.Question, Count = ggg.Count() };

The tables in the linq dont correspond to the example i have shown you, but tblQuestions equals ctx.TblPolls and tblVotes equals ctxTblVotes, the d.Guid equals questionId in tblQuestion and m.PollGuid equals questionId in tblVotes as a link to both the tables. The userId's are self explanatory.

so how would i get the results i want using linq?

Answer 1

I think I've cleaned up your namings correctly. Here is a slightly more readable version of your answer that you have posted:

var TblPolls = new[]
{
    new { Guid = 1, Question = "question 1", UserId = 1 },
    new { Guid = 2, Question = "question 2", UserId = 2 },
};

var TblVotes = new[]
{
    new { VoteId = 1, UserId = 1, PollGuid = 1 },
    new { VoteId = 2, UserId = 2, PollGuid = 1 },
    new { VoteId = 3, UserId = 3, PollGuid = 1 },
    new { VoteId = 4, UserId = 1, PollGuid = 2 },
};

var userId = 1;

var polls =
(
    from vote in TblVotes
    where vote.UserId == userId
    join poll in TblPolls on vote.PollGuid equals poll.Guid
    join vote2 in TblVotes on poll.Guid equals vote2.PollGuid into votes
    select new
    {
        poll.Question,
        Count = votes.Count(),
    }
).ToList();