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

namespace DatabaseFirst
{
    /// <summary>
    /// This application demonstrate Database First feature
    /// Before running this application ensure followings are done
    /// 1. Connection string is updated with correct connection details in app.config
    /// 2. Execute the DDL and DMLs (in corresponding database) present in DatabaseCommands folder in following order
    ///     - DepartmentDDL
    ///     - EmployeeDDL
    ///     - DepartmentRecords
    ///     - EmployeeRecords
    /// </summary>
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                using (var db = new PSQLEntities())
                {
                    // Perform a join operation between Employees table and Departments table to fetch data from database
                    // Ordering employee records by their department first and then by employee name
                    var Employees = db.Employees.Join(db.Departments,
                                                      e => e.DeptNo,
                                                      d => d.DepartmentNo,
                                                      (e, d) => new
                                                      {
                                                          Name = e.EmpName,
                                                          Id = e.EmpNo,
                                                          Designation = e.Designation,
                                                          Salary = e.Salary,
                                                          Department = d.DepartmentName,
                                                          DepartmentLocation = d.Location
                                                      })
                                                 .OrderBy(e => e.Department)
                                                 .ThenBy(e => e.Name);

                    Console.WriteLine("Name,Id,Designation,Department,Location");
                    Console.WriteLine("---------------------------------------");
                    // Show all employee records
                    foreach (var Emp in Employees)
                    {
                        Console.WriteLine(Emp.Name + "," + Emp.Id + "," + Emp.Designation + "," + Emp.Department + "," + Emp.DepartmentLocation);
                    }
                    Console.WriteLine("=======================================");

                }
            }
            catch (Exception ex)
            {
                ProcessExceptionMessage(ex);
            }
            finally
            {
                Console.Write("Press any Key to continue:");
                Console.ReadKey(true);
            }
        }

        private static void ProcessExceptionMessage(Exception ex)
        {
            while (ex != null && !String.IsNullOrWhiteSpace(ex.Message))
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine("-->");
                ex = ex.InnerException;
            }

        }
    }
}
