Performing Joins using LINQ to Objects
In last months DevCares LINQ presentations there were some good questions on using joins within LINQ statements when querying a collection of objects similar to how you would write a SQL join statement when querying a database. In this post I'll show a simple example of performing a LINQ to objects join.
In order to be able to perform a join of objects you first must have a property on the object that refers to either a reference to the other object itself or any sort of identifier value (such as a numeric ID). You can then use that property as the join property in the LINQ statement. For example, look at the following class definitions:
public class Person{ ... public string Name {
get { return _name; }
set { _name = value; }
}
public DateTime? Birthday {
get { return _Birthday; }
set { _Birthday = value; }
}
public int JobID {
get { return _jobID; }
set { _jobID = value; }
}
public Person(string name, DateTime birthday, bool member, int jobID){
this._name = name;
this._Birthday = birthday;
this._jobID = jobID;
}
...
}
public class Job {...
public string Title {
get { return _title; }
set { _title = value; }
}
public string Company {
get { return _company; }
set { _company = value; }
}
public int ID {
get { return _id; }
set { _id = value; }
}
public Region Location {
get { return _location; }
set { _location = value; }
}
public Job(int id, string title, string company, Region location) {
_title = title;
_id = id;
_company = company;
_location = location;
}
...
}
The Person object has a JobID property which is the identifier of the person's job. We can use this to join to a collection of job objects that has more details on the actual job to return in our shaped LINQ result. If we loaded the following person objects and job objects:
List<Person> myPeople =
new List<Person>();
myPeople.Add(new Person("Gerald", DateTime.Parse("1/2/2003"), 3));
myPeople.Add(new Person("Linda", DateTime.Parse("2/2/2002"), 1));
myPeople.Add(new Person("Sarah", DateTime.Parse("4/2/2002"), 1));
myPeople.Add(new Person("Bill", DateTime.Parse("4/5/2006"), 2));
List<Job> jobs = new List<Job>();
jobs.Add(new Job(1, "Developer", "Microsoft", Region.West));
jobs.Add(new Job(2, "Developer", "Microsoft", Region.East));
jobs.Add(new Job(3, "Technical Account Representative", "Microsoft", Region.Central));
We could then perform a LINQ statement to filter person's by specific jobs.
var result = from p in myPeople
from j in jobs
where p.JobID == j.ID
select new{
p.Name, j.Company, j.Title
};
Using an anonymous type as the output we can shape the resulting objects to include the person's name, job title and company. We can then manipulate or display this shaped type as necessary. If you wanted you could have also had a Job property of type Job on the Person object and having a direct object reference on the Job property of every Person. It would still be possible to join the Person.Job property to Job in a LINQ statement to include a filter by an attribute of Job or specific pull out Job property values into the results.
Change the JobID property on Person to:
public Job Job {
get { return _job; }
set { _job = value; }
}
Then after assigning Job object references to the Person objects in the people collection you could use LINQ to join in a query:
var result = from p in myPeople
from j in jobs
where p.Job == j
select new{
p.Name,
j.Company,
j.Title,
j.Location
};
LINQ to objects gives you a lot of flexibility in how you can filter against a collection of objects. With this new VS 2008 feature you can virtually wave good bye to all those previous foreach loops with comparison checks embedded within them!