英文:
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 });
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论