Problem
The need to combine results in multiple SQL Server tables for producing a list of records or summary reports are often a challenge to us as a developer. In this tip I will show you how I resolve a problem I have faced regarding joining tables where a null matched is expected when using LINQ.
Solution
The join methods provided in the LINQ framework are Join and GroupJoin. These methods perform equijoins or joins that match two data sources based on equality of their keys. A Left outer join is a join in which each element of the first collection is returned, regardless of whether it has any correlated elements in the second collection. It can be performed by calling the DefaultIfEmpty() method on the results of a group join.
Below is a simple left outer join query to help us understand the syntax:
var [dynamic collection] = from [data1] in [collection1]
join [data2] in [collection2]
on [data1].field equals [data2].field
into [group join]
from [data2] in [group join].DefaultIfEmpty()
select {[data1].field, [data2 sub collection variable].field};
Example 1: All rows join and all data returned
Now to give you an example, let’s try to use my AdoptAPet sample database and sample project. I have tables for PetTypes, PetProfile, PetForAdoption and the PetAdoptionTran that keeps the pet adoption transactions. For this example, I will display all the pets for adoption with their corresponding profile. The list will contain the pet id, type, name, owner and remarks. Here is that sample code:
Below are the query results.
Below is the corresponding SQL Server query.
SELECT pfa.PetID, pt.PetTypeDesc, pfa.petname, pf.PetOwner, pf.remarks
FROM dbo.PetForAdoption pfa
JOIN dbo.PetTypes pt
ON pfa.PetTypeID = pt.PetTypeID
JOIN dbo.PetProfile pf
ON pfa.PetID = pf.PetID
ORDER BY pt.PetTypeDesc
Example 2: All rows don’t join but are returned using DefaultIfEmpty()
Now, what if we want to see the list of either pets adopted or not? Here we are now expecting a null match for some of the columns. The list now will display all the pets either adopted or not.
Below are the query results.
Below is the corresponding SQL Server query:
SELECT pfa.PetID, pt.PetTypeDesc, pfa.petname, pf.PetOwner, pf.remarks, pat.AdoptedBy
FROM dbo.PetForAdoption pfa
LEFT OUTER JOIN dbo.PetAdoptionTran pat
ON pfa.PetID = pat.PetID
JOIN dbo.PetTypes pt
ON pfa.PetTypeID = pt.PetTypeID
JOIN dbo.PetProfile pf
ON pfa.PetID = pf.PetID
ORDER BY pt.PetTypeDesc
Example 3: All rows don’t join and are not returned
Below is the result if we do not use the LINQ group join.
Below are the query results.
Below is the corresponding SQL Server query.
SELECT pfa.PetID, pt.PetTypeDesc, pfa.petname, pf.PetOwner, pf.remarks, pat.AdoptedBy
FROM dbo.PetForAdoption pfa
JOIN dbo.PetAdoptionTran pat
ON pfa.PetID = pat.PetID
JOIN dbo.PetTypes pt
ON pfa.PetTypeID = pt.PetTypeID
JOIN dbo.PetProfile pf
ON pfa.PetID = pf.PetID
ORDER BY pt.PetTypeDesc
It is not enough that we know the syntax for joining tables or using the left outer join in LINQ, we need to understand the report requirements of our clients. This understanding will help us determine how and when we should use the LINQ join methods properly.
Next Steps
Learn more by reading and exploring on the following:
- Introduction to Language Integrated Query (LINQ)
- Basic LINQ Query Operations (C#)
- LINQ – Join Operators
- Formulate Joins and Cross-Product Queries
- Language Integrated Query LINQ Tips
- Here is the complete Visual Studio Solution that you can download for you to explore.
Sherlee Dizon works as an I.T. consultant web application developer for SMART Communications in the Philippines. Has 14 years of experience in I.T. as a software and web application developer using MS-Access, MYSQL, MS-SQL 2000, SQL 2005 and SQL 2008 as back-end. For programming languages has used FOXPRO for DOS, Foxpro for Windows, Visual Foxpro, Clipper, Visual Basic 5 and 6, VB .Net 1.0, 2.0, ASP Classic, Visual Studio 2005 C# .net and ASP .net, Visual Studio 2010 C# .net and ASP .net and for reporting Seagate Crystal Report and MS-SQL Server Business Intelligence Development Studio. In 2006, got her MCP title by taking the 070-229 Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition. Has been involved in different software application development for accounting systems, payroll systems, inventory systems, accounts receivable systems, ERP Applications, Web-based HR and Payroll systems and a customized CRM application and is now exploring .net socket programming.