LINQ Example to Join Multiple Tables Where Null Match Are Expected

Understanding LINQ to Join Multiple Tables Where Null Match Are Expected

By: Sherlee Dizon | Updated: 2014-03-20 | Comments (3) | Related: > Language Integrated Query LINQ

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:

Understanding LINQ to Join Multiple Tables Where Null Match Are Expected

Below are the query results.

The list will contain the pet id, type, name, owner and remarks

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 is the result if we will not use LINQ group join

Below are the query results.

how and when we should use LINQ join methods properly

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.

About the author

Sherlee Dizon is an IT consultant and web application developer with over 14 years of experience as a software and web developer.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2014-03-20

Comments For This Article

Monday, September 15, 2014 - 8:31:24 AM - Mohammad Shahnawaz

Dear,

it is good article.

Wednesday, April 2, 2014 - 7:36:55 AM - sherlee

Try using contains

something like this

SELECT * FROM People
WHERE Firstname IN ('Alex', 'Colin', 'Danny', 'Diego')

A SQL IN is equivalent to a LINQ Contains

In the LINQ (to objects) world there is no 'IN' so you need to reverse the order like and use the Contains method:

var names = new string[] { "Alex", "Colin", "Danny", "Diego" };

var matches = from person in people
where names.Contains(person.Firstname)
select person;

Notice that semantically we've gone from:

value.IN(set)

in SQL to

set.Contains(value)

In LINQ. The result however is the same.

Hope this helps.

Thursday, March 27, 2014 - 5:38:36 PM - Oscar

Sherlee,

Very good example, funny that a few weeks ago I was trying to do a linq query where the key on a table is alpha numeric, I did end up writing the code in T-Sql because I could not figure out how to do a starting and ending strings using link, a sample of what I am referring to is this:

a screen with Starting code : ___________ and Ending code: ___________, there two are my parameters for this example (there are a lot more but for now lets use these two).

There is a index on the table for this code but the code could be ' 100' or 'Sherlee ' the key is nvarchar(12) so it is format whether is a number or a alpha, left or right space filled, If I use T-SQL I can write :

"select xxx,xxx,xxx from table1 where keycode >= " + beginCode + ' and keycode <='" + endcode +"'" (these codes are validated before build the query)

and pass this sql command string to a sql command and that works fine. I just could not find a way to do it in Linq because Linq does not allow >= for strings. Have you have a problem like this or a solution for this example?

Thanks

Oscar

by the way I am using c# also.