Linq query doesn't return correct result if values are in different tables

Question

Hi there: I have two tables.

Tables:

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      |
-------------------------

The code I have for now:

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>();
}

The main idea is if I get type and id values I should get all users that are not mentioned in the Job Tables with JobID == id and if they have city == type. For now, it returns both mentioned and not, and if I remove Distinct() it returns many duplicates. Does anyone know how to solve this problem? Thanks!

Resolved:

Thank you guys!!! I found an answer, this piece of code is actually started to working as it should: 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

Here is same case I have build in my sample project.

 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 );
            }
        }

This will simply print arlene to the console which is not present in Pets collection.

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;

I'm not exactly sure what you need, but this query will give you all Persons which live in the given City (type) and does not have the given Job (id).

Answer 3

I change the return type and if i understand right, you wanna take the People that doesnt have a job.

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