c# - 如何对以下数据集执行 Linq2Sql 查询

Question

我有以下表格:

Person(Id, FirstName, LastName)
{
    (1, "John", "Doe"),
    (2, "Peter", "Svendson")
    (3, "Ola", "Hansen")
    (4, "Mary", "Pettersen")
}

Sports(Id, Name)
{
    (1, "Tennis")
    (2, "Soccer")
    (3, "Hockey")
}

SportsPerPerson(Id, PersonId, SportsId)
{
    (1, 1, 1)
    (2, 1, 3)
    (3, 2, 2)
    (4, 2, 3)
    (5, 3, 2)
    (6, 4, 1)
    (7, 4, 2)
    (8, 4, 3)
}

查看表格，我们可以得出以下事实:
约翰打网球
约翰打曲棍球
彼得踢足球
彼得打曲棍球
奥拉踢足球
玛丽打网球
玛丽踢足球
玛丽打曲棍球

现在我想创建一个 Linq2Sql 查询来检索以下内容:
获取所有玩Hockey 和Soccer

的人

执行查询应该返回:Peter and Mary
有人知道如何在 Linq2Sql 中处理这个问题吗？

Answer 1

Linq 的一大优点是您不必将所有这些都编写为一个整体查询，因为它不会实际执行，直到您枚举结果为止。您可以编写单个查询，但您不必。相反，您可以将其编写为多个单独的查询，以提高可读性并阐明您的意图。

var sportIds = Sports
    .Where(s => s.Name == "Hockey" || s.Name == "Soccer")
    .Select(s => s.Id);

var people = Person.Where(p => SportsPerPerson
    .Count(spp => (spp.PersonId == p.Id) 
    && sportIds.Contains(spp.SportId)) == 2);

首先，我们得到了我们感兴趣的运动 Id 集合。然后，我们在第一个列表中找到所有拥有两项运动的人。虽然表示为多次查询，但Linq会在我们最终枚举结果时，将其全部压缩为一次操作。

编辑:这是一个完整的测试类来说明查询:

using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace L2STest
{
    public class Sport
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public class Person
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

    public class SportPerPerson
    {
        public int Id { get; set; }
        public int PersonId { get; set; }
        public int SportId { get; set; }
    }

    [TestClass]
    public class SportsTest
    {
        private List<Person> persons;
        private List<Sport> sports;
        private List<SportPerPerson> sportsPerPerson;

         [TestInitialize]
         public void MyTestInitialize()
         {
             persons = new List<Person>
             {
                 new Person {Id = 1, FirstName = "John", LastName = "Doe"},
                 new Person {Id = 2, FirstName = "Peter", LastName = "Svendson"},
                 new Person {Id = 3, FirstName = "Ola", LastName = "Hansen"},
                 new Person {Id = 4, FirstName = "Marv", LastName = "Petterson"},
             };

             sports = new List<Sport>
             {
                 new Sport {Id = 1, Name = "Tennis"},
                 new Sport {Id = 2, Name = "Soccer"},
                 new Sport {Id = 3, Name = "Hockey"},
             };

             sportsPerPerson = new List<SportPerPerson>
             {
                 new SportPerPerson {Id = 1, PersonId = 1, SportId = 1}, 
                 new SportPerPerson {Id = 2, PersonId = 1, SportId = 3}, 
                 new SportPerPerson {Id = 3, PersonId = 2, SportId = 2}, 
                 new SportPerPerson {Id = 4, PersonId = 2, SportId = 3}, 
                 new SportPerPerson {Id = 5, PersonId = 3, SportId = 2}, 
                 new SportPerPerson {Id = 6, PersonId = 3, SportId = 1}, 
                 new SportPerPerson {Id = 7, PersonId = 4, SportId = 2}, 
                 new SportPerPerson {Id = 8, PersonId = 4, SportId = 3}, 
             };
         }

        [TestMethod]
        public void QueryTest()
        {
            var sportIds = sports
                .Where(s => s.Name == "Hockey" || s.Name == "Soccer")
                .Select(s => s.Id);

            var people = persons.Where(p => sportsPerPerson
                .Count(spp => (spp.PersonId == p.Id)
                && sportIds.Contains(spp.SportId)) == 2); 

            Assert.AreEqual(2, people.Count());
            Assert.AreEqual("Peter", people.First().FirstName);
            Assert.AreEqual("Marv", people.Last().FirstName);
        }
    }
}