Skip to content

Instantly share code, notes, and snippets.

@PhiHuyHoang
Created September 25, 2018 21:25
Show Gist options
  • Save PhiHuyHoang/40251701590c68c305f34723c641dd22 to your computer and use it in GitHub Desktop.
Save PhiHuyHoang/40251701590c68c305f34723c641dd22 to your computer and use it in GitHub Desktop.
Homework-3
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();
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment