Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Understanding LINQ to Join Multiple Tables Where Null Match Are Expected


By:   |   Last Updated: 2014-03-20   |   Comments (3)   |   Related Tips: 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


next webcast button


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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

Dear,

 

it is good article.


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

 

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

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.


Learn more about SQL Server tools