LINQ Inner-Join vs. Left-Join

Question

Using extension syntax. I am trying to create a connection on the left using LINQ in the two lists that I have. Microsoft's help is below, but I modified it to show that the pet list does not have items. What I get is a list of 0 items. I suppose this is because an inner join is taking place. What I want to finish is a list of 3 elements (3 people objects) with zero data filled for missing elements. those. left connection. Is it possible?

Person magnus = new Person { Name = "Hedlund, Magnus" }; Person terry = new Person { Name = "Adams, Terry" }; Person charlotte = new Person { Name = "Weiss, Charlotte" }; //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 daisy = new Pet { Name = "Daisy", Owner = magnus }; List<Person> people = new List<Person> { magnus, terry, charlotte }; //List<Pet> pets = new List<Pet> { barley, boots, whiskers, daisy }; List<Pet> pets = new List<Pet>(); // Create a list of Person-Pet pairs where // each element is an anonymous type that contains a // Pet name and the name of the Person that owns the Pet. var query = people.Join(pets, person => person, pet => pet.Owner, (person, pet) => new { OwnerName = person.Name, Pet = pet.Name }).ToList(); 

Answer 1

I think if you want to use extension methods you need to use GroupJoin

 var query = people.GroupJoin(pets, person => person, pet => pet.Owner, (person, petCollection) => new { OwnerName = person.Name, Pet = PetCollection.Select( p => p.Name ) .DefaultIfEmpty() } ).ToList(); 

You may need to play with an expression of choice. I am not sure if this will give you the desire you want when you have a one-to-many relationship.

It seems to me a little easier with LINQ query syntax

 var query = (from person in context.People join pet in context.Pets on person equals pet.Owner into tempPets from pets in tempPets.DefaultIfEmpty() select new { OwnerName = person.Name, Pet = pets.Name }) .ToList(); 

Answer 2

You need to assemble the combined objects into a set and then apply DefaultIfEmpty, as JPunyon said:

 Person magnus = new Person { Name = "Hedlund, Magnus" }; Person terry = new Person { Name = "Adams, Terry" }; Person charlotte = new Person { Name = "Weiss, Charlotte" }; Pet barley = new Pet { Name = "Barley", Owner = terry }; List<Person> people = new List<Person> { magnus, terry, charlotte }; List<Pet> pets = new List<Pet>{barley}; var results = from person in people join pet in pets on person.Name equals pet.Owner.Name into ownedPets from ownedPet in ownedPets.DefaultIfEmpty(new Pet()) orderby person.Name select new { OwnerName = person.Name, ownedPet.Name }; foreach (var item in results) { Console.WriteLine( String.Format("{0,-25} has {1}", item.OwnerName, item.Name ) ); } 

Outputs:

 Adams, Terry has Barley Hedlund, Magnus has Weiss, Charlotte has 

Answer 3

When the same problem occurs, the following error message appears:

The type of one of the expressions in the join clause is invalid. Type input error in "GroupJoin" call.

Solved, when I used the same property name, it worked.

(...)

 join enderecoST in db.PessoaEnderecos on new { CD_PESSOA = nf.CD_PESSOA_ST, CD_ENDERECO_PESSOA = nf.CD_ENDERECO_PESSOA_ST } equals new { enderecoST.CD_PESSOA, enderecoST.CD_ENDERECO_PESSOA } into eST 

(...)

Answer 4

Here is a good blog post just published by Fabrizio (LINQ author in action) that covers the material in the question I asked. I put it here for reference, as readers of the question will find this helpful.

Convert LINQ queries from query syntax to method / operator syntax

Answer 5

Left connections in LINQ are possible using the DefaultIfEmpty () method. I don't have the exact syntax for your case though ...

In fact, I think that if you just change the pets to pets.DefaultIfEmpty () in the request, this might work ...

EDIT: I really shouldn't answer questions when he is late ...

Answer 6

If you have a database, this is the easiest way:

 var lsPetOwners = ( from person in context.People from pets in context.Pets .Where(mypet => mypet.Owner == person.ID) .DefaultIfEmpty() select new { OwnerName = person.Name, Pet = pets.Name } ).ToList();