How to join two collections with LINQ
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