LINQ – 使用 UserId 和 Guid 查找两个表之间的连接,并返回投票计数。

huangapple go评论62阅读模式
英文:

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

问题

以下是翻译好的部分:

public IActionResult GetPollsVoted(string userId)
{
    var results = from q in ctx.tblQuestion
                  join v in ctx.tblVotes on q.questionId equals v.questionId into voteGroup
                  where userId == "1" // Replace with the actual userId parameter
                  select new
                  {
                      questionId = q.questionId,
                      question = q.question,
                      userId = userId,
                      voteCount = voteGroup.Count()
                  };

    return results.ToList();
}

请注意,上述代码中的"ctx.tblQuestion"和"ctx.tblVotes"应该替换为你的实际数据库上下文中的表名。此外,在实际使用中,你应该将参数中的"userId == "1""替换为传入函数的实际用户ID,以便根据不同的用户获取投票结果。

英文:
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?

答案1

得分: 1

我认为我已经正确整理了你的命名。以下是你发布的答案的稍微更易读的版本:

var TblPolls = new[]
{
    new { Guid = 1, Question = "问题 1", UserId = 1 },
    new { Guid = 2, 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();
英文:

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();

答案2

得分: 1

以下是您要翻译的代码部分:

public IActionResult GetPollsVoted(string userId)
{
    int _userid = int.Parse(userId);
    var _listuser = _db.TblPolls
        .Join(_db.TblVotes,
              p => p.Id,
              e => e.questionId,
              (p, e) => new
              {
                  Polls = p,
                  Votes = e
              }
        )
        .Where(d => d.Votes.userId == _userid && d.Polls.userId == _userid)
        .GroupBy(d => d.Votes.questionId)
        .Select(d => new
        {
            questionId = d.Key,
            question = d.FirstOrDefault().Polls.question,
            userId = _userid,
            voteCount = d.Count()
        })
        .ToList();
}

结果:

questionId question userId voteCount
f539fdbb-3ff2-4d00-85f3-520e018e8bcb question 1 1 3

示例数据:

Guid iD = Guid.NewGuid();
Guid iD2 = Guid.NewGuid();

_db.TblPolls.Add(new TblPoll() { Id = iD, userId = 1, question = "question 1" });
_db.TblPolls.Add(new TblPoll() { Id = iD2, userId = 2, question = "question 2" });

_db.TblVotes.Add(new TblVote() { userId = 1, questionId = iD });
_db.TblVotes.Add(new TblVote() { userId = 1, questionId = iD });
_db.TblVotes.Add(new TblVote() { userId = 1, questionId = iD });
_db.TblVotes.Add(new TblVote() { userId = 2, questionId = iD2 });
英文:

Your example data or data type is different with your query. i try to use example and your query

public IActionResult GetPollsVoted(string userId)

{
            int _userid = int.Parse(userId);
var _listuser = _db.TblPolls
                .Join(_db.TblVotes,
                      p => p.Id,
                      e => e.questionId,
                      (p, e) => new
                      {
                          Polls = p,
                          Votes = e
                      }
                      ).Where(d => d.Votes.userId == _userid && d.Polls.userId == _userid)
                      .GroupBy(d => d.Votes.questionId).Select(d => new
                      {
                          questionId = d.Key,
                          question = d.FirstOrDefault().Polls.question,
                          userId = _userid,
                          voteCount = d.Count()


                      }).ToList();


Result:

questionId question userId voteCount
f539fdbb-3ff2-4d00-85f3-520e018e8bcb question 1 1 3

Example Date

Guid iD = Guid.NewGuid();
Guid iD2 = Guid.NewGuid();

_db.TblPolls.Add(new TblPoll() { Id= iD, userId = 1, question = "question 1"});
 _db.TblPolls.Add(new TblPoll() { Id = iD2, userId = 2, question = "question 2" });

_db.TblVotes.Add(new TblVote() { userId = 1, questionId  = iD });
_db.TblVotes.Add(new TblVote() { userId = 1, questionId = iD });
_db.TblVotes.Add(new TblVote() { userId = 1, questionId = iD });
_db.TblVotes.Add(new TblVote() { userId = 2, questionId = iD2 });

huangapple
  • 本文由 发表于 2023年6月19日 10:31:29
  • 转载请务必保留本文链接:https://go.coder-hub.com/76503293.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定