/// <summary>
/// EF单实体查询封装
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="Query">IQueryable对象</param>
/// <param name="gridParam">过滤条件</param>
/// <returns>查询结果</returns>
public static PaginationResult <T> PageQuery <T>(this IQueryable <T> Query, QueryCondition gridParam)
{
//查询条件
EFFilter filter = GetParameterSQL <T>(gridParam);
var query = Query.Where(filter.Filter, filter.ListArgs.ToArray());
//查询结果
PaginationResult <T> result = new PaginationResult <T>();
if (gridParam.IsPagination)
{
int PageSize = gridParam.PageSize;
int PageIndex = gridParam.PageIndex < 0 ? 0 : gridParam.PageIndex;
//获取排序信息
string sort = GetSort(gridParam, typeof(T).FullName);
result.Data = query.OrderBy(sort).Skip(PageIndex * PageSize).Take(PageSize).ToList <T>();
if (gridParam.IsCalcTotal)
{
result.Total = query.Count();
result.TotalPage = Convert.ToInt32(Math.Ceiling(result.Total * 1.0 / PageSize));
}
else
{
result.Total = result.Data.Count();
}
}
else
{
result.Data = query.ToList();
result.Total = result.Data.Count();
}
return(result);
}
/// <summary>
/// 根据过滤条件统计总数
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="Query">IQueryable对象</param>
/// <param name="gridParam">过滤条件</param>
/// <returns>符合条件总数</returns>
public static int Count <T>(this IQueryable <T> Query, QueryCondition gridParam)
{
//查询条件
EFFilter filter = GetParameterSQL <T>(gridParam);
var query = Query.Where(filter.Filter, filter.ListArgs.ToArray());
if (gridParam.IsPagination)
{
if (gridParam.IsCalcTotal)
{
return(query.Count());
}
}
return(0);
}
/// <summary>
/// 通过查询条件,获取参数化查询SQL
/// </summary>
/// <param name="gridParam">过滤条件</param>
/// <returns>过滤条件字符</returns>
private static EFFilter GetParameterSQL <T>(QueryCondition gridParam)
{
EFFilter result = new EFFilter();
//参数值集合
List <object> listArgs = new List <object>();
string filter = "1=1";
#region "处理动态过滤条件"
if (gridParam.FilterList != null && gridParam.FilterList.Count > 0)
{
StringBuilder sb = new StringBuilder();
int paramCount = 0;
DateTime dateTime;
//操作符
string strOperator = string.Empty;
foreach (var item in gridParam.FilterList)
{
//字段名称为空则跳过
if (string.IsNullOrEmpty(item.FieldName))
{
continue;
}
//匹配枚举，防止SQL注入
Operator operatorEnum = (Operator)Enum.Parse(typeof(Operator), item.Operator, true);
//跳过字段值为空的
if (operatorEnum != Operator.Null && operatorEnum != Operator.NotNull && string.IsNullOrEmpty(item.FieldValue))
{
continue;
}
strOperator = operatorEnum.GetDescription();
if (item.IgnoreCase && !item.IsDateTime)
{
//2016-07-19添加查询时忽略大小写比较
item.FieldValue = item.FieldValue.ToLower();
item.FieldName = string.Format("{0}.ToLower()", item.FieldName);
}
switch (operatorEnum)
{
//等于,不等于，小于，大于，小于等于，大于等于
case Operator.EQ:
case Operator.NE:
case Operator.GT:
case Operator.GE:
case Operator.LT:
case Operator.LE:
if (item.IsDateTime)
{
if (DateTime.TryParse(item.FieldValue, out dateTime))
{
if (!item.FieldValue.Contains("00:00:00") && dateTime.ToString("HH:mm:ss") == "00:00:00")
{
if (operatorEnum == Operator.LE)
{
listArgs.Add(DateTime.Parse(dateTime.ToString("yyyy-MM-dd") + " 23:59:59"));
}
else
{
listArgs.Add(dateTime);
}
}
else
{
listArgs.Add(dateTime);
}
sb.AppendFormat(" AND {0} {1} @{2}", item.FieldName, strOperator, paramCount);
}
}
else
{
listArgs.Add(ConvertToType(item.FieldValue, GetPropType <T>(item.FieldName)));
sb.AppendFormat(" AND {0} {1} @{2}", item.FieldName, strOperator, paramCount);
}
paramCount++;
break;
case Operator.Like:
case Operator.NotLike:
case Operator.LLike:
case Operator.RLike:
listArgs.Add(item.FieldValue);
if (operatorEnum == Operator.Like)
{
sb.AppendFormat(" AND {0}.Contains(@{1})", item.FieldName, paramCount);
}
else if (operatorEnum == Operator.NotLike)
{
sb.AppendFormat(" AND !{0}.Contains(@{1})", item.FieldName, paramCount);
}
else if (operatorEnum == Operator.LLike)
{
sb.AppendFormat(" AND {0}.EndsWith(@{1})", item.FieldName, paramCount);
}
else if (operatorEnum == Operator.RLike)
{
sb.AppendFormat(" AND {0}.StartsWith(@{1})", item.FieldName, paramCount);
}
paramCount++;
break;
case Operator.Null:
listArgs.Add(item.FieldValue);
sb.AppendFormat(" AND {0}=null", item.FieldName);
paramCount++;
break;
case Operator.NotNull:
listArgs.Add(item.FieldValue);
sb.AppendFormat(" AND {0}!=null", item.FieldName);
paramCount++;
break;
case Operator.In:
sb.AppendFormat(" AND (");
foreach (var schar in item.FieldValue.Split(','))
{
listArgs.Add(schar);
sb.AppendFormat("{0}=@{1} or ", item.FieldName, paramCount);
paramCount++;
}
sb.Remove(sb.Length - 3, 3);
sb.AppendFormat(" )");
break;
case Operator.NotIn:
sb.AppendFormat(" AND (");
foreach (var schar in item.FieldValue.Split(','))
{
listArgs.Add(schar);
sb.AppendFormat("{0}!=@{1} and ", item.FieldName, paramCount);
paramCount++;
}
sb.Remove(sb.Length - 3, 3);
sb.AppendFormat(" )");
break;
}
if (sb.ToString().Length > 0)
{
filter = sb.ToString().Substring(4, sb.Length - 4);
}
}
#endregion
}
result.Filter = filter;
result.ListArgs = listArgs;
return(result);
}