Inner Join Using LINQ And Lambda

SQL Syntax

SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

Let's assume we have two tables, Students and Courses, and we want to join them based on the StudentID (from Students) and CourseID (from Courses) to retrieve student names along with the courses they are enrolled in.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace InnerJoinExample
{
    class Program
    {
        static void Main(string[] args)
        {
            using (UniversityEntities databaseEntities = new UniversityEntities())
            {
                #region InnerJoin with Linq
                // Deferred query execution
                var linqQuery = from student in databaseEntities.Students
                                join course in databaseEntities.Courses
                                on student.StudentID equals course.StudentID
                                select new
                                {
                                    student.StudentID,
                                    student.Name,
                                    course.CourseName
                                };

                Console.WriteLine("\n\tStudent Data with Inner Join Using LINQ Query");
                
                // Immediate query execution
                foreach (var studentData in linqQuery)
                {
                    Console.WriteLine($"Student ID: {studentData.StudentID}, Name: {studentData.Name}, Course: {studentData.CourseName}");
                }
                #endregion

                #region InnerJoin with Lambda
                // Deferred query execution
                var lambdaQuery = databaseEntities.Students.Join(databaseEntities.Courses, 
                                                                  s => s.StudentID, 
                                                                  c => c.StudentID, 
                                                                  (s, c) => new 
                                                                  {
                                                                      s.StudentID, 
                                                                      s.Name, 
                                                                      c.CourseName
                                                                  });

                Console.WriteLine("\n\tStudent Data with Inner Join Using LINQ Lambda");
                
                // Immediate query execution
                foreach (var studentData in lambdaQuery)
                {
                    Console.WriteLine($"Student ID: {studentData.StudentID}, Name: {studentData.Name}, Course: {studentData.CourseName}");
                }
                #endregion
            }
        }
    }
}

Output:

Student Data with Inner Join Using LINQ Query
Student ID: 1, Name: John Doe, Course: Mathematics
Student ID: 2, Name: Jane Smith, Course: Computer Science

Student Data with Inner Join Using LINQ Lambda
Student ID: 1, Name: John Doe, Course: Mathematics
Student ID: 2, Name: Jane Smith, Course: Computer Science

In this example:

• LINQ Query:
  We use the LINQ syntax to perform an inner join between the Students and Courses tables based on the StudentID and CourseID. The result selects the StudentID, Name, and CourseName.

• Lambda Query:
  Similarly, using Lambda syntax, we achieve the same result by joining the Students and Courses tables and selecting the necessary fields.