How to use SQL Join in LINQ?

Ethan Karla · 2021-09-24T02:36:20.870Z

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

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

Inner Join - it returns the elements from the collection which satisfy the condition
Left Outer Join - it return the all element from the left side collection and matched collection from right side collection
Cross Join - it returns some product of elements from the collection
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

forum

How to use SQL Join in LINQ?

Can you answer this question?

1 Answers