Homework-3

Homework-LinQ-Entities.cs

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

namespace linq_ORM_entities
{
    public static class CustomExtentions
    {
        public static void ToConsole<T>(this IEnumerable<T> s)
        {
            foreach (var item in s)
            {
                Console.WriteLine(item);
            }
            Console.ReadLine();
        }
    }
    class Program
    {
        static void Main(string[] args)
        {
            EmpDeptEntities ED = new EmpDeptEntities();
            //Query 1 - All Employee

            var Q1 = ED.EMP.Select(x => new { x.ENAME, x.DEPT.DNAME });
            Q1.ToConsole();

            //Determine the average income for every department names (income = salary + commission)!

            var Q2 = from d in ED.DEPT
                     join w in ED.EMP
                     on d.DEPTNO equals w.DEPTNO into dw
                     from e in dw.DefaultIfEmpty()
                     group e by e.DEPT.DNAME into g
                     select new
                     {
                         g.Key,
                         AVG = g.Average(x => x.SAL + (x.COMM??0)) ?? 0
                     };
            Q2.ToConsole();

            // Determine the biggest department!

            var Q3 = Q2.OrderByDescending(x => x.AVG).FirstOrDefault();
            Console.WriteLine($"Biggest : {Q3}");
            Console.WriteLine();

            /*Increase the salary for the people in the job with the smallest sum salary. 
             * The job with the smallest sum should have the same sum as the job with the second to smallest sum; 
             * distribute the extra salary evenly amongst the workers!	*/

            var Q4 = from w in ED.EMP
                     group w by w.JOB into g
                     select new
                     {
                         g.Key,
                         Sum = g.Sum(x => x.SAL + (x.COMM ?? 0))
                     };

            Q4.ToConsole();

            var smallest = Q4.OrderBy(x => x.Sum).FirstOrDefault();
            var secondsmallest = Q4.OrderBy(x => x.Sum).Skip(1).FirstOrDefault();

            var salInc = secondsmallest.Sum - smallest.Sum;

            Console.WriteLine($"Sal to increase: {salInc}");

            var Q4NumOfPeopleWithSmallestSalJob = ED.EMP.Where(x => x.JOB == smallest.Key).Count();

            Console.WriteLine($"Need = {salInc/Q4NumOfPeopleWithSmallestSalJob}");

            var Q4PoorPeople = ED.EMP.Where(x => x.JOB == smallest.Key).Select(x=>x);

            foreach (var item in Q4PoorPeople)
            {
                item.SAL = item.SAL + (salInc / Q4NumOfPeopleWithSmallestSalJob);
            }
            ED.SaveChanges();
            Q4.ToConsole();

            // Delete the workers who were hired in the 30 days after the hire date of the president!

            Q1.ToConsole();
            var boss = ED.EMP.Single(x => x.ENAME == "KING");

            var Q5 = from worker in ED.EMP
                     where worker.HIREDATE > boss.HIREDATE &&
                     worker.HIREDATE < DbFunctions.AddDays(boss.HIREDATE, 30)
                     select worker;
            foreach (var item in Q5)
            {
                ED.EMP.Remove(ED.EMP.Single(x => x.EMPNO == item.EMPNO));
            }
            ED.SaveChanges();
            Q1.ToConsole();
            Console.ReadLine();
        }
    }
}