项目中经常用到组合条件查询,根据用户配置的查询条件进行搜索,拼接SQL容易造成SQL注入,普通的LINQ可以用表达式树来完成,但也比较麻烦。有个System.Linq.Dynamic
用起来比较方便。
NuGet:
Install-Package System.Linq.Dynamic
大致的用法是这样的:
//NULL类型判断
var query = dbContext.sys_user.Where("userid!=null").OrderBy("id desc");
//整形的查询
var query = dbContext.sys_user.Where("id>0").OrderBy("id desc");
//整形的查询 带参数
var query = dbContext.sys_user.Where("id>@0", ).OrderBy("id desc");
//字符型查询
var query = dbContext.sys_user.Where("username=\"张三\").OrderBy("id desc");
//like查询
var query = dbContext.sys_user.Where("username.Contains(\"key\")").OrderBy("id desc");
//日期类型
var query = dbContext.sys_user.Where("createtime <= @0", DateTime.Now).OrderBy("id desc");
//组合条件
var query = dbContext.sys_user.Where("id>0 and username=\"张三\"").OrderBy("id desc");
- 安装
- VS->工具栏->
NuGet
程序管理器,System.Linq.Dynamic
- 注意:
使用动态查询必须先调用AsQueryable()
方法,因为动态扩展仅适用于实现IQueryable
的集合。然后迭代结果与常规Linq
一样。 - 动态查询
using System.Linq.Dynamic
/// 得到明细过滤条件
/// </summary>
/// <returns></returns>
private string GetFilterWhere(out List<object> paramList)
{
StringBuilder strWhere = new StringBuilder();
object items;
paramList = new List<object>();
strWhere.AppendFormat("ProductionDate >=@{0}", paramList.Count);
paramList.Add(Convert.ToDateTime(startDetailDateEdit.EditValue).Date);
strWhere.AppendFormat(" && ProductionDate <=@{0}", paramList.Count);
paramList.Add(Convert.ToDateTime(endDetailDateEdit.EditValue).Date);
items = factoryDetailCheckedComboBoxEdit.Properties.GetCheckedItems();
if (items != null && items.ToString() != string.Empty)
{//数组参数,数组中是否包含实例相等的FactoryID值,“outerIt 代表实例”
strWhere.AppendFormat(" && @{0}.Contains(outerIt.FactoryID)", paramList.Count);
paramList.Add(UIHelper.ToArray(items.ToString()));
}
if (cbCustomer.HasValue())
{
strWhere.AppendFormat(" && CustomerID=@{0}", paramList.Count);
paramList.Add(Convert.ToInt32(cbCustomer.SelectedValue));
}
if (txtMachineCode.Text.Trim() != string.Empty)
{
strWhere.AppendFormat(" && MachineCode.Contains(@{0})", paramList.Count);
paramList.Add(txtMachineCode.Text.Trim());
}
return strWhere.ToString();
}
//查询
List<object> paramList;
string strWhere = GetFilterWhere(out paramList);
//根据条件查询
var query = sourceList.AsQueryable<FacilityEfficiencyReportInfo>().Where(strWhere, paramList.ToArray());
var resultList = (from dynamic g in query select g).ToList();
动态分组
/// <summary>
/// 得到分组的列名字符串
/// </summary>
/// <returns></returns>
private string GetGroupNames()
{
StringBuilder groupName = new StringBuilder();
if (m_FirstGroupFieldName != null)
{
groupName.Append(m_FirstGroupFieldName.Key);
}
if (m_SecondGroupFieldName != null)
{
if(groupName.Length>)
{
groupName.AppendFormat(",{0}", m_SecondGroupFieldName.Key);
}
else
{
groupName.Append(m_SecondGroupFieldName.Key);
}
}
if (m_ThirdGroupFieldName != null)
{
if(groupName.Length>)
{
groupName.AppendFormat(",{0}", m_ThirdGroupFieldName.Key);
}
else
{
groupName.Append(m_ThirdGroupFieldName.Key);
}
}
return groupName.ToString();
}
object value;
string groupNames = GetGroupNames();
List<FacilityEfficiencyReportInfo> actualGroupList = new List<FacilityEfficiencyReportInfo>();//实际组列表,不区分日期
if (string.IsNullOrEmpty(groupNames))
{
actualGroupList = groupList.GroupBy(m => m.ID).Select(g => new FacilityEfficiencyReportInfo { FinishedAmount = g.Sum(m => m.FinishedAmount), Workers = g.Sum(m => m.Workers), Duration = g.Sum(m => m.Duration), Sam = g.Average(m => m.Sam),List=g.ToList() }).ToList();
}
else
{
var query = groupList.AsQueryable()
.GroupBy(string.Format("new ({0})", groupNames), "it")
.Select("new(it.Key as Key, it as GroupList)");
var tempGroupList = (from dynamic g in query select g).ToList();
foreach (var g in tempGroupList)
{//每一组合并成一个FacilityEfficiencyReportInfo实例,明细放入model.List列表
FacilityEfficiencyReportInfo newModel = null;
object test = g.Key;
foreach (FacilityEfficiencyReportInfo m in g.GroupList)
{
if (newModel == null)
{
newModel = m.Clone();
newModel.InitDate();
newModel.List = new List<FacilityEfficiencyReportInfo>();
}
newModel.List.AddRange(m.List);
}
actualGroupList.Add(newModel);
}
}
query = Contact.GetContactsList().AsQueryable().Where("@0.Contains(outerIt.Country)", newList<String>() { "Austria", "Poland" });
其中“outerIt
”代表整理上下文:“联系人”列表。为此,我们应该定义一个“outerIt
”关键字
query = Contact.GetContactsList().AsQueryable().Where("@0.Contains(outerIt.Country) && it.BirthDate.Year > @1", new List<string>() { "Austria", "Poland" }, );