public static void Main(string[] args)
{
// Linq's Join is equivalent to SQL's Inner Join
// Linq's GroupJoin is equivalent to SQL's Outer Join
// Left Outer Join and Right Outer Join can be performed by exchanging data source
// Cross Join doesn't require common property. It generates cartesian products of the collection
var joinQuerySyntax = from dept in Department.GetAllDepartments()
join std in Student.GetAllStudents()
on dept.ID equals std.DepartmentId
select new { Name = std.Name, Department = dept.Name };
//foreach(var std in joinQuerySyntax)
//{
// Console.WriteLine(std.Name + " " + std.Department);
//}
var groupJoinQuerySyntax = from dept in Department.GetAllDepartments()
join std in Student.GetAllStudents()
on dept.ID equals std.DepartmentId
into StudentGroups // List of children. Here it is list of Student
select new { Department = dept.Name, StudentGroups = StudentGroups };
// from p in Parent
// join c in Child
// on p.Id equals c.Id into GROUPJOIN
// select new { NewParent = Parent, NewChildren = GROUPJOIN};
// NewChildren is a list of Children. It can be empty if there is no children
//Outer Foreach is for all department
//foreach (var item in groupJoinQuerySyntax)
//{
// Console.WriteLine("Department :" + item.Department);
// //Inner Foreach loop for each student of a department
// foreach (var student in item.StudentGroups)
// {
// Console.WriteLine(" StudentID : " + student.Id + " , Name : " + student.Name);
// }
//}
var flattenedQuerySyntax = from dept in Department.GetAllDepartments()
join std in Student.GetAllStudents()
on dept.ID equals std.DepartmentId
into StudentGroups
from student in StudentGroups.DefaultIfEmpty()
select new { Department = dept, StudentName = student?.Name };
//foreach(var item in flattenedQuerySyntax)
//{
//Console.WriteLine("Department - " + item.Department.Name + " . Student - " + item.StudentName);
//}
var crossJoin = from std in Student.GetAllStudents()
from dept in Department.GetAllDepartments()
select new
{
Name = std.Name,
Depatment = dept.Name
};
Console.WriteLine(crossJoin.LongCount()); // Number of Student * Number of Department
}
static void Main(string[] args)
{
List <Student> students = new List <Student>()
{
new Student()
{
Id = 0, Name = "Zakir", GroupId = 1
},
new Student()
{
Id = 1, Name = "Zahid", GroupId = 2
},
new Student()
{
Id = 2, Name = "Limon", GroupId = 3
},
new Student()
{
Id = 3, Name = "Pranto", GroupId = 4
},
new Student()
{
Id = 4, Name = "Rakib", GroupId = 5
},
new Student()
{
Id = 5, Name = "Shuvo", GroupId = 6
},
new Student()
{
Id = 6, Name = "Shovon", GroupId = 7
}
};
List <Category> categories = new List <Category>()
{
new Category()
{
Id = 0, Group = "Group A", StudentId = 1
},
new Category()
{
Id = 1, Group = "Group B", StudentId = 2
},
new Category()
{
Id = 2, Group = "Group C", StudentId = 3
},
new Category()
{
Id = 3, Group = "Group D", StudentId = 4
}
};
var QueryLeftJoin = (from std in students
join cat in categories
on std.GroupId equals cat.StudentId into StudentGroups
from stdgroup in StudentGroups.DefaultIfEmpty()
select new { StudentName = std.Name, StudentGroup = stdgroup != null ? stdgroup.Group : "NA" }).ToList();
foreach (var item in QueryLeftJoin)
{
Console.WriteLine($"Student Name={item.StudentName} Group={item.StudentGroup}");
}
}