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

possible duplicate of How would you do a "not in" query with Linq? — Eugene Podskal
– Eugene Podskal, Commented Sep 8, 2014 at 9:12
m.Id != cfm.PersonID will not give you "all users that are not mentioned in the Job Tables", it will give you every combination of records in the 2 tables except the ones where that are related by the foreign key. — Coding Flow
– Coding Flow, Commented Sep 8, 2014 at 9:16

Pedro Augusto Freitas de Souza · Accepted Answer · 2014-09-08 09:16:05Z

2

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

answered Sep 8, 2014 at 9:16

Pedro Augusto Freitas de Souza

9607 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Viper · Accepted Answer · 2014-09-08 09:23:56Z

1

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

answered Sep 8, 2014 at 9:23

Viper

2,2441 gold badge22 silver badges44 bronze badges

Comments

Jenish Rabadiya · Accepted Answer · 2014-09-08 09:26:12Z

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.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related