LINQ 06 Join

If we have two related collections we can use linq to join them on a common field much like we do with SQL. Let's start with two classes Person and Department.

class Person

{

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public int DepartmentId { get; set; }

    public Person(string FirstName, string LastName, int DepartmentId) {

        this.FirstName = FirstName;

        this.LastName = LastName;

        this.DepartmentId = DepartmentId;

    }

}

class Department {

    public int Id { get; set; }

    public string Name { get; set; }

    public Department(int Id, string Name) {

        this.Id = Id;

        this.Name = Name;

    }

}

it doesn't take a genius to realize that Person's DepartmentId property maps to Department's Id property.

class Program

{

    static void Main(string[] args)

    {

        var ppl = new Person[] { new Person("Pawel", "Chooch",1),

            new Person("Magda", "Tyvoniuk",2), new Person("Tomek","Chooch",3),

            new Person("Marin","Smartzic",2), new Person("Jake","Tyvoniuk",1)};

           

        var depts = new Department[] { new Department(1, "IT"),

        new Department(2,"Finance"), new Department(3,"Operatios")};

        var result1 = from p in ppl

                      join d in depts on p.DepartmentId equals d.Id

                      select new {

                            FullName = $"{p.FirstName} {p.LastName}",

                            Department = d.Name };

        var result2 = ppl.Join(depts, p => p.DepartmentId, d => d.Id,

            (p, d) => new {

                FullName = $"{p.FirstName} {p.LastName}",

                Department = d.Name });

        Array.ForEach(result1.ToArray(), pd =>

            Console.WriteLine(pd.FullName + " " + pd.Department));

        Console.WriteLine();

        Array.ForEach(result2.ToArray(), pd =>

            Console.WriteLine(pd.FullName + "  " + pd.Department));

        Console.WriteLine();

    }

}

now one major caveat when it comes to the expression syntax is that we use the "equals" keyword to map the two properties as the fields we join our collections on.

and as for the method based syntax we just have to remember the order of parameters for the join function

1. the collection we're joining, 
2. source collections property to join on, 
3. joining collections property to join on, 
4. projection from the collections