即使使用LINQ联接不存在到子表数据的链接，也从两个联接的表返回结果

m3eecexj 于 2023-06-27 发布在其他

关注(0)|答案(1)|浏览(95)

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

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

我上面有这些表格，但正如你所看到的，问题3还没有投票。那么我如何仍然提取问题3的TblPolls行的数据，考虑到这个Poll没有投票，所以结果应该是返回问题3的行，但投票中的值为null。我猜这是linq中的一种左连接。我已经尝试过了，但我没有得到我想要的结果：

poll = (
        from polls in ctx.TblPolls
        join votes in ctx.TblVotes on polls.Guid equals votes.PollGuid into gg
        where polls.Guid == guid
        from ff in gg.DefaultIfEmpty()
        where (ff.UserId == userId || ff.UserId == null) && ff.PollGuid == guid
        select new
        {
            polls.UserId,
            polls.Id,
            polls.Guid,
            polls.Question,
            polls.Description,
            polls.Date,
            polls.VideoId,
            value = gg.Where(x => x.UserId == userId && x.PollGuid == guid).FirstOrDefault().Value,
            VoteCount = gg.Count(),
        }
        ).ToList();

发生的事情是，如果用户投票不存在的TblPoll问题im得到空数组时，问题应该返回，即使没有投票的用户。
那么，linq查询如何按照我的建议实现reuslts呢？

linq

来源：https://stackoverflow.com/questions/76509996/return-results-from-a-two-joined-tables-even-if-the-link-to-the-child-table-data

1条答案

按热度按时间

0sgqnhkj1#

您有两个表，TblPolls带有Guid属性，TblVotes带有UserId和PollGuid属性，您希望查找给定poll guid的所有投票，并统计给定用户id的所有投票--或者如果没有投票，则不统计任何投票。
您可以使用left outer join执行此操作，如下所示：

var selectedPolls = (
    from poll in ctx.TblPolls
    // Filter on polls.Guid before the join, for performance and simplicity.
    where poll.Guid == guid
    // Filter on vote.UserId and vote.PollGuid before the join, for performance and simplicity.
    join vote in ctx.TblVotes.Where(v => v.UserId == userId && v.PollGuid == guid) on poll.Guid equals vote.PollGuid into gg
    from vote in gg.DefaultIfEmpty() // At this point vote will be null if gg was empty.
    select new
    {
        poll.UserId,
        poll.Id, // Not shown in question.
        poll.Guid,
        poll.Question,
        poll.Description, // Not shown in question.
        poll.Date, // Not shown in question.
        poll.VideoId, // Not shown in question.
        //What is vote.Value?  It isn't shown in the question so I don't know how to fix this line:
        //value = gg.Where(x => x.UserId == userId && x.PollGuid == guid).FirstOrDefault().Value,
        //But possibly this is correct:
        value = (vote != null ? vote.Value : -1), // Substitute whatever default value you want here.
        VoteCount = gg.Count(),
    }
).ToList();

注意事项：

为了性能和简单性，您应该在执行join之前过滤投票的Guid和投票的UserId和PollGuid。
由于TblVotes的内容是类而不是结构体，因此如果gg为空，则from ff in gg.DefaultIfEmpty()将返回null。那么ff.UserId将导致异常。
您的TblVotes没有属性Value，所以我不知道如何修复

value = gg.Where(x => x.UserId == userId && x.PollGuid == guid).FirstOrDefault().Value

既然你已经在循环gg了，也许你只是想：

value = (vote != null ? vote.Value : -1),

为了清晰起见，我修改了一些变量名。

演示小提琴here。

redoc01更新：

解决方案几乎就在那里了，我只是修改了这一行：

join vote in ctx.TblVotes.Where(v => v.UserId == userId && v.PollGuid == guid) on poll.Guid equals vote.PollGuid into gg
where poll.Guid == guid <----- to here
from vote in gg.DefaultIfEmpty() // At this point vote will be null if gg was empty.

这一行：

from value = vote?.Value, to value = vote.Value,

现在工作！...

赞(0）回复(0）举报 2023-06-27

我来回答

即使使用LINQ联接不存在到子表数据的链接，也从两个联接的表返回结果

1条答案

相关问题

热门标签

最新问答