I have this data structure:
class Person
{
public string FirstName { get; set; }
}
class Pet
{
public int Id { get; set; }
public string Name { get; set; }
}
class Link
{
public Person Person { get; set; }
public int PetId { get; set; }
}
and this data:
List<Person> people = new List<Person>
{
new Person {FirstName = "Foo"},
new Person {FirstName = "Bar"}
};
List<Pet> pets = new List<Pet>
{
new Pet {Id = 1, Name = "FooBoy"},
};
List<Link> links = new List<Link>
{
new Link {Person = people.First(), PetId = pets.First().Id}
};
Now i want to get the list of all people and their pet names(or null, if there is no pet) The result should be
Foo - FooBoy
Bar - null
I tried
var query = from person in people
join lnk in links on person equals lnk.Person into linkPets
from link in linkPets.DefaultIfEmpty()
join p in pets on link.PetId equals p.Id into subPets
from subPet in subPets.DefaultIfEmpty()
select new { person.FirstName, PetName = (subPet == null ? String.Empty : subPet.Name) };
But i get null reference exception on link.Pet
. If i remove linkPets.DefaultIfEmpty()
, i get only the first person.
Before the question changed
(Originally the link table had a reference to the pet, not an ID.)
It looks to me like you only need one join, because you can just use the Pet
property from the link, where there is one:
var query = from person in people
join lnk in links on person equals lnk.Person into linkPets
from link in linkPets.DefaultIfEmpty()
select new { person.FirstName,
PetName = link == null ? "" : link.Pet.Name };
After the question changed
I've left the original solution above because it's easy to transform the modified question into the original one - just do a join between pets
and links
first:
var fullLinks = from link in links
join pet in pets on link.PetId equals pet.Id
select new { link.Person, Pet = pet };
var query = from person in people
join lnk in fullLinks on person equals lnk.Person into linkPets
from link in linkPets.DefaultIfEmpty()
select new { person.FirstName,
PetName = link == null ? "" : link.Pet.Name };
You could do it in a single statement if you want to, but I wouldn't:
var query = from person in people
join lnk in (from link in links
join pet in pets on link.PetId equals pet.Id
select new { link.Person, Pet = pet } )
on person equals lnk.Person into linkPets
from link in linkPets.DefaultIfEmpty()
select new { person.FirstName,
PetName = link == null ? "" : link.Pet.Name };
See more on this question at Stackoverflow