My name
is
Jon Skeet

How to filter data using ternary operator in linq

var PFOpeningList = (from emp in dbContext.EmployeeList
                                 join dsg in dbContext.hrmDesig on emp.HrmDesignationId equals dsg.Id into DsgLeftJoin
                                 from dsgleftjoin in DsgLeftJoin.DefaultIfEmpty()
                                 join opb in dbContext.PfOpeningBalances on emp.Id equals opb.HrmEmployeeId into OpbLeftJoin
                                 from opbleftjoin in OpbLeftJoin.DefaultIfEmpty()
                                 where opbleftjoin.CmnCalendarYearId == clndrId
                                       && (empId != 0 ? emp.Id == empId
                                       : (dptId != 0 ? emp.HrmDepartmentId == dptId
                                         : (officId != 0 ? emp.HrmOfficeId == officId : emp.CmnCompanyId == CmnId)))
                                       && emp.CmnCompanyId == CmnId
                                 select new
                                 {
                                     EmployeeId=emp.Id,
                                     EmployeeName=emp.Name,
                                     Designation = dsgleftjoin.Name,
                                     OpeningIncome = (decimal?)opbleftjoin.OpeningIncome,
                                     EmployeeContribution = (decimal?)opbleftjoin.EmployeeContribution,
                                     CompanyContribution = (decimal?)opbleftjoin.CompanyContribution
                                 }).ToList();

I want to achieve all employees with designation(hrmDesig) from EmployeeList. Filtering with Calendar Year is mandatory. But if user select Office/Department/Employee, data should be filtered as well. Can anyone help please?

There's no need to use the conditional operator at all here... I suspect you want a where clause of:

where opbleftjoin.CmnCalendarYearId == clndrId
   && (empId == 0 || emp.Id == empId)
   && (dptId == 0 || emp.HrmDepartmentId == dptId)
   && (officId != 0 ? emp.HrmOfficeId == officId)
   && emp.CmnCompanyId == CmnId;

Better yet, you could add the conditions in steps - just have the mandatory ones to start with:

var query = from emp in dbContext.EmployeeList
            join dsg in dbContext.hrmDesig on emp.HrmDesignationId equals dsg.Id into DsgLeftJoin
            from dsgleftjoin in DsgLeftJoin.DefaultIfEmpty()
            join opb in dbContext.PfOpeningBalances on emp.Id equals opb.HrmEmployeeId into OpbLeftJoin
            from opbleftjoin in OpbLeftJoin.DefaultIfEmpty()
            where opbleftjoin.CmnCalendarYearId == clndrId
               && emp.CmnCompanyId == CmnId
            select new { emp, dsgleftjoin, opbleftjoin };

if (empId != 0)
{
    query = query.Where(x => x.emp.Id == empId);
}
// etc

Also note that your query currently seems to assume that dsgleftjoin and opbleftjoin are non-null, when they could easily be null due to the left join.

See more on this question at Stackoverflow