Transitioning from standard SQL to LINQ to SQL is in many ways very intuitive, both conceptually and syntactically. Some routine tasks are however decidedly unintuitive, at least in comparison to standard SQL. One such task is constructing outer joins. As you will soon see the LINQ to SQL syntax for performing outer joins is far less intuitive for most people than the OUTER JOIN keyword syntax of standard SQL. Hopefully in a future version of LINQ to SQL Microsoft will give us an OUTER JOIN operator but until then here are four ways to do outer joins in LINQ to SQL.
Suppose we have a Patients table and a Claims table where a patient can have zero or more claims and we want to return all patients, regardless of whether they have a claim, and any claim information we have if any. This is of course very simple to do in standard SQL with an outer join. Let's see how to accomplish this with LINQ to SQL.
Method 1: Using a Group Join with DefaultIfEmpty()
MedicalDataContext db = new MedicalDataContext();
var query = ( from p in db.Patients
join c in db.Claims on p.UnqPatientId equals c.PatientId into claims
from c in claims.DefaultIfEmpty()
select new { Patient = p, Claim = c });
foreach (var item in query)
{
System.Diagnostics.Debug.WriteLine(String.Format("Account {0}: #{1}.", item.Patient.AccountNumber, (item.Claim == null ? String.Empty : item.Claim.ClaimNumber)));
}
A group join is a join that uses the into operator to coalesce parent and child information into a single hierarchical collection so you end up with parent information appearing only once in the collection as opposed to flattened out into multiple instances as you would normally get. To convert this group join into a left join we simply add another from clause to select from the group and apply the DefaultIfEmpty() method to force the inclusion of null child references where parents have no children. Note that this method of performing an outer join produces a flattened result.
Method 2: Using GroupJoin()
MedicalDataContext db = new MedicalDataContext();
var query = db.Patients.GroupJoin(db.Claims,
p => p.UnqPatientId,
c => c.PatientId,
(Patient, Claims) => new { Patient, Claims });
foreach (var item in query)
{
System.Diagnostics.Debug.WriteLine(String.Format("Account {0} has {1} claims.", item.Patient.AccountNumber, item.Claims.Count()));
}
In the above example I am using the GroupJoin() method to define my outer join. The first parameter specifies the inner table to join on, the second parameter specifies the key column of the outer table, the third parameter specifies the key column of the inner table and finally the fourth parameter specifies what to return. Note that this method of performing an outer join produces a hierarchical result.
Method 3: Project Directly Into Object Hierarchy
MedicalDataContext db = new MedicalDataContext();
var query = ( from p in db.Patients
select new
{
Patient = p,
Claims = db.Claims.Where(c => c.PatientId == p.UnqPatientId)
});
foreach (var item in query)
{
System.Diagnostics.Debug.WriteLine(String.Format("Account {0} has {1} claims.", item.Patient.AccountNumber, item.Claims.Count()));
}
In this example I am using a Lambda Expression to project the claim data directly into the object hierarchy. Note that this method of performing an outer join produces a hierarchical result.
Method 4: Use Association Property
MedicalDataContext db = new MedicalDataContext();
var query = ( from p in db.Patients
select new
{
Patient = p,
Claims = p.Claims
});
foreach (var item in query)
{
System.Diagnostics.Debug.WriteLine(String.Format("Account {0} has {1} claims.", item.Patient.AccountNumber, item.Claims.Count()));
}
In this example I am using the Claims association property of the Patient object to project the claim data into the object hierarchy. Note that this method of performing an outer join produces a hierarchical result.