Joins Examples

The LINQ keyword Joins. This keyword gives you the ability to join data properties, database tables, etc.., that don’t have any kind of association. For example say you have tables with ID properties in two database tables without any kind of association from a database perspective. What the join keyword can do is join the two ID properties of the tables together to run queries on.

There are three types of Joins I’ll be showing here. Normal Joins, group joins, and cross joins. All of them will be using query syntax. I’ll be demonstrating method syntax in some later examples.

Again if you’re confused about the terminology of the different query types use this. Query = Comprehension Syntax, Lambda = Method Syntax.

JoinsSolution JoinsProgram

 

DepartmentRepo.cs & EmployeeRepo.cs Code Contents

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

namespace MyLinq
{
    public class DepartmentRepo
    {
        // Public Methods
        //
        // Method that returns the private list of departments.
        public IEnumerable<Department> GetAll()
        {
            return _departments;
        }

        //Private Methods
        //
        // A simple in memory collection of departments.
        List<Department> _departments = new List<Department>
        {
            // I'm going to need departments 1 - 5 with names.
            new Department(1, "Information Technology"),
            new Department(2, "Metrology"),
            new Department(3, "Validations"),
            new Department(4, "Human Resources"),
            new Department(5, "Skunkworks")
        };
    }
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MyLinq
{
    public class EmployeeRepo
    {
        // Public Methods
        //
        
        // Method that returns the private employee list collection.
        public IEnumerable<Employee> GetAll()
        {
            return _employees;
        }

        // Private Methods
        //

        // Creating a simple in memory list of employees.
        List<Employee> _employees = new List<Employee>
        {
            new Employee(1, "Daniel", 1),
            new Employee(2, "Sara", 3),
            new Employee(3, "John", 4),
            new Employee(4, "Debbie", 2)
        };
    }
}

You may recognize the EmployeeRepo class from some other posts in this blog. The DepartmentRepo is doing something similar. It’s taking a list of Department object, which have ID and Name properties, and storing them in a list. When the GetAll method is called it returns the list of departments. The end goal is to tie the department ID and employee department ID together in a LINQ query utilizing the Join operator.

 
Program.cs Code Contents

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

namespace MyLinq
{
    class Program
    {
        static void Main(string[] args)
        {
            useJoins();
            useGroupJoin();
            useCrossJoin();

            Console.ReadLine();
        }

        // Private Methods
        //
        private static void useJoins()
        {
            var employeeRepository = new EmployeeRepo();
            var departmentRepository = new DepartmentRepo();

            // Demonstrating a simulation query of just displaying the employee and department data with a simple expression query.
            var employeeQuery =
                from employees in employeeRepository.GetAll()
                orderby employees.ID descending
                select employees;

            // Printing the results of the employeeQuery. You will notice that the department is just a number. Not really user friendly.
            Console.WriteLine("****Showing The Results Of The Normal Query****");
            foreach (var item in employeeQuery)
            {
                Console.WriteLine("Name: {0} - Department: {1}", item.Name, item.ID);   
            }
            Console.WriteLine();

            // Demonstrating a simulation of database data on two different data tables, in this case data types,
            // that have no relationship associations with an expression query.
            var joinQuery =
                from employees in employeeRepository.GetAll()
                join departments in departmentRepository.GetAll() on                        // Using the join and on keywords.
                employees.DepartmentID equals departments.ID
                select new { Employee = employees.Name, Department = departments.Name };    // Selecting the new employee and associated department name.

            // Printing out the results of the query.
            Console.WriteLine("****Showing The Results Of The Join Query****");
            foreach (var item in joinQuery)
            {
                Console.WriteLine("Name: {0} - Department: {1}", item.Employee, item.Department);
            }
            Console.WriteLine();
            
        }

        private static void useGroupJoin()
        {
            var employeeRepository = new EmployeeRepo();
            var departmentRepository = new DepartmentRepo();

            // If you noticed in the useJoins method it only displayed the department name if there was an employee associated.
            // You can use a left join to fix this.
            var query =
                from departments in departmentRepository.GetAll()
                join employees in employeeRepository.GetAll() on
                departments.ID equals employees.DepartmentID
                into employeeDepartment
                select new { Department = departments.Name, Employee = employeeDepartment };

            // Displaying all the departments with employees. Even if no employees are associated with the dept.
            Console.WriteLine("****Showing The Results Of The Group Join Query****");
            foreach (var group in query)
            {
                Console.WriteLine(group.Department);
                foreach (var employee in group.Employee)
                {
                    Console.WriteLine("\t" + employee.Name);
                }
            }
            Console.WriteLine();
        }

        private static void useCrossJoin()
        {
            var employeeRepository = new EmployeeRepo();
            var departmentRepository = new DepartmentRepo();

            // If I wanted to list everything in all the departments and all the employees I'd drop the join and into
            // keywords from the query.
            var query =
                from employee in employeeRepository.GetAll()
                from department in departmentRepository.GetAll()
                select new { employeeName = employee.Name, departmentName = department.Name };

            // By doing this you will notice that everything is being listed. Regardless of the department and employee.
            // Not very useful but this shows you how a query can be made.
            foreach (var employee in query)
            {
                Console.WriteLine("Employee Name: {0}", employee.employeeName);
                Console.WriteLine("Department Name: {0}", employee.departmentName);
            }
        }

    }
}

I’ve broken the different keyword up into three methods, useJoins, useGroupJoin, and useCrossJoin.

In the useJoins method the actual join operation is taking place at line 43. I’m calling on the GetAll methods from the employees and departments collection. I’m joining the employees DepartmentID property to the departments ID property. Then I’m creating an anonymous type with the employee name and department name data extracted from the query.

One thing a normal join won’t do is it won’t pull and information if the query result is null. You can fix this by using a GroupJoin. To perform a group join with a comprehension query you use the into keyword. You can see this in the useGroupJoin method. After the variable declaration after the into keyword I’m creating an anonymous type for the query data properties.

A cross join is basically not using the into, select, or join keywords. In the useCrossJoins method you can see this. I’m calling on both GetAll methods from the employee and department repositories and again using an anonymous type to declare the properties from the query data. What this is going to do is just blast all the data regardless if its null or not. Sort of like a shotgun blast approach.

Between all of these I use the join operator mostly. I find myself wanting to join tables in different databases that don’t any kind of relationship when the database was designed.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s