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}"); } }