写在算法前:最近在做SQL多条件查询,感觉自己写查询逻辑太过麻烦。比如,假如有一个查询条件,就需要分析两种情况(空条件和一个条件);两个条件就要四中情况;三个条件,8种情况.......
这样是不是也太麻烦了?!下面这种方法可以为你简化很多步骤,一起来看看吧。。。
1,首先你要自定义两个方法(这里封装到类里):
public static class MyHelper
{
//查询方法
public List<User> AjaxSeniorSearchUser(string userName, string nickName, string ddlClasses)
{
List<User> eqList = new List<User>();
Expression<Func<User, bool>> predicate = p => 1 == 1;
if (userName == "" && nickName == "" && ddlClasses == "0")
{
predicate = predicate.And(p => 1 == 1);
}
else
{
if (userName != "")
{
predicate = predicate.And(p => p.UserName.Contains(userName.Trim()));
}
if (nickName != "")
{
predicate = predicate.And(p => p.NickName.Contains(nickName.Trim()));
}
if (ddlClasses != "0")
{
int tempClass=int.Parse(ddlClasses);
predicate = predicate.And(p => p.ClassID ==tempClass );
}
}
return SeachBodyFunction(eqList, predicate);
}
//查询的主方法
private List<User> SeachBodyFunction(List<User> eqList, Expression<Func<User, bool>> predicate)
{
var User = db.User.Where(predicate).Join(db.User, u => u.TeacherID, r => r.ID, (u, r) => new
{
ID = u.ID,
UserName = u.UserName,
NickName = u.NickName,
//ClassName = u.ClassName,
Sex = u.Sex,
TeacherID = u.ID,
TeacherName = r.NickName,
PracticeCompany = u.PracticeCompany,
Flow = u.Flow
}).ToList();
foreach (var eq in User)
{
User UserFo = new User();
UserFo.ID=eq.ID;
UserFo.UserName=eq.UserName;
UserFo.NickName=eq.NickName;
UserFo.SexName = EnumTool.GetSex(eq.Sex.Value);
UserFo.TeacherID=eq.TeacherID;
UserFo.TeacherName=eq.TeacherName;
UserFo.PracticeCompany=eq.PracticeCompany;
UserFo.Flow = eq.Flow;
eqList.Add(UserFo);
}
return eqList;
}
},
2,然后要自定义一个扩展方法:
namespace MyProject.Tool
{
public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
{
return first.Compose(second, Expression.And);
}
}
3,主函数调用示例:
首先别忘记引用Tool,
using MyProject.Tool;
public ActionResult StudentIndex(string userName, string nickName, string ddlClasses)//这里实在MVC下的示例,如果是别的,换成相应的调用函数就行了
{
List<User> userList=AjaxSeniorSearchUser(userName, nickName, ddlClasses);//至此,你已经得到查询出来的集合了
return View(userList);
}
完结----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
下面的(你可以完全不用看了)
4,前台页面显示示例
<table>
@foreach (var item in Model)
{
<tr>
<td>
<input name='checkItem' type="checkbox" value="@item.ID" /></td>
<td>
@Html.DisplayFor(modelItem => item.UserName)
</td>
<td>
@Html.DisplayFor(modelItem => item.NickName)
</td>
<td>
@Html.DisplayFor(modelItem => item.ClassName)
</td>
<td>
@Html.DisplayFor(modelItem => item.SexName)
</td>
<td>
@Html.DisplayFor(modelItem => item.PracticeCompany)
</td>
<td>
@Html.DisplayFor(modelItem => item.TeacherName)
</td>
<td>
@Html.DisplayFor(modelItem => item.Flow)
</td>
</tr>
}
</table>