如果值位于不同的表中，则Linq查询不会返回正确的结果

Question

你好，我有两张桌子。

表：

Person table:
--------------------------------
| id | Namer | Surename | City |
--------------------------------
|1   |aaa    |aaa       | NewY |
|2   |bbb    |bbb       | Dall |
|3   |ccc    |ccc       | Dall |
|4   |ddd    |ddd       | Dall |
--------------------------------

Job table:
-------------------------
| id | PersonID | JobID |
-------------------------
|1   |1         |1      |
|2   |3         |1      |
|3   |2         |2      |
|4   |3         |2      |
-------------------------

我现在的代码是：

C#:

public IEnumerable<Material> GetAllMaterialsByTypeNotSelected(string type , int id)
{
    return (from m in dataContext.Person
            from cfm in dataContext.Job
            where m.Id != cfm.PersonID && 
            m.City == type &&
            cfm.JobID == id                   
            select m).Distinct().AsEnumerable<Material>();
}

主要的想法是，如果我获得类型和id值，我应该得到所有在带有JobID == id的作业表中没有提到的用户，如果他们有city ==类型的。现在，它返回上述和不返回，如果删除Distinct()，则返回许多重复项。有人知道如何解决这个问题吗？谢谢!

解决方案：

谢谢你们！我找到了一个答案，这段代码实际上已经开始正常工作了: C#：

public IEnumerable<Material> GetAllMaterialsByTypeNotSelected(string type , int id)
        {
            return (from m in dataContext.Person
                   where !(from o in dataContext.Job
                          where o.JobID == id
                          select o.PersonID).Contains(m.Id)&& 
                   m.City == type                  
                   select m).Distinct().AsEnumerable<Material>();
}

Answer 1

我改变了回报的类型，如果我理解得对，你想要接受那些没有工作的人。

public IEnumerable<Person> GetAllMaterialsByTypeNotSelected(string type , int id)
{
 return  dataContext.Person
 .Where(p => dataContext.Job.FirstOrDefault(j => j.PersonId == p.PersonId)== null);
}

Answer 2

var result = from person in ( from p in dataContext.Persons
                              where string.Compare( p.City, type, true ) == 0
                              select p )

             join job in ( from j in dataContext.Jobs
                           where j.JobID == id 
                           select j )
               on person.id equals job.PersonID
             into jobJoinData
             from jobJoinRecord in jobJoinData.DefaultIfEmpty( )

             where jobJoinRecord == null 

             select person;

我不太清楚您需要什么，但是这个查询将为您提供位于给定城市(类型)且没有给定作业(id)的所有Persons。

Answer 3

下面是我在我的示例项目中构建的相同的案例。

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

class Pet
    {
        public string Name { get; set; }
        public Person Owner { get; set; }
    }

static void Main(string[] args)
        {
            Person magnus = new Person { FirstName = "Magnus", LastName = "Hedlund" };
            Person terry = new Person { FirstName = "Terry", LastName = "Adams" };
            Person charlotte = new Person { FirstName = "Charlotte", LastName = "Weiss" };
            Person arlene = new Person { FirstName = "Arlene", LastName = "Huff" };

            Pet barley = new Pet { Name = "Barley", Owner = terry };
            Pet boots = new Pet { Name = "Boots", Owner = terry };
            Pet whiskers = new Pet { Name = "Whiskers", Owner = charlotte };
            Pet bluemoon = new Pet { Name = "Blue Moon", Owner = terry };
            Pet daisy = new Pet { Name = "Daisy", Owner = magnus };

            // Create two lists.
            List<Person> people = new List<Person> { magnus, terry, charlotte, arlene };
            List<Pet> pets = new List<Pet> { barley, boots, whiskers, bluemoon, daisy };

            var query = from person in people
                        join pet in pets on person equals pet.Owner into gj
                        from subpet in gj.DefaultIfEmpty() where subpet == null
                        select new { person.FirstName};

            foreach (var v in query)
            {
                Console.WriteLine(v.FirstName );
            }
        }

这将简单地将arlene打印到控制台，这在宠物集合中是不存在的。