LINQ中where子句的动态形成 [英] Dynamic formation of where clause in LINQ

问题描述

我需要LINQ方面的帮助.这是我的情况.我有两个表用于存储员工详细信息，例如姓名，ID，薪水，Primary_Emp和Year.用户可以从DDL中选择名称"或"ID"，然后将输入值传递给SP. SQL Server将根据给定的输入返回数据.

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

这是我的SQLQuery.我想在LINQ中应用相同的条件. 注意:-用户有两种选择，可以从DDL中选择EmpID或EmpName并在文本框中输入值(自由文本)

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)**

问题:如何在linq中形成动态的 where 子句.这里的问题是LHS也是动态的.如果用户传递EmpID，则不应考虑名称，反之亦然.

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.

必须在linq中加入！

JOIN in linq is mandatory!

推荐答案

如果只是要有条件地应用的单独的和过滤器表达式，则可以执行以下操作:

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();

您可以仅通过这种方式有条件地继续堆叠 where 子句.

You can simply keep stacking where clauses conditionally this way.

这篇关于LINQ中where子句的动态形成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！