Browse All Articles > Joining in LINQ
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:
![TableA]()
![TableB]()
Code samples will be given both in VB and C#.
Before we begin lets have a look at the Class constructors:
C#
VB
Basic Joins
The SQL Equivalent of an INNER JOIN is
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#
VB
Both of these will output the following in the console:
![Inner Join Results LINQ]()
The SQL Equivalent of the LEFT OUTER JOIN is
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#
VB
The output if we run this code in a console application is:
![Left Outer Join Results LINQ]()
The SQL Equivalent of a FULL OUTER JOIN is
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#
VB
Again the results are:
![Full Outer Join Results LINQ]()
Joins with exclusions
In SQL we would have the following query:
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#
VB
The results of the above codes are:
![LINQ Results From Only TableA]()
The query in SQL would be:
The results produced by that SQL query are:
![Exclude Matches]()
This where clause is reflected in the LINQ expressions bellow:
C#
VB
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.
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:
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; }
}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 ClassBasic 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.NameThe expected results are Name of all the persons that have both a Pet and a TransportationType. Results are shown in the image bellow:
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);
}
}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 SubBoth of these will output the following in the console:
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.NameIn 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:
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);
}
}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 SubThe output if we run this code in a console application is:
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.NameThe 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:
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);
}
}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 SubAgain the results are:
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 NULLAnd the results would be:
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);
}
}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 SubThe results of the above codes are:
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 NULLThe results produced by that SQL query are:
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);
}
}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 SubRunning the console will produce:
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.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (4)
Commented:
Having the SQL syntax bridge the LINQ syntax easier to understand.
Thanks for the write up!
Author
Commented:Commented:
Commented: