How to use SQL Join in LINQ?

Question

Ravi Vishwakarma · Answer

Join in LINQ 
and 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 -and 
using System;
using System.Collections.Generic;
using System.Linq;
public class Program
{
and public static void Main()
and {
and Listandlt;Subject> subjects = new Listandlt;Subject>(){
and and new Subject() { SubjectId = 1, SubjectName = 'C#'},
and and new Subject() { SubjectId = 2, SubjectName = 'Cand#43;and#43;'},
and and new Subject() { SubjectId = 3, SubjectName = 'C'},
and and new Subject() { SubjectId = 4, SubjectName = 'Python'},
and and new Subject() { SubjectId = 5, SubjectName = 'Java'}
and };
and Listandlt;Student> students = new Listandlt;Student>(){
and new Student() { StudentId = 1, Name = 'Ashu', Marks = 500,SubjectId=2 },
and new Student() { StudentId = 2, Name = 'Shyam', Marks = 500,SubjectId=3},
and new Student() { StudentId = 3, Name = 'Shriyam', Marks = 400,SubjectId=2 },
and new Student() { StudentId = 4, Name = 'Sunny', Marks = 550,SubjectId=4 },
and new Student() { StudentId = 5, Name = 'Ram', Marks = 600,SubjectId=3 },
and new Student() { StudentId = 6, Name = 'Krishna', Marks = 550,SubjectId=2 },
and new Student() { StudentId = 7, Name = 'Anupam', Marks = 550,SubjectId=5 }
and } ;
and // inner join command 
and var result = from student in students
and join subject in subjects
and on student.SubjectId equals subject.SubjectId
and select new
and {
and StudentName = student.Name,
and SubjectName = subject.SubjectName,
and ID = student.StudentId
and };
and Console.WriteLine('Inner Join
');
and Console.WriteLine('{0,3} {1,-15} {2,-10}','ID', 'StudentName', 'Subject');
and foreach(var item in result)
and and Console.WriteLine('{0,3} {1,-15} {2,-10}',item.ID, item.StudentName,item.SubjectName);
and //
and Console.ReadLine();
and }
}
class Student
{
public int StudentId { get; set; }
public string Name { get; set; }
public int Marks { get; set; }
public int SubjectId { get; set; }
}
class Subject{
and public int SubjectId { get; set; }
and public string SubjectName { get; set; }
}	
Output 
Inner Join
and ID StudentName Subject 
and 1 Ashu Cand#43;and#43; 
and 2 Shyam C 
and 3 Shriyam Cand#43;and#43; 
and 4 Sunny Python 
and 5 Ram C 
and 6 Krishna Cand#43;and#43; 
and 7 Anupam Java 
Left Outer Example -and 
using System;
using System.Collections.Generic;
using System.Linq;
public class Program
{
and public static void Main()
and {
and Listandlt;Subject> subjects = new Listandlt;Subject>(){
and and new Subject() { SubjectId = 1, SubjectName = 'C#'},
and and new Subject() { SubjectId = 2, SubjectName = 'Cand#43;and#43;'},
and and new Subject() { SubjectId = 3, SubjectName = 'C'},
and and new Subject() { SubjectId = 4, SubjectName = 'Python'},
and and new Subject() { SubjectId = 5, SubjectName = 'Java'}
and };
and Listandlt;Student> students = new Listandlt;Student>(){
and new Student() { StudentId = 1, Name = 'Ashu', Marks = 500,SubjectId=2 },
and new Student() { StudentId = 2, Name = 'Shyam', Marks = 500,SubjectId=3},
and new Student() { StudentId = 3, Name = 'Shriyam', Marks = 400 },
and new Student() { StudentId = 4, Name = 'Sunny', Marks = 550,SubjectId=4 },
and new Student() { StudentId = 5, Name = 'Ram', Marks = 600,SubjectId=3 },
and new Student() { StudentId = 6, Name = 'Krishna', Marks = 550,SubjectId=5 },
and new Student() { StudentId = 7, Name = 'Anupam', Marks = 550 }
and } ;
and // left outer join command 
and var result = from student in students
and join subject in subjects
and on student.SubjectId equals subject.SubjectId into stusub
and from ss in stusub.DefaultIfEmpty()
and select new
and {
and StudentName = student.Name,
and SubjectName = ss == null ? 'No Subject Choose' : ss.SubjectName,
and ID = student.StudentId
and };
and Console.WriteLine('Left outer Join
');
and Console.WriteLine('{0,3} {1,-15} {2,-10}','ID', 'StudentName', 'Subject');
and foreach(var item in result)
and and Console.WriteLine('{0,3} {1,-15} {2,-10}',item.ID, item.StudentName,item.SubjectName);
and //
and Console.ReadLine();
and }
}
class Student
{
public int StudentId { get; set; }
public string Name { get; set; }
public int Marks { get; set; }
public int SubjectId { get; set; }
}
class Subject{
and public int SubjectId { get; set; }
and public string SubjectName { get; set; }
}
Output
Left outer Join
and ID StudentName Subject 
and 1 Ashu Cand#43;and#43; 
and 2 Shyam C 
and 3 Shriyam No Subject Choose
and 4 Sunny Python 
and 5 Ram C 
and 6 Krishna Java 
and 7 Anupam No Subject Choose
Cross Join LINQ
using System;
using System.Collections.Generic;
using System.Linq;
public class Program
{
and public static void Main()
and {
and Listandlt;Subject> subjects = new Listandlt;Subject>(){
and and new Subject() { SubjectId = 1, SubjectName = 'C#'},
and and new Subject() { SubjectId = 2, SubjectName = 'Cand#43;and#43;'},
and and new Subject() { SubjectId = 3, SubjectName = 'C'},
and and new Subject() { SubjectId = 4, SubjectName = 'Python'},
and and new Subject() { SubjectId = 5, SubjectName = 'Java'}
and };
and Listandlt;Student> students = new Listandlt;Student>(){
and new Student() { StudentId = 1, Name = 'Ashu', Marks = 500,SubjectId=2 },
and new Student() { StudentId = 2, Name = 'Shyam', Marks = 500,SubjectId=3},
and new Student() { StudentId = 3, Name = 'Shriyam', Marks = 400 },
and new Student() { StudentId = 4, Name = 'Sunny', Marks = 550,SubjectId=4 },
and new Student() { StudentId = 5, Name = 'Ram', Marks = 600,SubjectId=3 },
and new Student() { StudentId = 6, Name = 'Krishna', Marks = 550,SubjectId=5 },
and new Student() { StudentId = 7, Name = 'Anupam', Marks = 550 }
and } ;
and // left outer join command 
and var result = from student in students
and and and from subject in subjects
and select new
and {
and StudentName = student.Name,
and SubjectName = subject.SubjectName, 
and ID = student.StudentId
and };
and Console.WriteLine('Cross Join
');
and Console.WriteLine('{0,3} {1,-15} {2,-10}','ID', 'StudentName', 'Subject');
and foreach(var item in result)
and and Console.WriteLine('{0,3} {1,-15} {2,-10}',item.ID, item.StudentName,item.SubjectName);
and //
and Console.ReadLine();
and }
}
class Student
{
public int StudentId { get; set; }
public string Name { get; set; }
public int Marks { get; set; }
public int SubjectId { get; set; }
}
class Subject{
and public int SubjectId { get; set; }
and public string SubjectName { get; set; }
}
Example
Cross Join
and ID StudentName Subject 
and 1 Ashu C# 
and 1 Ashu Cand#43;and#43; 
and 1 Ashu C 
and 1 Ashu Python 
and 1 Ashu Java 
and 2 Shyam C# 
and 2 Shyam Cand#43;and#43; 
and 2 Shyam C 
and 2 Shyam Python 
and 2 Shyam Java 
and 3 Shriyam C# 
and 3 Shriyam Cand#43;and#43; 
and 3 Shriyam C 
and 3 Shriyam Python 
and 3 Shriyam Java 
and 4 Sunny C# 
and 4 Sunny Cand#43;and#43; 
and 4 Sunny C 
and 4 Sunny Python 
and 4 Sunny Java 
and 5 Ram C# 
and 5 Ram Cand#43;and#43; 
and 5 Ram C 
and 5 Ram Python 
and 5 Ram Java 
and 6 Krishna C# 
and 6 Krishna Cand#43;and#43; 
and 6 Krishna C 
and 6 Krishna Python 
and 6 Krishna Java 
and 7 Anupam C# 
and 7 Anupam Cand#43;and#43; 
and 7 Anupam C 
and 7 Anupam Python 
and 7 Anupam Javaand and and 
Cross Join LINQ
using System;using System.Collections.Generic;using System.Linq;
public class Program{and public static void Main()and {and Listandlt;Subject> subjects = new Listandlt;Subject>(){and and new Subject() { SubjectId = 1, SubjectName = 'C#'},and and new Subject() { SubjectId = 2, SubjectName = 'Cand#43;and#43;'},and and new Subject() { SubjectId = 3, SubjectName = 'C'},and and new Subject() { SubjectId = 4, SubjectName = 'Python'},and and new Subject() { SubjectId = 5, SubjectName = 'Java'}and };
and Listandlt;Student> students = new Listandlt;Student>(){and new Student() { StudentId = 1, Name = 'Ashu', Marks = 500,SubjectId=2 },and new Student() { StudentId = 2, Name = 'Shyam', Marks = 500,SubjectId=3},and new Student() { StudentId = 3, Name = 'Shriyam', Marks = 400 },and new Student() { StudentId = 4, Name = 'Sunny', Marks = 550,SubjectId=4 },and new Student() { StudentId = 5, Name = 'Ram', Marks = 600,SubjectId=3 },and new Student() { StudentId = 6, Name = 'Krishna', Marks = 550,SubjectId=5 },and new Student() { StudentId = 7, Name = 'Anupam', Marks = 550 }and } ;
and // left outer join command 
and var result = from subject in subjectsand and and and and and and and and and and join student in studentsand and and and and and and and and and and and and and on subject.SubjectId equals student.SubjectId into stusuband select newand {and SubjectName = subject.SubjectName, and Student = from stu in stusuband and orderby stu.Nameand and select stuand };
and Console.WriteLine('Group Join
');and foreach(var item in result)and {and and Console.WriteLine(item.SubjectName and#43; ' Subject 
');and and if(item.Student.ToList().Count() == 0)and and Console.WriteLine('	No availible student
');and and elseand and {and item.Student.ToList().ForEach( stu => Console.WriteLine('	' and#43; stu.StudentId and#43; ' | ' and#43; stu.Name ));and and Console.WriteLine();and and }and }and Console.ReadLine();and }}
class Student{public int StudentId { get; set; }public string Name { get; set; }public int Marks { get; set; }public int SubjectId { get; set; }
}
class Subject{and public int SubjectId { get; set; }and public string SubjectName { get; set; }}
Output
Group Join
C# Subject 
and and No availible student
Cand#43;and#43; Subject 
and and 1 | Ashu
C Subject 
and and 5 | Ramand and 2 | Shyam
Python Subject 
and and 4 | Sunny
Java Subject 
and and 6 | Krishna

forum

How to use SQL Join in LINQ?

Ethan Karla

1 Answers

Liked By