Different type of join in LINQ C#.
The following are four most common join types:
1) Inner Join
2) Cross Join (Cross join is not equijoin)
3) Left Outer Join
4) Group Join
So i will explain you one by one , first of all we will start with Inner join.
Inner Join : A join clause takes two sequences as input or many . The elements in each sequence must either be or contain a property that can be compared to a corresponding property in the other sequence. All joins performed by the join clause are equijoins.
please see below exp.
I have two type of collection class , CourseDetail and Course.
private class Course
{
public string CourseId { get; set; }
public string CourseName { get; set; }
}
private class CourseDetail
{
public string CourseId { get; set; }
public string CourseDescription { get; set; }
public long CourseSer { get; set; }
}
The following are four most common join types:
2) Cross Join (Cross join is not equijoin)
3) Left Outer Join
4) Group Join
So i will explain you one by one , first of all we will start with Inner join.
Inner Join : A join clause takes two sequences as input or many . The elements in each sequence must either be or contain a property that can be compared to a corresponding property in the other sequence. All joins performed by the join clause are equijoins.
please see below exp.
I have two type of collection class , CourseDetail and Course.
private class Course
{
public string CourseId { get; set; }
public string CourseName { get; set; }
}
private class CourseDetail
{
public string CourseId { get; set; }
public string CourseDescription { get; set; }
public long CourseSer { get; set; }
}
Now we are going to bind some of data into list .
var listCourse = new List<Course>()
{
new Course {CourseName = "Test", CourseId = "1"},
new Course {CourseName = "Test2", CourseId = "2"},
new Course {CourseName = "Test3", CourseId = "2"},
new Course {CourseName = "Test4", CourseId = "5"}
};
var listCourseDetail = new List<CourseDetail>()
{
new CourseDetail {CourseDescription = "Test", CourseId = "1", CourseSer = 1234},
new CourseDetail {CourseDescription = "Test2", CourseId = "2", CourseSer = 12345},
new CourseDetail {CourseDescription = "Test3", CourseId = "3", CourseSer = 12346},
new CourseDetail {CourseDescription = "Test4", CourseId = "4", CourseSer = 12347},
};
Now will Perform inner join using LINQ as below
var innerJoinResult = (from cd in listCourseDetail
join cour in listCourse on cd.CourseId equals cour.CourseId // here i am comparing CourseDetail list courseid to course list CourseId , if it is match we will get result .
select new
{
cour.CourseName,
cour.CourseId,
cd.CourseDescription
}).ToList();
foreach (var item in innerJoinResult)
{
Console.WriteLine($"Course Name {item.CourseName} and its description {item.CourseDescription}");
}
below are output.
Cross Join: Cross join consists to perform a Cartesian product of two sets or sequences.
Cross join is not equijoin, means that no predicate expression of equality in the Join clause of the query.
Below is sample exp.
var innerJoinResult = (from cd in listCourseDetail
from cour in listCourse // here as you see i did not put any join keyword as its not a equijoin.
select new
{
cour.CourseName,
cour.CourseId,
cd.CourseDescription
}).ToList();
foreach (var item in innerJoinResult)
{
Console.WriteLine($"Course Name {item.CourseName} and its description {item.CourseDescription}");
}
below are output.
Left Outer Join: In a left outer join, all the elements in the left source sequence are returned, even if no matching elements are in the right sequence. To perform a left outer join in LINQ, use the DefaultIfEmpty method in combination with a group join to specify a default right-side element to produce if a left-side element has no matches. You can use null as the default value for any reference type, or you can specify a user-defined default type. In the following example, a user-defined default type is shown:
var leftJoinResult = (from cd in listCourseDetail
join cour in listCourse on cd.CourseId equals cour.CourseId into coursegroup // before using DefaultIfEmpty , we have to use group join , if you put your query result into that we called group join.
from item in coursegroup.DefaultIfEmpty(new Course {CourseId = "0", CourseName = "None"}) // here i have provide some default value like CourseName ="None", if above condition did not match up then we will get Courser Name as "None".
select new
{
item.CourseName,
item.CourseId,
cd.CourseDescription
});
foreach (var item in leftJoinResult)
{
Console.WriteLine($" Course Name {item.CourseName} and its description {item.CourseDescription}");
}
Group Join: A join clause with an into expression is called a group join.
A group join produces a hierarchical result sequence, which associates elements in the left source sequence with one or more matching elements in the right side source sequence. Group Join sequence of object arrays.
If no elements from the right source sequence are found to match an element in the left source, the join clause will produce an empty array for that item. Therefore, the group join is still basically an inner-equijoin except that the result sequence is organized into groups.
Using lambda expressions:
var groupJoin = listCourse.GroupJoin(listCourseDetail, //inner sequence
courseObj => courseObj.CourseId, //outerKeySelector
courseDetailObj => courseDetailObj.CourseId, //innerKeySelector
(s, courseDetailGroup) => new // resultSelector
{
s.CourseName,
GroupArray = courseDetailGroup
}) ;
Or Use Linq Query :
var groupJoin =
from courseObj in listCourse
join courseDetailObj in listCourseDetail on courseObj.CourseId equals courseDetailObj.CourseId into courseDetailGroup
select new { CategoryName = courseObj.CourseName, courseDetail = courseDetailGroup };
foreach (var item1 in groupJoin)
{
Console.WriteLine(item1.CourseName);
foreach (var item in item1.GroupArray)
Console.WriteLine( $"Group data is {item.CourseDescription} for courseId {item.CourseId}");
Console.Write("\n");
}
Below are out Put.
No comments:
Post a Comment