public async Task <SingleDepartmentModel> GetSingleDepartment(int id)
{
//SELECT d.Id, d.DepartmentName, (FirstName + ' ' + LastName) AS Employeename, l.Id AS LaptopId, BrandName
//FROM Department d
//JOIN Employee e ON e.DepartmentId = d.Id
//JOIN Laptop l ON l.EmployeeId = e.Id
//WHERE d.Id = 1
using (var context = new EntityFrameworkDbEntities())
{
var department = await context.Department
.Where(d => d.Id == id)
.Include(d => d.Employee)
.Select(d => new SingleDepartmentModel()
{
DepartmentId = d.Id,
DepartmentName = d.DepartmentName,
Employees = d.Employee.Where(e => e.DepartmentId == d.Id).Select(e => new EmployeeForDepartment()
{
Id = e.Id,
FirstName = e.FirstName,
LastName = e.LastName,
LaptopForDepartment = e.Laptop.Where(l => l.EmployeeId == e.Id).Select(l => new LaptopForDepartment()
{
Id = l.Id,
BrandName = l.BrandName
}).FirstOrDefault()
}).ToList()
}).ToListAsync();
return(department[0]);
}
}
public async Task <SingleEmployeeModel> GetSingleEmployee(int id)
{
//SELECT e.FirstName, e.LastName, e.Email, e.DepartmentId, d.DepartmentName, l.BrandName as LaptopBrand
//FROM Employee e
//JOIN Department d ON e.DepartmentId = d.Id
//JOIN Laptop l ON l.EmployeeId = e.Id
//WHERE e.Id = 1
using (var context = new EntityFrameworkDbEntities())
{
var employee = await context.Employee
.Where(e => e.Id == id)
.Include(e => e.Department)
.Include(e => e.Laptop)
.Select(e => new SingleEmployeeModel()
{
EmployeeId = e.Id,
Name = e.FirstName + " " + e.LastName,
Email = e.Email,
DepartmentId = e.DepartmentId,
DepartmentName = e.Department.DepartmentName,
LaptopId = e.Laptop.FirstOrDefault(l => l.EmployeeId == e.Id).Id,
LaptopBrand = e.Laptop.FirstOrDefault(l => l.EmployeeId == e.Id).BrandName
}).ToListAsync();
return(employee[0]);
}
}
public async Task <SingleLaptopDetail> GetSingleLaptop(int id)
{
//SELECT l.Id, l.BrandName, e.Id AS EmployeeId, (FirstName + ' ' + LastName) AS Employeename, d.Id AS DepartmentId, d.DepartmentName
//FROM Laptop l
//JOIN Employee e ON e.Id = l.EmployeeId
//JOIN Department d ON d.Id = e.Id
//WHERE l.Id = 102
using (var context = new EntityFrameworkDbEntities())
{
var laptop = context.Laptop
.Where(l => l.Id == id)
.Select(l => new SingleLaptopDetail()
{
LaptopId = l.Id,
BrandName = l.BrandName,
EmployeeId = l.EmployeeId,
EmployeeName = l.Employee.FirstName + " " + l.Employee.LastName,
DepartmentId = l.Employee.DepartmentId,
DepartmentName = l.Employee.Department.DepartmentName
}).FirstOrDefault();
return(laptop);
}
}
public async Task <List <DepartmentListModel> > GetDepartmentList()
{
using (var context = new EntityFrameworkDbEntities())
{
var department = await context.Department
.Include(d => d.Employee)
.Select(d => new DepartmentListModel()
{
Id = d.Id,
DepartmentName = d.DepartmentName
})
.ToListAsync();
return(department);
}
}
public async Task <List <LaptopListModel> > GetLaptopList()
{
using (var context = new EntityFrameworkDbEntities())
{
var laptops = await context.Laptop
.Include(l => l.Employee)
.Select(d => new LaptopListModel()
{
Id = d.Id,
BrandName = d.BrandName,
EmployeeName = context.Employee.FirstOrDefault(e => e.Id == d.EmployeeId).FirstName + " " + context.Employee.FirstOrDefault(e => e.Id == d.EmployeeId).LastName
})
.ToListAsync();
return(laptops);
}
}
public async Task <List <EmployeeListModel> > GetEmployeesList()
{
using (var context = new EntityFrameworkDbEntities())
{
var employees = await context.Employee
.Include(e => e.Department)
.Include(e => e.Laptop)
.Include(e => e.EmployeeProject)
.Select(e => new EmployeeListModel()
{
Id = e.Id,
FirstName = e.FirstName,
LastName = e.LastName,
Email = e.LastName,
DepartmentId = e.DepartmentId,
ProjectCount = e.EmployeeProject.Count
})
.ToListAsync();
return(employees);
}
}
