/// <summary>
/// 分页查询
/// 如果T中有OraModel，并且查询总记录数，并且查询条件都隶属于OraModel，则使用单表查询
/// </summary>
/// <param name="searchParameters">查询参数 <see cref="DynamicSearch.SearchParameters" /></param>
/// <param name="sqlwhere">字符串的查询条件</param>
/// <returns>一个待查询的结果集</returns>
public IQueryable <T> GetByPage(SearchParameters searchParameters, string sqlwhere = "")
{
//处理EF查询时，查询null值问题
searchParameters.BuildEmptySearch();
if (searchParameters.PageInfo.IsGetTotalCount && searchParameters.PageInfo.PageSize <= 1)
{
var attr = ReflectionTool.GetCustomAttributeEx <ViewOraModelAttribute>(typeof(T));
if (attr != null)
{
var oraModelPropInfos = ReflectionTool.GetPropertyInfosFromCache(attr.OraModel);
// 判断是否所有的查询条件都在单表内
var sqlWhereCondition = sqlwhere.Split(
new[] { " And ", " Or ", " AND ", " OR ", " and ", " or " },
StringSplitOptions.RemoveEmptyEntries);
sqlWhereCondition = GetPropNameBySqlWhere(sqlWhereCondition);
if (searchParameters.QueryModel?.Items != null &&
searchParameters.QueryModel.Items.All(a => oraModelPropInfos.Any(b => b.Name == a.Field)) &&
sqlWhereCondition.All(a => oraModelPropInfos.Any(b => a.ToLower().Equals(b.Name.ToLower()))))
{
GetSingleTableQuickCount(searchParameters, sqlwhere, attr);
return(new List <T>().AsQueryable());
}
}
}
IQueryable <T> list = _dbSet.Where(searchParameters.QueryModel);
if (!string.IsNullOrEmpty(sqlwhere))
{
list = list.Where(sqlwhere);
}
var totalCount = 0;
var result = GetByPage(list, searchParameters.PageInfo, out totalCount);
searchParameters.PageInfo.TotalCount = totalCount;
return(result);
}
/// <summary>
/// 根据QueryModel组织sqlWhere语句,如果有字段前缀的话,需要提前增加进来
/// 如果是Dapper 需要转换成
/// </summary>
/// <returns>Where 语句</returns>
public string ConvertToSqlWhere(SearchParameters searchParameters, DbType dbType)
{
if (searchParameters == null)
{
return("");
}
//处理sql查询时，查询null值问题
searchParameters.BuildEmptySearch();
// 复制一个,避免修改的时候影响外部数据
var copyCondition = JsonEx.JsonConvert.CopyObject(searchParameters.QueryModel.Items);
copyCondition.Sort((a, b) =>
{
return(a.Field.CompareTo(b.Field));
});
var sb = new StringBuilder();
List <string> groups = new List <string>();
// 如果是Dapper 则把Condition 的 Field 和 Value 替换掉
var mapDic = DbColumnMaps.GetDbContextFiledMaps();
for (int i = 0; i < copyCondition.Count; i++)
{
var item = copyCondition[i];
if (dbType == DbType.Oracle)
{
// Oracle 字段需要前后双引号
item.Value = "\"" + Prefix + item.Field + i + "\"";
}
else if (item.Value != null)
{
item.Value = Prefix + item.Field + i;
}
if (mapDic.ContainsKey(item.Field))
{
item.Field = mapDic[item.Field];
}
}
foreach (var conditionItem in copyCondition)
{
if (!string.IsNullOrEmpty(conditionItem.OrGroup))
{
if (!groups.Contains(conditionItem.OrGroup))
{
var sbChild = new StringBuilder();
foreach (var senItem in copyCondition)
{
if (senItem.OrGroup == conditionItem.OrGroup)
{
if (sbChild.Length > 0)
{
sbChild.Append(" or ");
}
sbChild.Append(GetQueryCloumn(senItem) + " " + ConvertMethodToSql(senItem.Method, senItem.Value));
}
}
if (sb.Length > 0)
{
sb.Append(" and ");
}
sb.Append("(" + sbChild.ToString() + ")");
groups.Add(conditionItem.OrGroup);
}
}
else
{
if (sb.Length > 0)
{
sb.Append(" and ");
}
sb.Append((string.IsNullOrEmpty(conditionItem.Prefix) ? "" : (conditionItem.Prefix + ".")) + conditionItem.Field + " " + ConvertMethodToSql(conditionItem.Method, conditionItem.Value));
}
}
return(sb.ToString());
}
