public List <AttenanceViewModel> GetAttenanceReport(filter f) { using (var _dbcontext = new PayRollEntities()) { SqlConnection con = _dbcontext.Database.Connection as SqlConnection; var eMPLOYEEData = con.Query <EMPLOYEE>("[dbo].[SP_GetEmployeelist] @Mode,@UNITID,@DEPARTMENTID,@DESIGNATIONID,@WAGES_TYPEID", new { Mode = f.Mode, UNITID = f.UNIT, DEPARTMENTID = f.Department, DESIGNATIONID = f.DESIGNATION, WAGES_TYPEID = f.Wagetype }); var Empdetails = (from e in eMPLOYEEData join u in _dbcontext.UNITs on e.UNITID equals u.UnitId join dep in _dbcontext.Departments on e.DEPARTMENTID equals dep.DepId join des in _dbcontext.Designations on e.DESIGNATIONID equals des.DegId select new AttenanceViewModel() { EMPID = e.EMPID, Unit = u.Name, Department = dep.DEPT, Designation = des.DESG, EmpName = e.EMP_NAME, EmpCode = e.EMPCODE }).ToList(); var ProcessedData = (from pd in _dbcontext.ProcessedData where pd.Date == DbFunctions.TruncateTime(f.Date) select pd).ToList(); var Attdetails = (from ai in _dbcontext.ATTENDANCE_IMPORT where ai.AttendanceDate == DbFunctions.TruncateTime(f.Date) select ai).ToList(); var res = (from e in Empdetails join at in Attdetails on e.EmpCode equals at.EmployeeId into Attendance from ai in Attendance.DefaultIfEmpty() join pd in ProcessedData on e.EmpCode equals pd.EmpCode into Data from d in Data.DefaultIfEmpty() select new AttenanceViewModel() { EMPID = e.EMPID, Unit = e.Unit, Department = e.Department, Designation = e.Designation, EmpName = e.EmpName, EmpCode = e.EmpCode, InTime = ai != null ? ai.InTime : null, OutTime = ai != null ? ai.OutTime : null, PunchRecords = ai != null ? ai.PunchRecords : "", Status = ai != null ? "Present" : "Absent", Attenanceid = ai != null ? ai.Id : 0, ShiftCount = d != null ? d.ShiftCount : 0m }).ToList(); return(res); } }