Linq To SQL Join

本文关键字：Join SQL To Linq | 更新日期: 2023-09-27 18:20:49

我正在学习Linq2SQL，我有一个关于左外部联接的问题。在下面的例子中，我认为我正在执行问题表到收藏夹问题表的左外部联接。然而，我不相信我的where子句是正确的。那么，如果我对两个表执行遗漏联接，我应该如何适当地设置where子句呢？

var myResults = from quest in context.MyQuestions
                join favQuest in context.MyFavoriteQuestions on quest.UserFavoriteQuestionId equals favQuest.UserFavoriteQuestionId
                join specialQuest in context.Questions on favQuest.QuestionId equals specialQuest.QuestionId into joinedQuestions
                from specialQuest in joinedQuestions.DefaultIfEmpty()
                where (quest.UserId == userId) &&
                                    ( specialQuest.Id == paramId && (!specialQuest.IsBlue || (specialQuest.IsBlue && canViewBlueQuestion)) &&
                                      (!specialQuest.IsRed || (specialQuest.IsRed && canViewRedQuestion))
                                    )
                              select quest;

Linq To SQL Join

对于LINQ到SQL上下文，建议这样编写左外部联接，因为这实际上会生成SQL left join:

var myResults = from question in context.MyQuestions
from favoriteQuestion in context.MyFavoriteQuestions
    .Where(fc => fc.UserFavoriteQuestionId == question.UserFavoriteQuestionId)
    .DefaultIfEmpty()

还建议（为了提高易读性）将无关（和AND ed）where子句分开：

var myResults = from question in context.MyQuestions
                where question.UserId == userId
                from favoriteQuestion in context.MyFavoriteQuestions
                    .Where(fc => fc.UserFavoriteQuestionId == question.UserFavoriteQuestionId)
                    .DefaultIfEmpty()
                from specialQuestion in context.Questions
                    .Where(sc => sc.QuestionId == favoriteQuestion.QuestionId)
                    .DefaultIfEmpty()
                where specialQuestion.Id == paramId
                where !specialQuestion.IsBlue || (specialQuestion.IsBlue && canViewBlueQuestion)
                where !specialQuestion.IsRed || (specialQuestion.IsRed && canViewRedQuestion)
                select question;