I have a sql query which i am trying to convert to LINQ code.
SQL Query
declare @EmpID INT = 0,
@RoleID INT = 3,
@UserID INT = 19,
@EmpIDs VARCHAR(MAX) = '0',
@SystemStatus VARCHAR(10) = '0',
@FilterType VARCHAR(100) = '',
--@Status VARCHAR(15) = '1,2,3,7',
@Status VARCHAR(15) = '4,5,8,7',
@DurationFrom DATETIME = NULL,
@DurationTo DATETIME = NULL,
@ProbationStatus VARCHAR(10) = '1';
Declare @CurrentStatusDesc varchar(10)='';
select @CurrentStatusDesc=Status from EmployeeCurrentStatus where Status_Desc='On Notice'
if exists(SELECT LinkLevel FROM EntityAccessRights02 AS s CROSS APPLY dbo.SplitStrings_XML(s.LinkId, default) AS f where roleid=@RoleID and s.LinkLevel=15)
begin
;with cteEmployeeDetails as (
select a.Emp_Id,a.Emp_Code,(case when a.Middle_Name is null and a.Last_Name is null then a.First_Name when a.Middle_Name is null then a.First_Name+' '+a.Last_Name when a.Last_Name is null then a.First_Name+' '+a.Middle_Name else a.First_Name+' '+a.Middle_Name+' '+a.Last_Name end) as Name,
isnull(a.Guardians_Name,'') as Guardians_Name,
FORMAT( a.DateOfBirth, 'dd/MM/yyyy') as DateOfBirth, FORMAT( a.Join_Dt, 'dd/MM/yyyy') as JoinDate,FORMAT( a.Join_Dt, 'yyyyMMdd') as DataDate,
case when a.Gender is null then 'NA' when a.Gender='M' then 'Male' when a.Gender='F' then 'Female' when a.Gender='O' then 'Other' else a.Gender end as Gender,
a.SeperationStatus,
[dbo].[GetEmployeeAge](a.DateOfBirth) as Age,FORMAT( a.Probation_Dt, 'dd/MM/yyyy') as Probation_Dt,Is_probation,LastEntity,
CurrentStatus,emp_status,isnull(EmpFileNumber,'') as EmpFileNumber,isnull(a.IsSave,0) as IsSave
from HR_EMP_MASTER a WITH (NOLOCK)
left join Resignation t on t.Emp_Id=a.Emp_Id and t.CurrentRequest=1 and t.ApprovalStatus<>'D' and t.ApprovalStatus<>'R' and (t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc then 'P' else 'A' end) or t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc then 'A' else 'A' end)) and t.Rejoin_Status='P'
where
(
(a.Join_Dt between @DurationFrom and @DurationTo and (select item from dbo.SplitNEW(@FilterType,','))=1)
or(a.Probation_Dt between @DurationFrom and @DurationTo and (select item from dbo.SplitNEW(@FilterType,','))=2)
or(a.RelievingDate between @DurationFrom and @DurationTo and (select item from dbo.SplitNEW(@FilterType,','))=3) or @FilterType is null or @FilterType='' or @FilterType=0
)
and (a.CurrentStatus = @SystemStatus or @SystemStatus='0' or (@SystemStatus=@CurrentStatusDesc and t.Resignation_Id is not null and t.RelievingDate>=GETUTCDATE()))
and (
a.emp_status in(select item from dbo.SplitNEW(@Status,',') where Item in(select StatusId from HR_EMP_STATUS_SETTINGS where isResignation!=1)) and exists(select 1 from dbo.SplitNEW(@Status,',') where item in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation!=1)) and ISNULL(a.SeperationStatus,0) not in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1 --and StatusID not in(select item from dbo.SplitNEW(@Status,','))
)
or
(a.emp_status in(select item from dbo.SplitNEW(@Status,',') where Item in(select StatusId from HR_EMP_STATUS_SETTINGS where isResignation!=1)) or (a.emp_status='1' or a.emp_status='2' or a.emp_status='3' or a.emp_status='7')) and exists(select 1 from dbo.SplitNEW(@Status,',') where item in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1)) and ISNULL(a.SeperationStatus,0) in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1-- and StatusID in(select item from dbo.SplitNEW(@Status,','))
)
)
and ((@ProbationStatus=2 and ISNULL(a.Is_Probation,0)=1)or(@ProbationStatus=3 and ISNULL(a.Is_Probation,0)=0) or (@ProbationStatus=1 and (ISNULL(a.Is_Probation,0)=1 or ISNULL(a.Is_Probation,0)=0)))
and isnull(IsDelete,0)=0
and (a.Emp_Id in(select item from dbo.SplitNEW(@EmpIDs,',')) or @EmpIDs='0')
)
select a.Emp_Id,i.image_url,a.Emp_Code,a.Name,Guardians_Name,a.DateOfBirth,
JoinDate,DataDate,j.Status_Desc,s.Status_Desc as EmpStatus,
isnull(a.Gender,'NA')as Gender,
FORMAT(t.Resignation_Date,'dd/MM/yyyy')as Resignation_Date,
FORMAT(t.RelievingDate,'dd/MM/yyyy')as RelievingDate,a.SeperationStatus,isnull(EMail,'NA') as OfficialEmail,
b.PersonalEMail,b.Phone,
case when Marital_Status='S' then 'Single'
when Marital_Status='M' then 'Married'
when Marital_Status='W' then 'Widowed'
when Marital_Status='X' then 'Separated'
when Marital_Status='D' then 'Divorcee'
ELSE 'NA' end as Marital_Status,Age,a.Probation_Dt,
f.LevelOneDescription,f.LevelTwoDescription,f.LevelThreeDescription,f.LevelFourDescription,f.LevelFiveDescription,f.LevelSixDescription,
f.LevelSevenDescription ,f.LevelEightDescription,f.LevelNineDescription ,f.LevelTenDescription,f.LevelElevenDescription,
rm.Description,(select Status_Desc from HR_EMP_STATUS_SETTINGS where StatusID=t.Relieving_Type) as ResignationType,
case when a.Is_probation=0 then 'CONFIRMED' when a.Is_probation=1 then 'PROBATION' end as ProbationStatus,
cm.Country_Name as Nationality,a.IsSave,a.EmpFileNumber,a.CurrentStatus,IsSave
from cteEmployeeDetails a
left join HR_EMP_ADDRESS b WITH (NOLOCK) on a.Emp_Id=b.Emp_Id
left join HR_EMP_PERSONAL c WITH (NOLOCK) on c.Emp_Id=a.Emp_Id
left join HR_EMP_REPORTING r WITH (NOLOCK) on r.emp_id=a.Emp_Id
left join HighLevelViewTable f WITH (NOLOCK) on a.LastEntity=f.LastEntityID
left join HR_EMP_IMAGES i WITH (NOLOCK) on i.emp_id=a.Emp_Id
inner join EmployeeCurrentStatus j WITH (NOLOCK) on a.CurrentStatus=j.Status
inner join HR_EMP_STATUS_SETTINGS s WITH (NOLOCK) on s.StatusID=a.emp_status
LEFT JOIN HR_EMP_STATUS_SETTINGS h ON h.StatusID = a.SeperationStatus
left join Resignation t WITH (NOLOCK) on t.Emp_Id=a.Emp_Id and t.CurrentRequest=1 and t.ApprovalStatus<>'D' and t.ApprovalStatus<>'R' and (t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc then 'P' else 'A' end) or t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc then 'A' else 'A' end)) and t.Rejoin_Status='P'
--left join HR_EMP_MASTER z on a.Emp_Id=z.Emp_Id
left join ReasonMaster rm WITH (NOLOCK) on t.Reason=rm.Reason_Id and rm.Type='Reason'
left join ADM_Country_Master cm WITH (NOLOCK) on cm.Country_ID=c.Nationality
order by a.Emp_Code asc
end
My LINQ code is shown below
private async Task<PaginatedResult<EmployeeResultDto>> GetEmployeeLinkLevelExistDataAsync(
DateTime? durationFrom, DateTime? durationTo, string empSystemStatus, string currentStatusDesc,
List<int> result, HashSet<int> excludedStatuses, int probationStatus, int pageNumber, int pageSize, int draw)
{
var empList = await GetFilteredEmployeesAsync(new HashSet<int> { 4, 5, 8, 9 });
var empStatusSet = empList
.Where(e => e.HasValue) // Filter null values
.Select(e => e.Value) // Convert to non-nullable int
.ToHashSet();
var finalQuery =
from emp in (
from emp in _context.HrEmpMasters
join res in _context.Resignations
.Where(r => r.CurrentRequest == 1 &&
!new[] { ApprovalStatus.Deleted.ToString(), ApprovalStatus.Rejceted.ToString() }
.Contains(r.ApprovalStatus) &&
r.ApprovalStatus == (empSystemStatus == currentStatusDesc ?
ApprovalStatus.Pending.ToString() : ApprovalStatus.Approved.ToString()) &&
r.RejoinStatus == ApprovalStatus.Pending.ToString())
on emp.EmpId equals res.EmpId into resGroup
from res in resGroup.DefaultIfEmpty()
where
(durationFrom == null || durationTo == null ||
emp.JoinDt >= durationFrom && emp.JoinDt <= durationTo ||
emp.ProbationDt >= durationFrom && emp.ProbationDt <= durationTo ||
emp.RelievingDate >= durationFrom && emp.RelievingDate <= durationTo)
&& (emp.CurrentStatus == Convert.ToInt32(empSystemStatus) ||
empSystemStatus.Equals(byte.MinValue.ToString()) ||
empSystemStatus == currentStatusDesc && res.ResignationId != null && res.RelievingDate >= DateTime.UtcNow)
&& emp.EmpStatus.HasValue // Ensure it's not null
&& empStatusSet.Contains(emp.EmpStatus.Value) // Use Value instead of GetValueOrDefault()
&& !excludedStatuses.Contains(emp.SeperationStatus.GetValueOrDefault())
&& (probationStatus == 2 && emp.IsProbation == true ||
probationStatus == 3 && emp.IsProbation == false ||
probationStatus == 1 && (emp.IsProbation == true || emp.IsProbation == false))
&& emp.IsDelete.Equals(false)
select new
{
EmpId = emp.EmpId,
EmpCode = emp.EmpCode,
Name = $"{emp.FirstName} {emp.MiddleName} {emp.LastName}",
GuardiansName = emp.GuardiansName,
DateOfBirth = FormatDate(emp.DateOfBirth, _employeeSettings.DateFormat),
JoinDate = FormatDate(emp.JoinDt, _employeeSettings.DateFormat),
DataDate = FormatDate(emp.JoinDt, _employeeSettings.DateFormat),
SeperationStatus = emp.SeperationStatus,
Gender = emp.Gender,
WorkingStatus = emp.SeperationStatus == (int)SeparationStatus.Live ? nameof(SeparationStatus.Live) : nameof(SeparationStatus.Resigned),
Age = CalculateAge(emp.DateOfBirth, "Years"),
ProbationDt = FormatDate(emp.ProbationDt, _employeeSettings.DateFormat),
Probation = emp.IsProbation == false ? ProbationStatus.CONFIRMED : ProbationStatus.PROBATION,
LastEntity = emp.LastEntity,
CurrentStatus = emp.CurrentStatus,
EmpStatus = emp.EmpStatus.ToString(),
IsSave = emp.IsSave,
EmpFileNumber = emp.EmpFileNumber,
DailyRateTypeId = emp.DailyRateTypeId,
PayrollMode = emp.PayrollMode,
ResignationReason = res.Reason,
ResignationDate = res.ResignationDate.ToString(),
RelievingDate = res.RelievingDate.ToString()
}
)
join addr in _context.HrEmpAddresses on emp.EmpId equals addr.EmpId into addrGroup
from addr in addrGroup.DefaultIfEmpty()
join pers in _context.HrEmpPersonals on emp.EmpId equals pers.EmpId into persGroup
from pers in persGroup.DefaultIfEmpty()
join rep in _context.HrEmpReportings on emp.EmpId equals rep.EmpId into repGroup
from rep in repGroup.DefaultIfEmpty()
join highView in _context.HighLevelViewTables on emp.LastEntity equals highView.LastEntityId into highViewGroup
from highView in highViewGroup.DefaultIfEmpty()
join img in _context.HrEmpImages on emp.EmpId equals img.EmpId into imgGroup
from img in imgGroup.DefaultIfEmpty()
join currStatus in _context.EmployeeCurrentStatuses on emp.CurrentStatus equals currStatus.Status into currStatusGroup
from currStatus in currStatusGroup.DefaultIfEmpty()
join empStatusSettings in _context.HrEmpStatusSettings on Convert.ToInt32(emp.EmpStatus) equals empStatusSettings.StatusId into empStatusGroup
from empStatusSettings in empStatusGroup.DefaultIfEmpty()
join reason in _context.ReasonMasters on Convert.ToInt32(emp.ResignationReason) equals reason.ReasonId into reasonGroup
from reason in reasonGroup.DefaultIfEmpty()
join country in _context.AdmCountryMasters on pers.Nationality equals country.CountryId into countryGroup
from country in countryGroup.DefaultIfEmpty()
join empDetails in _context.EmployeeDetails on rep.ReprotToWhome equals empDetails.EmpId into empDetailsGroup
from empDetails in empDetailsGroup.DefaultIfEmpty()
select new EmployeeResultDto
{
EmpId = emp.EmpId,
ImageUrl = img.ImageUrl,
EmpCode = emp.EmpCode,
Name = emp.Name,
GuardiansName = emp.GuardiansName,
JoinDate = emp.JoinDate,
DataDate = emp.DataDate,
EmpStatusDesc = currStatus.StatusDesc,
EmpStatus = empStatusSettings.StatusDesc,
Gender = GetGender(emp.Gender).ToString(),
SeperationStatus = emp.SeperationStatus,
OfficialEmail = addr.OfficialEmail,
PersonalEmail = addr.PersonalEmail,
Phone = addr.Phone,
MaritalStatus = GetMaritalStatus(pers.MaritalStatus).ToString(),
Age = emp.Age,
ProbationDt = emp.ProbationDt,
LevelOneDescription = highView.LevelOneDescription,
LevelTwoDescription = highView.LevelTwoDescription,
ProbationStatus = emp.Probation.ToString(),
Nationality = country.Nationality,
IsSave = emp.IsSave,
EmpFileNumber = emp.EmpFileNumber,
CurrentStatus = emp.CurrentStatus,
LevelThreeDescription = highView.LevelThreeDescription,
LevelFourDescription = highView.LevelFourDescription,
LevelFiveDescription = highView.LevelFiveDescription,
LevelSixDescription = highView.LevelSixDescription,
LevelSevenDescription = highView.LevelSevenDescription,
LevelEightDescription = highView.LevelEightDescription,
LevelNineDescription = highView.LevelNineDescription,
LevelTenDescription = highView.LevelTenDescription,
LevelElevenDescription = highView.LevelElevenDescription,
LevelTwelveDescription = highView.LevelTwelveDescription,
ReportingEmployeeCode = empDetails.EmpCode,
ReportingEmployeeName = empDetails.Name,
WorkingStatus = emp.WorkingStatus,
RelievingDate = emp.RelievingDate
};
var totalRecords = await finalQuery.CountAsync();
var paginatedResult = finalQuery
.OrderBy(x => x.EmpCode) // Sorting logic
.Skip((pageNumber - 1) * pageSize)
.Take(pageSize)
.ToList();
return new PaginatedResult<EmployeeResultDto>
{
draw = draw,
recordsTotal = totalRecords,
recordsFiltered = totalRecords,
PageNumber = pageNumber,
PageSize = pageSize,
data = paginatedResult
};
}
there are two conditions - 1. Active (1,2,3,7), 2. InActive(4,5,8,9)
the issue in my code is that when i set Inactive mode, record coming but not proper records as exact count
I have a function to filter the emp status
public async Task<List<int?>> GetFilteredEmployeesAsync(HashSet<int> statusSet)
{
var nonResignationStatuses = await GetStatusSetAsync(false);
var resignationStatuses = await GetStatusSetAsync(true);
var excludedStatuses = await GetStatusSetAsync(true, statusSet);
var employeeList = await _context.HrEmpMasters.Where(a => (nonResignationStatuses.Contains((int)a.EmpStatus) && resignationStatuses.Any(r => statusSet.Contains(r))) && !excludedStatuses.Contains((int)a.SeperationStatus) || ((nonResignationStatuses.Contains((int)a.EmpStatus) || statusSet.Contains((int)a.EmpStatus)) && resignationStatuses.Any(r => statusSet.Contains(r)) && excludedStatuses.Contains((int)a.SeperationStatus))).Select(a => a.EmpStatus).ToListAsync();
return employeeList;
}
this code is for mathing to sql code part
a.emp_status in(select item from dbo.SplitNEW(@Status,',') where Item in(select StatusId from HR_EMP_STATUS_SETTINGS where isResignation!=1)) and exists(select 1 from dbo.SplitNEW(@Status,',') where item in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation!=1)) and ISNULL(a.SeperationStatus,0) not in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1 --and StatusID not in(select item from dbo.SplitNEW(@Status,','))
)
or
(a.emp_status in(select item from dbo.SplitNEW(@Status,',') where Item in(select StatusId from HR_EMP_STATUS_SETTINGS where isResignation!=1)) or (a.emp_status='1' or a.emp_status='2' or a.emp_status='3' or a.emp_status='7')) and exists(select 1 from dbo.SplitNEW(@Status,',') where item in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1)) and ISNULL(a.SeperationStatus,0) in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1-- and StatusID in(select item from dbo.SplitNEW(@Status,','))
)
can you check it and fix it? Thanks in advance.