<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Joining in LINQ

Published on
20,287 Points
10,187 Views
6 Endorsements
Last Modified:
Approved
A Few Words About LINQ
LINQ is a high level querying lanquage. It was made in order to unify the way a programmer queries different sources, like databases, lists and objects. That is the power of it, as you may change datasource and your LINQ may remain the same producing the same results.
 
As it is a querying lanquage you may find similarities with T-SQL, such as keywords (SELECT, FROM, WHERE), but there are also differences. The major difference is the syntax, but also the simplicity of LINQ when it comes to complex queries.  

Of course LINQ is not always the best choice. For instance LINQ is not good for handling bulk inserts.

Now lets see how LINQ implements various cases of JOIN queries, compared with T-SQL.

Introduction
This article is meant to give a helping hand to people that have dificulties when it comes to joining their LINQ queries.

In all examples we will use two tables names for simplicity TableA and TableB. I am using "tables" as I am fond of SQL, but this article also applies to whatever LINQ applies to.

Below are the the tables we are going to use through this article:

TableATableB
Code samples will be given both in VB and C#.

Before we begin lets have a look at the Class constructors:
C#
    public class TableA
    {
        public string Name { get; set; }
        public string Pet{ get; set; }
    }
    public class TableB
    {
        public string Name { get; set; }
        public string TransportationType { get; set; }
    }

Open in new window


VB
    Public Class TableA
        Public Property Name() As String
            Get
                Return m_Name
            End Get
            Set(value As String)
                m_Name = Value
            End Set
        End Property
        Private m_Name As String
        Public Property Pet() As String
            Get
                Return m_Pet
            End Get
            Set(value As String)
                m_Pet = Value
            End Set
        End Property
        Private m_Pet As String
    End Class
    Public Class TableB
        Public Property Name() As String
            Get
                Return m_Name
            End Get
            Set(value As String)
                m_Name = Value
            End Set
        End Property
        Private m_Name As String
        Public Property TransportationType() As String
            Get
                Return m_TransportationType
            End Get
            Set(value As String)
                m_TransportationType = Value
            End Set
        End Property
        Private m_TransportationType As String
    End Class

Open in new window


Basic Joins

INNER JOIN
INNER JOIN is the most common join in LINQ and the simplest one in a code point of view. INNER JOIN will give records that exist in both tables.
The SQL Equivalent of an INNER JOIN is
SELECT	*
FROM	TableA A
		INNER JOIN TableB B
			ON	A.Name = B.Name

Open in new window


The expected results are Name of all the persons that have both a Pet and a TransportationType. Results are shown in the image bellow:
Inner Join Results
Bellow is the code for creating the Inner Join using LINQ:
C#
        static void InnerJoin(List<TableA> TableA,List<TableB> TableB)
        {
            var query = from a in TableA
                        join b in TableB
                        on a.Name equals b.Name
                        select new {a.Name,a.Pet,b.TransportationType };
            foreach(var person in query)
            {
                Console.WriteLine(person);
            }

        }

Open in new window


VB
        Private Shared Sub InnerJoin(TableA As List(Of TableA), TableB As List(Of TableB))
            Dim query = From a In TableA _
                        Join b In TableB _
                        On a.Name Equals b.Name
                        Select New With
                        { _
                         a.Name, _
                         a.Pet, _
                         b.TransportationType _
                        }
            For Each person In query
                Console.WriteLine(person)
            Next

        End Sub

Open in new window


Both of these will output the following in the console:
Inner Join Results LINQ
LEFT OUTER JOIN
LEFT OUTER JOIN gets all records from the first table, no matter if there is a match on the second table. If there is a match, it gets the values of the second table, if there is not a match then it returns NULL.
The SQL Equivalent of the LEFT OUTER JOIN is
SELECT	*
FROM	TableA A
		LEFT OUTER JOIN Table B
			ON	A.Name = B.Name

Open in new window


In our example, the results should be Name of persons that do have a pet and if they have a TransportationType display that too.
In SQL the results are:
Left Outer Join Results
In this example, in LINQ we are going to use the DefaultIfEmpty method, that returns a default value if the join doesn't have a match. Note: If we were using LINQ2SQL we wouldn't have to define the default value as it is defined by the system. In our example though we must define the default value to be null or nothing.

C#
        static void LeftOuterJoin(List<TableA> TableA, List<TableB> TableB)
        {
            TableB DefaultTableB = new TableB { Name = null, TransportationType = null };

            var query = from a in TableA
                                join b in TableB
                                on a.Name equals b.Name
                                into temp
                                from b in temp.DefaultIfEmpty(DefaultTableB)
                                select new
                                {
                                    a.Name,
                                    a.Pet,
                                    TransportationType = b.TransportationType
                                };
            foreach (var person in query)
            {
                Console.WriteLine(person);
            }
        }

Open in new window


VB
        Private Shared Sub LeftOuterJoin(TableA As List(Of TableA), TableB As List(Of TableB))
            Dim DefaultTableB As New TableB() With { _
              .Name = Nothing, _
              .TransportationType = Nothing _
            }

            Dim query = From a In TableA _
                                Group Join b In TableB _
                                On a.Name Equals b.Name Into temp = Group _
                                From b In temp.DefaultIfEmpty(DefaultTableB) _
                                Select New With { _
                                    a.Name, _
                                    a.Pet, _
                                    .TransportationType = b.TransportationType _
                                }

            For Each person In query
                Console.WriteLine(person)
            Next
        End Sub

Open in new window


The output if we run this code in a console application is:
Left Outer Join Results LINQ

FULL OUTER JOIN
FULL OUTER JOIN is the Join that will get all records no matter if they exist on both tables.
The SQL Equivalent of a FULL OUTER JOIN is
SELECT	*
FROM	TableA A
		FULL OUTER JOIN TableB B
			ON	A.Name = B.Name

Open in new window


The expected results of these would be a full list of all the persons in both Tables displaying their Name, Pet and/or TransportationType. Following are the results in SQL:
Full Outer Join Results
In this case LINQ is a little bit more complicated. It actually consists of three queries with the third being the union of the first two. The first two queries are LEFT OUTER JOINs as seen previously.

C#
        static void FullOuterJoin(List<TableA> TableA, List<TableB> TableB)
        {
            TableA DefaultTableA = new TableA { Name=null, Pet = null };
            TableB DefaultTableB = new TableB { Name = null, TransportationType = null };

            var leftOuterJoin = from a in TableA
                                join b in TableB
                                on a.Name equals b.Name
                                into temp
                                from b in temp.DefaultIfEmpty(DefaultTableB)
                                select new
                                {
                                    a.Name,
                                    a.Pet,
                                    TransportationType=b.TransportationType
                                };
            var rightOuterJoin = from b in TableB
                                 join a in TableA
                                 on b.Name equals a.Name
                                 into temp
                                 from a in temp.DefaultIfEmpty(DefaultTableA)
                                 select new
                                 {
                                     b.Name,
                                     a.Pet,
                                     TransportationType = b.TransportationType
                                 };
            var query = leftOuterJoin.Union(rightOuterJoin);
            foreach (var person in query)
            {
                Console.WriteLine(person);
            }
        }

Open in new window


VB
        Private Shared Sub FullOuterJoin(TableA As List(Of TableA), TableB As List(Of TableB))
            Dim DefaultTableA As New TableA() With { _
              .Name = Nothing, _
              .Pet = Nothing _
            }
            Dim DefaultTableB As New TableB() With { _
              .Name = Nothing, _
              .TransportationType = Nothing _
            }

            Dim leftOuterJoin = From a In TableA _
                                Group Join b In TableB _
                                On a.Name Equals b.Name Into temp = Group _
                                From b In temp.DefaultIfEmpty(DefaultTableB) _
                                Select New With { _
                                    a.Name, _
                                    a.Pet, _
                                    .TransportationType = b.TransportationType _
                                }
            Dim rightOuterJoin = From b In TableB _
                                 Group Join a In TableA _
                                 On b.Name Equals a.Name Into temp = Group _
                                 From a In temp.DefaultIfEmpty(DefaultTableA) _
                                 Select New With { _
                                 b.Name, _
                                 a.Pet, _
                                 .TransportationType = b.TransportationType _
                                 }
            Dim query = (From q In leftOuterJoin.Union(rightOuterJoin) _
                        Select q.Name, q.Pet, q.TransportationType).Distinct

            For Each person In query
                Console.WriteLine(person)
            Next
        End Sub

Open in new window


Again the results are:
Full Outer Join Results LINQ
Joins with exclusions
Data that exist only on the first(LEFT) Table
There will be scenarios that we want all the data that exist in one table but do not exist in the other. In our example this translates to get the Name of all persons that do have a pet but do not have a TransportationType.
In SQL we would have the following query:
SELECT	*
FROM	TableA A
		LEFT OUTER JOIN TableB B
			ON	A.Name = B.Name
WHERE	B.Name IS NULL

Open in new window


And the results would be:
Results From Only TableA
In code we get the same code with LEFT OUTER JOIN but we add a where clause that the id of the second table should be null or nothing, as we have done in SQL.

C#
        static void ExcludeTableB(List<TableA> TableA, List<TableB> TableB)
        {
            TableB DefaultTableB = new TableB { Name = null, TransportationType = null };

            var query = from a in TableA
                        join b in TableB
                        on a.Name equals b.Name
                        into temp
                        from b in temp.DefaultIfEmpty(DefaultTableB)
                        where b.Name==null
                        select new
                        {
                            a.Name,
                            a.Pet,
                            TransportationType = b.TransportationType
                        };
            foreach (var person in query)
            {
                Console.WriteLine(person);
            }
        }

Open in new window


VB
        Private Shared Sub ExcludeTableB(TableA As List(Of TableA), TableB As List(Of TableB))
            Dim DefaultTableB As New TableB() With { _
              .Name = Nothing, _
              .TransportationType = Nothing _
            }

            Dim query = From a In TableA _
                                Group Join b In TableB _
                                On a.Name Equals b.Name Into temp = Group _
                                From b In temp.DefaultIfEmpty(DefaultTableB) _
                                Where b.Name Is Nothing
                                Select New With { _
                                    a.Name, _
                                    a.Pet, _
                                    .TransportationType = b.TransportationType _
                                }

            For Each person In query
                Console.WriteLine(person)
            Next
        End Sub

Open in new window


The results of the above codes are:
LINQ Results From Only TableA
Data that exist only either on the first(LEFT) or the second(RIGHT) Table
In the last example we need to find differences in the two tables for a possible merge. So what we need to do is get the Name of the persons that either have a Pet or a TransportationType.
The query in SQL would be:
SELECT	*
FROM	TableA A
		FULL OUTER JOIN TableB B
			ON	A.Name = B.Name
WHERE	B.Name IS NULL
		OR A.Name IS NULL

Open in new window


The results produced by that SQL query are:
Exclude Matches
This where clause is reflected in the LINQ expressions bellow:

C#
        static void ExcludeMatches(List<TableA> TableA, List<TableB> TableB)
        {
            TableA DefaultTableA = new TableA { Name = null, Pet = null };
            TableB DefaultTableB = new TableB { Name = null, TransportationType = null };

            var leftOuterJoin = from a in TableA
                                join b in TableB
                                on a.Name equals b.Name
                                into temp
                                from b in temp.DefaultIfEmpty(DefaultTableB)
                                select new
                                {
                                    NameA = a.Name,
                                    a.Pet,
                                    NameB = b.Name,
                                    TransportationType = b.TransportationType
                                };
            var rightOuterJoin = from b in TableB
                                 join a in TableA
                                 on b.Name equals a.Name
                                 into temp
                                 from a in temp.DefaultIfEmpty(DefaultTableA)
                                 select new
                                 {
                                    NameA = a.Name,
                                    a.Pet,
                                    NameB = b.Name,
                                    TransportationType = b.TransportationType
                                 };
            var query = from q in leftOuterJoin.Union(rightOuterJoin)
                        where (q.NameA ==null|| q.NameB==null)
                        select q;
            foreach (var person in query)
            {
                Console.WriteLine(person);
            }
        }

Open in new window


VB
        Private Shared Sub ExcludeMatches(TableA As List(Of TableA), TableB As List(Of TableB))
            Dim DefaultTableA As New TableA() With { _
              .Name = Nothing, _
              .Pet = Nothing _
            }
            Dim DefaultTableB As New TableB() With { _
              .Name = Nothing, _
              .TransportationType = Nothing _
            }

            Dim leftOuterJoin = From a In TableA _
                                Group Join b In TableB _
                                On a.Name Equals b.Name Into temp = Group _
                                From b In temp.DefaultIfEmpty(DefaultTableB) _
                                Select New With { _
                                    .NameA = a.Name, _
                                    a.Pet, _
                                    .NameB = b.Name, _
                                    .TransportationType = b.TransportationType _
                                }
            Dim rightOuterJoin = From b In TableB _
                                 Group Join a In TableA _
                                 On b.Name Equals a.Name Into temp = Group _
                                 From a In temp.DefaultIfEmpty(DefaultTableA) _
                                 Select New With { _
                                    .NameA = a.Name, _
                                    a.Pet, _
                                    .NameB = b.Name, _
                                    .TransportationType = b.TransportationType _
                                 }
            Dim query = (From q In leftOuterJoin.Union(rightOuterJoin) _
                         Where q.NameA Is Nothing Or q.NameB Is Nothing
                        Select q.NameA, q.Pet, q.NameB, q.TransportationType).Distinct

            For Each person In query
                Console.WriteLine(person)
            Next
        End Sub

Open in new window


Running the console will produce:
Exclude Matches LINQ
References
In this article i have used as a reference a very nice visual representation of SQL joins, that really have helped me understand the concept and it may be found here

Conclusion
Using LINQ may be a little bit strange, especially if you are used to writing SQL. In the end you get used to the syntax and with the vast of resources out there you may accomplish almost everything. I have written this article just to give some examples and a starting point to whoever might need it. My examples may not be the best but they do work so you may find them helpful.
6
Comment
4 Comments
LVL 5

Expert Comment

by:allanau20
This is a great starting point especially when all you'll need is inner or left joins.
Having the SQL syntax bridge the LINQ syntax easier to understand.

Thanks for the write up!
0
LVL 23

Author Comment

by:Ioannis Paraskevopoulos
It is good to know that this is helpful. Thanks for your comment.
0
LVL 51

Expert Comment

by:PortletPaul
Giannis: fantastic. Thanks for this, very helpful indeed. Well done!
0

Expert Comment

by:Nico2011
Excellent as always!  Thanks very much!
0

Featured Post

Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Join & Write a Comment

Key to your CPU's ability to stay cool is to use the right amount of thermal paste and apply it correctly. In other words you want as much thermal conductivity between CPU and the cooling block. Use a quality thermal paste and apply it in a manner…
Please check the video also in regards to recovery of deleted emails from office 365 admin center and through the MFCMAPI tool. I have mentioned each and every step with the proper steps that need to be taken care of.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month