如何在 linq to sql 中选择全部

Question

在我的 LINQ 查询中，如果值为 null，我尝试选择全部：

var filteredLesson = (from l in storeDB.lessons
                      where l.statusID == SUBMITTED ||
                            l.statusID == APPROVED &&
                            l.projectID == (l.projectID.HasValue 
                                ? lesson.projectID : /*select all*/  )
                      select l).ToList();

有办法做到这一点吗？

Answer 1

如果值为 null，您只想将其与自身进行比较。这样，如果值为 null，您的行将包含在结果中。

var filteredLesson = (from l in storeDB.lessons
                      where l.statusID == SUBMITTED ||
                      l.statusID == APPROVED &&
                      l.projectID == (l.projectID.HasValue 
                                   ? lesson.projectID : l.projectID )
                      select l).ToList();

Answer 2

也许是这样的？

var filteredLesson = (from l in storeDB.lessons
                      where
                          (l.statusID == SUBMITTED || l.statusID == APPROVED)
                          &&
                          (!l.projectID.HasValue || l.projectID == lesson.projectID)
                      select l
                     ).ToList();

Answer 3

在不知道您的对象模型的情况下，很难完全确定这一点，但我想您应该寻找类似以下的内容：

var filteredLesson = storeDB.lessons
                            .Where(l => (l.statusID == SUBMITTED
                                       || l.statusID == APPROVED)
                                       && !l.projectID.HasValue)
                            .ToList();

Answer 4

以下是使用 xUnit.Net 测试编写的上述许多答案。请注意，原作者在他选择的答案中的评论（这是一个错误的解决方案）给出了他期望的结果。我清理了他的解决方案以删除重复的比较。

示例值位于底部。

    public class SelectAllInLinq2SqlTests
    {
        [Fact]
        public void Unsuccessful()
        {
            var filteredLesson =
                (from l in Lessons
                 where l.statusID == SUBMITTED ||
                       l.statusID == APPROVED &&
                       l.projectID == (l.projectID.HasValue ? projectOne : l.projectID)
                 select l).ToList();

            Assert.Equal(4, filteredLesson.Count);

            // this returned 5 items!
            // good - filtered the null project IDs and statusID == SUBMITTED
            // good - filtered the null project IDs and statusID == APPROVED
            // good - filtered the projectID == projectOne and statusID == SUBMITTED
            // good - filtered the projectID == projectOne and statusID == APPROVED
            // FAIL - filtered the projectID == projectTwo !!!

            // two failures first selecting all records containing a null projectID and any status (we wanted to filter on SUBMITTED or APPROVED)
            // the failure is due to operator precedence the 'l.statusID == SUBMITTED ||' is short-circuiting the rest of the comparison
        }

        [Fact]
        public void Unsuccessful2()
        {
            var filteredLesson =
                (from l in Lessons
                 where !l.projectID.HasValue ||
                       (l.statusID == SUBMITTED || l.statusID == APPROVED && l.projectID == projectOne)
                 select l).ToList();

            Assert.Equal(4, filteredLesson.Count);

            // this returned 6 items!
            // good - filtered the null project IDs and statusID == SUBMITTED
            // good - filtered the null project IDs and statusID == APPROVED
            // FAIL - filtered the null project IDs and statusID == OTHER!
            // good - filtered the projectID == projectOne and statusID == SUBMITTED
            // good - filtered the projectID == projectOne and statusID == APPROVED
            // FAIL - filtered the projectID == projectTwo !!!

            // Two failures:
            //   first selecting all records containing a null projectID and any status (we wanted to filter on SUBMITTED or APPROVED)
            //   second failure is due to operator precedence the 'l.statusID == SUBMITTED ||' is short-circuiting the rest of the comparison
        }

        [Fact]
        public void Unsuccessful2NotQuiteFixed()
        {
            var filteredLesson =
                (from l in Lessons
                 where !l.projectID.HasValue
                       || ((l.statusID == SUBMITTED || l.statusID == APPROVED)
                           && l.projectID == projectOne)
                 select l).ToList();

            Assert.Equal(4, filteredLesson.Count);
            // this returned 5 items!
            // good - filtered the null project IDs and statusID == SUBMITTED
            // good - filtered the null project IDs and statusID == APPROVED
            // FAIL - filtered the null project IDs and statusID == OTHER!
            // good - filtered the projectID == projectOne and statusID == SUBMITTED with a SUBMITTED status
            // good - filtered the projectID == projectOne and statusID == APPROVED with a APPROVED status

            // failures selecting all records containing a null projectID and any status (we wanted to filter on SUBMITTED or APPROVED)
        }

        [Fact]
        public void Sucessful()
        {
            // Questioner's provided solution...

            var filteredLesson =
                (from l in Lessons
                 where !l.projectID.HasValue && (l.statusID == SUBMITTED || l.statusID == APPROVED)
                       || l.projectID == projectOne && (l.statusID == SUBMITTED || l.statusID == APPROVED)
                 select l).ToList();

            Assert.Equal(4, filteredLesson.Count);
        }

        [Fact]
        public void SucessfulRefined()
        {
            // cleaned up without the duplicate status comparisons. Note the parens are necessary.

            var filteredLesson =
                (from l in Lessons
                 where (!l.projectID.HasValue || l.projectID == projectOne)
                       && (l.statusID == SUBMITTED || l.statusID == APPROVED)
                 select l).ToList();

            Assert.Equal(4, filteredLesson.Count);
        }

        public class Lesson
        {
            public int? projectID { get; set; }
            public int statusID { get; set; }
        }

        const int OTHER = 0;
        const int SUBMITTED = 1;
        const int APPROVED = 2;

        const int projectOne = 1;

        Lesson[] Lessons = new[]
                          {
                              new Lesson { projectID = null, statusID = SUBMITTED},
                              new Lesson { projectID = null, statusID = APPROVED},
                              new Lesson { projectID = null, statusID = OTHER},
                              new Lesson { projectID = 1, statusID = SUBMITTED},
                              new Lesson { projectID = 1, statusID = APPROVED},
                              new Lesson { projectID = 1, statusID = OTHER},
                              new Lesson { projectID = 2, statusID = SUBMITTED},
                              new Lesson { projectID = 2, statusID = APPROVED},
                              new Lesson { projectID = 2, statusID = OTHER},
                          };
    }
}