Dynamic formation of where clause in LINQ

Question

I need a help in LINQ. here is my scenario. I've two tables for storing Employee details like name, ID, Salary, Primary_Emp and Year. User can either select Name or ID from DDL and Pass the input value to SP. SQL Server will return the data based on the input given.

tbl_Employee
EmpID   EmpName     Salary  Primary_Emp
1       xxx         10000   Yes
2       yyy         20000   Yes
3       zzz         30000   Yes

tbl_Year
EmpID   [Year]
1       2010
2       2011
3       2011

Here is my SQLQuery. I wanted to apply the same conditions in LINQ. NOTE:- User has two option to select EmpID or EmpName from DDL and input the value in textbox (Free text)

Input Parameters:
Name varchar(100)
EmpID varchar(100)

select distinct e.EmpID from 
tbl_employee e
inner join 
tbl_Year y
on e.EmpID = y.EmpID
where E.Primary_Emp = 'Yes'
**AND e.EmpName = (SELECT CASE WHEN @Key = 'Name' THEN @Value ELSE e.Empname END)
    AND e.EmpID = (SELECT CASE WHEN @Key = 'EmpID' THEN @Value ELSE e.EmpID END)**

Question: how to form the dynamic where clause in linq. Here the problem is LHS is also dynamic. If the user pass EmpID, it should not consider the Name and Vice versa.

JOIN in linq is mandatory!

Answer 1

If it's just the separate and filter expressions you want to apply conditionally, you could do the following:

var query = from e in dbcontext.tbl_employee 
            join y in tbl_Year 
            on e.EmpID equals y.EmpID 
            where e.Primary_Emp = "Yes"
            select e;

if (key == "EmpName")
    query = from e in query where e.EmpName = value select e;
else if (key == "EmpID")
    query = from e in query where e.EmpID = value select e;

var result = (from e in query
              select e.EmpID).Distinct();

You can simply keep stacking where clauses conditionally this way.