How to join two collections with LINQ
- by JustinGreenwood
Here is a simple and complete example of how to perform joins on two collections with LINQ. I wrote it for a friend to show him, in one simple file, the power of LINQ queries and anonymous objects.
In the file below, there are two simple data classes defined: Person and Item. In the beginning of the main method, two collections are created. Note that the Item's OwnerId field reference the PersonId of a Person object. The effect of the LINQ query below is equivalent to a SQL statement looking like this:
select
Person.PersonName as OwnerName,
Item.ItemName as OwnedItem
from Person
inner join Item on Item.OwnerId = Person.PersonId
order by Item.ItemName desc;
using System;
using System.Collections.Generic;
using System.Linq;
namespace LinqJoinAnonymousObjects
{
class Program
{
class Person
{
public int PersonId { get; set; }
public string PersonName { get; set; }
}
class Item
{
public string ItemName { get; set; }
public int OwnerId { get; set; }
}
static void Main(string[] args)
{
// Create two collections: one of people, and another with their possessions.
var people = new List<Person> {
new Person { PersonId=1, PersonName="Justin" },
new Person { PersonId=2, PersonName="Arthur" },
new Person { PersonId=3, PersonName="Bob" }
};
var items = new List<Item> {
new Item { OwnerId=1, ItemName="Armor" },
new Item { OwnerId=1, ItemName="Book" },
new Item { OwnerId=2, ItemName="Chain Mail" },
new Item { OwnerId=2, ItemName="Excalibur" },
new Item { OwnerId=3, ItemName="Bubbles" },
new Item { OwnerId=3, ItemName="Gold" }
};
// Create a new, anonymous composite result for person id=2.
var compositeResult = from p in people
join i in items on p.PersonId equals i.OwnerId
where p.PersonId == 2
orderby i.ItemName descending
select new
{
OwnerName = p.PersonName,
OwnedItem = i.ItemName
};
// The query doesn't evaluate until you iterate through the query or convert it to a list
Console.WriteLine("[" + compositeResult.GetType().Name + "]");
// Convert to a list and loop through it.
var compositeList = compositeResult.ToList();
Console.WriteLine("[" + compositeList.GetType().Name + "]");
foreach (var o in compositeList)
{
Console.WriteLine("\t[" + o.GetType().Name + "] " + o.OwnerName + " - " + o.OwnedItem);
}
Console.ReadKey();
}
}
}
The output of the program is below:
[WhereSelectEnumerableIterator`2]
[List`1]
[<>f__AnonymousType1`2] Arthur - Excalibur
[<>f__AnonymousType1`2] Arthur - Chain Mail
