Understanding LINQ to Join Multiple Tables Where Null Match Are Expected


By:   |   Updated: 2014-03-20   |   Comments (3)   |   Related: More > 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 is the corresponding MSSQL query

Below are the query results.

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:



Last Updated: 2014-03-20


get scripts

next tip button



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

View all my tips



Comments For This Article




Monday, September 15, 2014 - 8:31:24 AM - Mohammad Shahnawaz Back To Top (34528)

Dear,

 

it is good article.


Wednesday, April 02, 2014 - 7:36:55 AM - sherlee Back To Top (29940)

 

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 Back To Top (29906)

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.



download





Recommended Reading

Reading XML documents using LINQ to XML

Querying SQL Server databases using LINQ to SQL

Using Stored Procedures with LINQ to SQL

Creating LINQ to SQL Object Models using SqlMetal

Using LINQ to join data from multiple SQL Servers














get free sql tips
agree to terms