How to use SQL Join in LINQ?

How to use SQL Join in LINQ like INNER JOIN, LEFT OUTER JOIN, CROSS JOIN, and GROUP JOIN explain with example?

Last updated:9/24/2021 2:36:20 AM

1 Answers

Ravi Vishwakarma
Ravi Vishwakarma

Join in LINQ

 In LINQ, join operators are used to join more than one list or the collection and get the matched data from the collection based on condition. The LINQ join operator functionality are the same as the SQL Join query.

LINQ has different types of joins

  1. Inner Join - it returns the elements from the collection which satisfy the condition
  2. Left Outer Join - it return the all element from the left side collection and matched collection from right side collection
  3. Cross Join - it returns some product of elements from the collection
  4. Group Join -

Inner Join Example - 

using System;

using System.Collections.Generic;
using System.Linq;
public class Program
{
 public static void Main()
 {
  List<Subject> subjects = new List<Subject>(){
   new Subject() { SubjectId = 1, SubjectName = 'C#'},
   new Subject() { SubjectId = 2, SubjectName = 'C++'},
   new Subject() { SubjectId = 3, SubjectName = 'C'},
   new Subject() { SubjectId = 4, SubjectName = 'Python'},
   new Subject() { SubjectId = 5, SubjectName = 'Java'}
  };
  List<Student> students = new List<Student>(){
  new Student() { StudentId = 1, Name = 'Ashu', Marks = 500,SubjectId=2 },
  new Student() { StudentId = 2, Name = 'Shyam', Marks = 500,SubjectId=3},
  new Student() { StudentId = 3, Name = 'Shriyam', Marks = 400,SubjectId=2 },
  new Student() { StudentId = 4, Name = 'Sunny', Marks = 550,SubjectId=4 },
  new Student() { StudentId = 5, Name = 'Ram', Marks = 600,SubjectId=3 },
  new Student() { StudentId = 6, Name = 'Krishna', Marks = 550,SubjectId=2 },
  new Student() { StudentId = 7, Name = 'Anupam', Marks = 550,SubjectId=5 }
  } ;
  // inner join command
  var result = from student in students
  join subject in subjects
  on student.SubjectId equals subject.SubjectId
  select new
  {
  StudentName = student.Name,
  SubjectName = subject.SubjectName,
  ID = student.StudentId
  };
  Console.WriteLine('Inner Join\n');
  Console.WriteLine('{0,3} {1,-15} {2,-10}','ID', 'StudentName', 'Subject');
  foreach(var item in result)
   Console.WriteLine('{0,3} {1,-15} {2,-10}',item.ID, item.StudentName,item.SubjectName);
  //
  Console.ReadLine();
 }
}
class Student

{
public int StudentId { get; set; }
public string Name { get; set; }
public int Marks { get; set; }
public int SubjectId { get; set; }
}
class Subject{

 public int SubjectId { get; set; }
 public string SubjectName { get; set; }
}

Output

Inner Join

 ID StudentName Subject
  1 Ashu C++
  2 Shyam C
  3 Shriyam C++
  4 Sunny Python
  5 Ram C
  6 Krishna C++
  7 Anupam Java

Left Outer Example - 

using System;

using System.Collections.Generic;
using System.Linq;
public class Program
{
 public static void Main()
 {
  List<Subject> subjects = new List<Subject>(){
   new Subject() { SubjectId = 1, SubjectName = 'C#'},
   new Subject() { SubjectId = 2, SubjectName = 'C++'},
   new Subject() { SubjectId = 3, SubjectName = 'C'},
   new Subject() { SubjectId = 4, SubjectName = 'Python'},
   new Subject() { SubjectId = 5, SubjectName = 'Java'}
  };
  List<Student> students = new List<Student>(){
  new Student() { StudentId = 1, Name = 'Ashu', Marks = 500,SubjectId=2 },
  new Student() { StudentId = 2, Name = 'Shyam', Marks = 500,SubjectId=3},
  new Student() { StudentId = 3, Name = 'Shriyam', Marks = 400 },
  new Student() { StudentId = 4, Name = 'Sunny', Marks = 550,SubjectId=4 },
  new Student() { StudentId = 5, Name = 'Ram', Marks = 600,SubjectId=3 },
  new Student() { StudentId = 6, Name = 'Krishna', Marks = 550,SubjectId=5 },
  new Student() { StudentId = 7, Name = 'Anupam', Marks = 550 }
  } ;
  // left outer join command
  var result = from student in students
  join subject in subjects
  on student.SubjectId equals subject.SubjectId into stusub
  from ss in stusub.DefaultIfEmpty()
  select new
  {
  StudentName = student.Name,
  SubjectName = ss == null ? 'No Subject Choose' : ss.SubjectName,
  ID = student.StudentId
  };
  Console.WriteLine('Left outer Join\n');
  Console.WriteLine('{0,3} {1,-15} {2,-10}','ID', 'StudentName', 'Subject');
  foreach(var item in result)
   Console.WriteLine('{0,3} {1,-15} {2,-10}',item.ID, item.StudentName,item.SubjectName);
  //
  Console.ReadLine();
 }
}
class Student

{
public int StudentId { get; set; }
public string Name { get; set; }
public int Marks { get; set; }
public int SubjectId { get; set; }
}
class Subject{

 public int SubjectId { get; set; }
 public string SubjectName { get; set; }
}

Output

Left outer Join

 ID StudentName Subject
  1 Ashu C++
  2 Shyam C
  3 Shriyam No Subject Choose
  4 Sunny Python
  5 Ram C
  6 Krishna Java
  7 Anupam No Subject Choose

Cross Join LINQ

using System;

using System.Collections.Generic;
using System.Linq;
public class Program
{
 public static void Main()
 {
  List<Subject> subjects = new List<Subject>(){
   new Subject() { SubjectId = 1, SubjectName = 'C#'},
   new Subject() { SubjectId = 2, SubjectName = 'C++'},
   new Subject() { SubjectId = 3, SubjectName = 'C'},
   new Subject() { SubjectId = 4, SubjectName = 'Python'},
   new Subject() { SubjectId = 5, SubjectName = 'Java'}
  };
  List<Student> students = new List<Student>(){
  new Student() { StudentId = 1, Name = 'Ashu', Marks = 500,SubjectId=2 },
  new Student() { StudentId = 2, Name = 'Shyam', Marks = 500,SubjectId=3},
  new Student() { StudentId = 3, Name = 'Shriyam', Marks = 400 },
  new Student() { StudentId = 4, Name = 'Sunny', Marks = 550,SubjectId=4 },
  new Student() { StudentId = 5, Name = 'Ram', Marks = 600,SubjectId=3 },
  new Student() { StudentId = 6, Name = 'Krishna', Marks = 550,SubjectId=5 },
  new Student() { StudentId = 7, Name = 'Anupam', Marks = 550 }
  } ;
  // left outer join command
  var result = from student in students
      from subject in subjects
  select new
  {
  StudentName = student.Name,
  SubjectName = subject.SubjectName,
  ID = student.StudentId
  };
  Console.WriteLine('Cross Join\n');
  Console.WriteLine('{0,3} {1,-15} {2,-10}','ID', 'StudentName', 'Subject');
  foreach(var item in result)
   Console.WriteLine('{0,3} {1,-15} {2,-10}',item.ID, item.StudentName,item.SubjectName);
  //
  Console.ReadLine();
 }
}
class Student
{
public int StudentId { get; set; }
public string Name { get; set; }
public int Marks { get; set; }
public int SubjectId { get; set; }
}
class Subject{
 public int SubjectId { get; set; }
 public string SubjectName { get; set; }
}

Example

Cross Join

 ID StudentName Subject
  1 Ashu C#
  1 Ashu C++
  1 Ashu C
  1 Ashu Python
  1 Ashu Java
  2 Shyam C#
  2 Shyam C++
  2 Shyam C
  2 Shyam Python
  2 Shyam Java
  3 Shriyam C#
  3 Shriyam C++
  3 Shriyam C
  3 Shriyam Python
  3 Shriyam Java
  4 Sunny C#
  4 Sunny C++
  4 Sunny C
  4 Sunny Python
  4 Sunny Java
  5 Ram C#
  5 Ram C++
  5 Ram C
  5 Ram Python
  5 Ram Java
  6 Krishna C#
  6 Krishna C++
  6 Krishna C
  6 Krishna Python
  6 Krishna Java
  7 Anupam C#
  7 Anupam C++
  7 Anupam C
  7 Anupam Python
  7 Anupam Java    

Cross Join LINQ

using System;
using System.Collections.Generic;
using System.Linq;
public class Program
{
 public static void Main()
 {
  List<Subject> subjects = new List<Subject>(){
   new Subject() { SubjectId = 1, SubjectName = 'C#'},
   new Subject() { SubjectId = 2, SubjectName = 'C++'},
   new Subject() { SubjectId = 3, SubjectName = 'C'},
   new Subject() { SubjectId = 4, SubjectName = 'Python'},
   new Subject() { SubjectId = 5, SubjectName = 'Java'}
  };
  List<Student> students = new List<Student>(){
  new Student() { StudentId = 1, Name = 'Ashu', Marks = 500,SubjectId=2 },
  new Student() { StudentId = 2, Name = 'Shyam', Marks = 500,SubjectId=3},
  new Student() { StudentId = 3, Name = 'Shriyam', Marks = 400 },
  new Student() { StudentId = 4, Name = 'Sunny', Marks = 550,SubjectId=4 },
  new Student() { StudentId = 5, Name = 'Ram', Marks = 600,SubjectId=3 },
  new Student() { StudentId = 6, Name = 'Krishna', Marks = 550,SubjectId=5 },
  new Student() { StudentId = 7, Name = 'Anupam', Marks = 550 }
  } ;
  // left outer join command
  var result = from subject in subjects
              join student in students
               on subject.SubjectId equals student.SubjectId into stusub
  select new
  {
  SubjectName = subject.SubjectName,
  Student = from stu in stusub
   orderby stu.Name
   select stu
  };
  Console.WriteLine('Group Join\n');
  foreach(var item in result)
  {
   Console.WriteLine(item.SubjectName + ' Subject \n');
   if(item.Student.ToList().Count() == 0)
    Console.WriteLine('\tNo availible student\n');
   else
   {
  item.Student.ToList().ForEach( stu => Console.WriteLine('\t' + stu.StudentId + ' | ' + stu.Name ));
    Console.WriteLine();
   }
  }

  Console.ReadLine();
 }
}
class Student
{
public int StudentId { get; set; }
public string Name { get; set; }
public int Marks { get; set; }
public int SubjectId { get; set; }
}
class Subject{
 public int SubjectId { get; set; }
 public string SubjectName { get; set; }
}

Output

Group Join

C# Subject
    No availible student
C++ Subject
    1 | Ashu
C Subject
    5 | Ram
    2 | Shyam
Python Subject
    4 | Sunny
Java Subject
    6 | Krishna


Answer