Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

LINQ Inner-Join vs Left-Join

Using extension syntax I'm trying to create a left-join using LINQ on two lists that I have. The following is from the Microsoft help but I've modified it to show that the pets list has no elements. What I'm ending up with is a list of 0 elements. I assume that this is because an inner-join is taking place. What I want to end up with is a list of 3 elements (the 3 Person objects) with null data filled in for the missing elements. i.e. a Left-Join. Is this 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's 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(); 
like image 483
Guy Avatar asked Feb 08 '09 05:02

Guy


People also ask

Can we use left join in Linq?

You can use LINQ to perform a left outer join by calling the DefaultIfEmpty method on the results of a group join.

Is Linq join inner or outer?

One commonly used feature of Language-Integrated Query (LINQ) is the facility to combine two sequences of related data using joins. The standard join operation provides an inner join but with a minor modification can be changed to give a left outer join.

What is inner join in Linq?

A simple inner join that correlates elements from two data sources based on a simple key. An inner join that correlates elements from two data sources based on a composite key. A composite key, which is a key that consists of more than one value, enables you to correlate elements based on more than one property.

Which join is valid in Linq?

LINQ Inner Join Inner Join produces the result from two or more than two tables. So, basically we are meant to get the records from both tables based on matching conditions. Basically in SQL, we use the INNER JOIN keyword to make relationship between both tables. The following is the Linq query for above SQL query.


1 Answers

I think if you want to use extension methods you need to use the 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 have to play around with the selection expression. I'm not sure it would give you want you want in the case where you have a 1-to-many relationship.

I think it's a little easier with the 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(); 
like image 61
tvanfosson Avatar answered Oct 05 '22 15:10

tvanfosson