PredicateBuilder类(linq多条件组合查询)
PredicateBuilder类如下:
public static class PredicateBuilder
{
/// <summary>
/// 机关函数应用True时:单个AND有效,多个AND有效;单个OR无效,多个OR无效;混应时写在AND后的OR有效
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public static Expression<Func<T, bool>> True<T>() { return f => true; }
/// <summary>
/// 机关函数应用False时:单个AND无效,多个AND无效;单个OR有效,多个OR有效;混应时写在OR后面的AND有效
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public static Expression<Func<T, bool>> False<T>() { return f => false; }
public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1,
Expression<Func<T, bool>> expr2)
{
var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
return Expression.Lambda<Func<T, bool>>
(Expression.Or(expr1.Body, invokedExpr), expr1.Parameters);
}
public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1,
Expression<Func<T, bool>> expr2)
{
var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
return Expression.Lambda<Func<T, bool>>
(Expression.And(expr1.Body, invokedExpr), expr1.Parameters);
}
}
多条件查询的代码:
/// <summary>
/// 多条件查询
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnSearch_Click(object sender, EventArgs e)
{
using(LinqDBDataContext db = new LinqDBDataContext())
{
var list = db.StuInfo;
var where = PredicateBuilder.True<StuInfo>();
if(this.txtName.Text.Trim().Length!=0)
{
where = where.And(p => p.StuName.Contains(this.txtName.Text.Trim()));
}
if(this.txtAge.Text.Trim().Length!=0)
{
where = where.And(p => p.StuAge == Convert.ToInt32(this.txtAge.Text.Trim()));
}
var result = list.Where(where).ToList();
this.repStuInfo.DataSource = result;
this.repStuInfo.DataBind();
}
}
上面代码中,txtName是姓名文本框,txtAge是年龄文本框,因为要进行and条件查询所以一开始使用PredicateBuilder.True<StuInfo>()来创建初始为true的where条件,
如果进行or多条件查询,就应该使用PredicateBuilder.False<StuInfo>()来创建初始为false的where条件
______________________________________
以下例子是参考以上的实现“先构建条件再加入Lambda表达式”的代码:
public List<DHCP_NonJxxxxDevice> GetNotJxxxxHistory(string site, DateTime? start, DateTime? end, string keyWord, bool newload = false)
{
var list = new List<DHCP_NonJxxxxDevice>();
try
{
Expression<Func<DHCP_NonJxxxxDevice, bool>> filterExp=null;
if (!string.IsNullOrWhiteSpace(site))
{
filterExp = row => row.SiteName == site;
}
if (newload) //打开页面时初次加载
{
Expression<Func<DHCP_NonJxxxxDevice, bool>> sec = p => p.Action == null;
filterExp =
Expression.Lambda<Func<DHCP_NonJxxxxDevice, bool>>(
ParameterExpression.AndAlso(
filterExp.Body, Expression.Invoke(sec, filterExp.Parameters.Cast<Expression>())
)
, filterExp.Parameters
);
}
else
{
if (start != null && end != null)
{
Expression<Func<DHCP_NonJxxxxDevice, bool>> sec = p => p.InsertTime >= start && p.InsertTime <= end;
filterExp =
Expression.Lambda<Func<DHCP_NonJxxxxDevice, bool>>(
ParameterExpression.AndAlso(
filterExp.Body, Expression.Invoke(sec, filterExp.Parameters.Cast<Expression>())
)
, filterExp.Parameters
);
}
if (!string.IsNullOrEmpty(keyWord))
{
Expression<Func<DHCP_NonJxxxxDevice, bool>> sec =
p => p.PcName.Contains(keyWord)
|| p.IPAddress.Contains(keyWord)
|| p.MacAddress.Contains(keyWord)
|| p.Building.Contains(keyWord)
|| p.Floor.Contains(keyWord);
filterExp = Expression.Lambda<Func<DHCP_NonJxxxxDevice, bool>>(
ParameterExpression.AndAlso(filterExp.Body,Expression.Invoke(sec,filterExp.Parameters.Cast<Expression>()))
, filterExp.Parameters);
}
}
var data = filterExp != null
? pcContext.DHCP_NonJxxxxDevices.Where(filterExp)
: pcContext.DHCP_NonJxxxxDevices;
list = data.ToList();
}
catch (Exception ex)
{
LogHelper.RecordLogToDb(0, "AMS.BIZ.Monitor", "Class:MonitorHelper Function:GetNotJxxxxHistory Error:" + ex.Message, "admin");
}
return list;
}