/// <summary>
/// 从起始点向后取指定条数的数据
/// </summary>
/// <typeparam name="T">表实体类型</typeparam>
/// <param name="queryable">查询对象</param>
/// <param name="num">指定条数</param>
/// <returns>Queryable</returns>
public static Queryable <T> Take <T>(this Queryable <T> queryable, int num)
{
if (queryable.OrderByValue.IsNullOrEmpty())
{
throw new Exception(".Take必需使用.OrderBy排序");
}
queryable.Take = num;
return(queryable);
}
/// <summary>
/// 分组
/// </summary>
/// <typeparam name="T">表实体类型</typeparam>
/// <param name="queryable">查询对象</param>
/// <param name="expression">分组字段 (例如：it=>it.fieldName)</param>
/// <returns>Queryable</returns>
public static Queryable <T> GroupBy <T>(this Queryable <T> queryable, Expression <Func <T, object> > expression)
{
ResolveExpress re = new ResolveExpress();
var field = re.GetExpressionRightField(expression, queryable.DB);
var pre = queryable.GroupByValue.IsValuable() ? "," : "";
queryable.GroupByValue += pre + field;
return(queryable);
}
/// <summary>
/// 跳过序列中指定数量的元素，然后返回剩余的元素。
/// </summary>
/// <typeparam name="T">表实体类型</typeparam>
/// <param name="queryable">查询对象</param>
/// <param name="index">指定数量的索引</param>
/// <returns>Queryable</returns>
public static Queryable <T> Skip <T>(this Queryable <T> queryable, int index)
{
if (queryable.OrderByValue.IsNullOrEmpty())
{
throw new Exception(".Skip必需使用.Order排序");
}
queryable.Skip = index;
return(queryable);
}
/// <summary>
/// 返回Sql和参数信息
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="queryable">查询对象</param>
/// <returns></returns>
public static KeyValuePair <string, string[]> ToSql <T>(this Queryable <T> queryable)
{
var sql = SqlSugarTool.GetQueryableSql <T>(queryable).ToString();
var pars = queryable.Params.ToArray();
var reval = new Dictionary <string, string[]>();
reval.Add(sql, pars.Select(it => it.ParameterName + ":" + it.Value).ToArray());
return(reval.First());
}
/// <summary>
/// 排序
/// </summary>
/// <typeparam name="T">表实体类型</typeparam>
/// <typeparam name="T2">表实体类型</typeparam>
/// <param name="queryable">查询对象</param>
/// <param name="expression">例如 (s1,s2)=>s1.id,相当于 order by s1.id</param>
/// <param name="type">排序类型</param>
/// <returns>Queryable</returns>
public static Queryable <T> OrderBy <T, T2>(this Queryable <T> queryable, Expression <Func <T, T2, object> > expression, OrderByType type = OrderByType.asc)
{
ResolveExpress re = new ResolveExpress();
var field = re.GetExpressionRightFieldByNT(expression, queryable.DB);
var pre = queryable.OrderByValue.IsValuable() ? "," : "";
queryable.OrderByValue += pre + field + " " + type.ToString().ToUpper();
return(queryable);
}
/// <summary>
/// 将Queryable转换为DataTable
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="queryable">查询对象</param>
/// <returns>DataTable</returns>
public static DataTable ToDataTable <T>(this Queryable <T> queryable)
{
StringBuilder sbSql = SqlSugarTool.GetQueryableSql <T>(queryable);
var dataTable = queryable.DB.GetDataTable(sbSql.ToString(), queryable.Params.ToArray());
queryable = null;
sbSql = null;
return(dataTable);
}
/// <summary>
/// 返回序列的唯一元素；如果该序列并非恰好包含一个元素，则会引发异常。
/// </summary>
/// <typeparam name="T">表实体类型</typeparam>
/// <param name="queryable">查询对象</param>
/// <returns>T</returns>
public static T Single <T>(this Queryable <T> queryable)
{
if (queryable.OrderByValue.IsNullOrEmpty())
{
queryable.OrderByValue = "GETDATE()";
}
queryable.Skip(0);
queryable.Take(1);
return(queryable.ToList().Single());
}
/// <summary>
/// 条件筛选 ( 例如：InFieldName 为 id, inValues 值为 new string[]{"1" ,"2"} 生成的SQL就是 id in('1','2') )
/// </summary>
/// <typeparam name="T">表实体类型</typeparam>
/// <typeparam name="FieldType">In的字段类型</typeparam>
/// <param name="queryable">查询对象</param>
/// <param name="InFieldName">In的字段名称</param>
/// <param name="inValues">In的值的数组集合</param>
/// <returns>Queryable</returns>
public static Queryable <T> In <T, FieldType>(this Queryable <T> queryable, string InFieldName, params FieldType[] inValues)
{
var type = queryable.Type;
queryable.WhereIndex = queryable.WhereIndex + 100;
ResolveExpress re = new ResolveExpress(queryable.WhereIndex);
queryable.WhereValue.Add(string.Format(" AND {0} IN ({1})", InFieldName, inValues.ToJoinSqlInVal()));
return(queryable);
}
/// <summary>
/// 将Queryable转换T的集合
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="queryable">查询对象</param>
/// <returns>T的集合</returns>
public static List <T> ToList <T>(this Queryable <T> queryable)
{
StringBuilder sbSql = SqlSugarTool.GetQueryableSql <T>(queryable);
var reader = queryable.DB.GetReader(sbSql.ToString(), queryable.Params.ToArray());
var reval = SqlSugarTool.DataReaderToList <T>(typeof(T), reader, queryable.SelectValue.GetSelectFiles());
queryable = null;
sbSql = null;
return(reval);
}
/// <summary>
/// 返回序列中的第一个元素。
/// </summary>
/// <typeparam name="T">表实体类型</typeparam>
/// <param name="queryable">查询对象</param>
/// <returns>T</returns>
public static T First <T>(this Queryable <T> queryable)
{
if (queryable.OrderByValue.IsNullOrEmpty())
{
queryable.OrderByValue = "GETDATE()";
}
queryable.Skip(0);
queryable.Take(1);
var reval = queryable.ToList();
return(reval.First());
}
/// <summary>
/// 返回序列的唯一元素；如果该序列并非恰好包含一个元素，否则返回null。
/// </summary>
/// <typeparam name="T">表实体类型</typeparam>
/// <param name="queryable">查询对象</param>
/// <param name="expression">表达式条件</param>
/// <returns>T</returns>
public static T SingleOrDefault <T>(this Queryable <T> queryable, Expression <Func <T, bool> > expression)
{
var type = queryable.Type;
queryable.WhereIndex = queryable.WhereIndex + 100;
ResolveExpress re = new ResolveExpress(queryable.WhereIndex);
re.ResolveExpression(re, expression, queryable.DB);
queryable.WhereValue.Add(re.SqlWhere);
queryable.Params.AddRange(re.Paras);
return(queryable.ToList().SingleOrDefault());
}
/// <summary>
/// 确定序列是否包含任何元素。
/// </summary>
/// <typeparam name="T">表实体类型</typeparam>
/// <param name="queryable">查询对象</param>
/// <param name="expression">表达式条件</param>
/// <returns>count>0返回true</returns>
public static bool Any <T>(this Queryable <T> queryable, Expression <Func <T, bool> > expression)
{
var type = queryable.Type;
queryable.WhereIndex = queryable.WhereIndex + 100;
ResolveExpress re = new ResolveExpress(queryable.WhereIndex);
re.ResolveExpression(re, expression, queryable.DB);
queryable.WhereValue.Add(re.SqlWhere);
queryable.Params.AddRange(re.Paras);
return(queryable.Count() > 0);
}
/// <summary>
/// 条件筛选
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="queryable">查询对象</param>
/// <param name="whereString">Where后面的Sql条件语句 (例如： id=@id )</param>
/// <param name="whereObj"> 匿名参数 (例如：new{id=1,name="张三"})</param>
/// <returns>Queryable</returns>
public static Queryable <T> Where <T>(this Queryable <T> queryable, string whereString, object whereObj = null)
{
var type = queryable.Type;
string whereStr = string.Format(" AND {0} ", whereString);
queryable.WhereValue.Add(whereStr);
if (whereObj != null)
{
queryable.Params.AddRange(SqlSugarTool.GetParameters(whereObj));
}
return(queryable);
}
/// <summary>
/// 条件筛选
/// </summary>
/// <typeparam name="T">表实体类型</typeparam>
/// <typeparam name="T2">表实体类型</typeparam>
/// <typeparam name="T3">表实体类型</typeparam>
/// <typeparam name="T4">表实体类型</typeparam>
/// <typeparam name="T5">表实体类型</typeparam>
/// <param name="queryable">查询对象</param>
/// <param name="expression">表达式条件</param>
/// <returns>Queryable</returns>
public static Queryable <T> Where <T, T2, T3, T4, T5>(this Queryable <T> queryable, Expression <Func <T, T2, T3, T4, T5, bool> > expression)
{
var type = queryable.Type;
queryable.WhereIndex = queryable.WhereIndex + 100;
ResolveExpress re = new ResolveExpress(queryable.WhereIndex);
re.Type = ResolveExpressType.nT;
re.ResolveExpression(re, expression, queryable.DB);
queryable.Params.AddRange(re.Paras);
queryable.WhereValue.Add(re.SqlWhere);
return(queryable);
}
/// <summary>
/// 将Queryable转换为分页后的List<T>集合
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="queryable">查询对象</param>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">每页显示数量</param>
/// <returns>T的集合</returns>
public static List <T> ToPageList <T>(this Queryable <T> queryable, int pageIndex, int pageSize)
{
if (queryable.OrderByValue.IsNullOrEmpty())
{
throw new Exception("分页必需使用.Order排序");
}
if (pageIndex == 0)
{
pageIndex = 1;
}
queryable.Skip = (pageIndex - 1) * pageSize;
queryable.Take = pageSize;
return(queryable.ToList());
}
/// <summary>
/// 返回序列中的第一个元素,如果序列为NULL返回default(T)
/// </summary>
/// <typeparam name="T">表实体类型</typeparam>
/// <param name="queryable">查询对象</param>
/// <returns>T</returns>
public static T FirstOrDefault <T>(this Queryable <T> queryable)
{
if (queryable.OrderByValue.IsNullOrEmpty())
{
queryable.OrderByValue = "GETDATE()";
}
queryable.Skip(0);
queryable.Take(1);
var reval = queryable.ToList();
if (reval == null || reval.Count == 0)
{
return(default(T));
}
return(reval.First());
}
/// <summary>
/// 获取最小值
/// </summary>
/// <typeparam name="TSource">实体类型</typeparam>
/// <typeparam name="TResult">返回类型</typeparam>
/// <param name="queryable">查询对象</param>
/// <param name="minField">列名</param>
/// <returns>TResult</returns>
public static TResult Min <TSource, TResult>(this Queryable <TSource> queryable, string minField)
{
StringBuilder sbSql = new StringBuilder();
string withNoLock = queryable.DB.IsNoLock ? "WITH(NOLOCK)" : null;
var tableName = queryable.TName;
if (queryable.TableName.IsValuable())
{
tableName = queryable.TableName;
}
sbSql.AppendFormat("SELECT MIN({3}) FROM {0} {1} WHERE 1=1 {2} {4} ", tableName.GetTranslationSqlName(), withNoLock, string.Join("", queryable.WhereValue), minField, queryable.GroupByValue.GetGroupBy());
var objValue = queryable.DB.GetScalar(sbSql.ToString(), queryable.Params.ToArray());
var reval = Convert.ChangeType(objValue, typeof(TResult));
return((TResult)reval);
}
/// <summary>
/// 获取序列总记录数
/// </summary>
/// <param name="queryable">查询对象</param>
/// <returns>int</returns>
public static int Count <T>(this Queryable <T> queryable)
{
StringBuilder sbSql = new StringBuilder();
string joinInfo = string.Join(" ", queryable.JoinTableValue);
string withNoLock = queryable.DB.IsNoLock ? "WITH(NOLOCK)" : null;
var tableName = queryable.TName;
if (queryable.TableName.IsValuable())
{
tableName = queryable.TableName;
}
sbSql.AppendFormat("SELECT COUNT({3}) FROM {0} {1} " + joinInfo + " WHERE 1=1 {2} {4} ", tableName.GetTranslationSqlName(), withNoLock, string.Join("", queryable.WhereValue), "1", queryable.GroupByValue.GetGroupBy());
var count = queryable.DB.GetInt(sbSql.ToString(), queryable.Params.ToArray());
return(count);
}
/// <summary>
/// 将源数据对象转换到新对象中
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <typeparam name="TResult"></typeparam>
/// <param name="queryable"></param>
/// <param name="expression"></param>
/// <returns></returns>
public static SqlSugar.Queryable <TResult> Select <TSource, TResult>(this SqlSugar.Queryable <TSource> queryable, string select)
{
var type = typeof(TSource);
SqlSugar.Queryable <TResult> reval = new Queryable <TResult>()
{
DB = queryable.DB,
OrderBy = queryable.OrderBy,
Params = queryable.Params,
Skip = queryable.Skip,
Take = queryable.Take,
Where = queryable.Where,
TableName = type.Name,
GroupBy = queryable.GroupBy,
Select = select
};
return(reval);
}
/// <summary>
/// 联表查询根据字符串
/// </summary>
/// <typeparam name="T">第一个表的对象</typeparam>
/// <param name="queryable">查询对象</param>
/// <param name="tableName">表名（可是以表或也可以是SQL语句加括号）</param>
/// <param name="shortName">表名简写</param>
/// <param name="onWhere">on后面的条件</param>
/// <param name="whereObj">匿名参数(例如:new{id=1,name="张三"})</param>
/// <param name="type">Join的类型</param>
/// <returns>Queryable</returns>
public static Queryable <T> JoinTable <T>(this Queryable <T> queryable, string tableName, string shortName, string onWhere, object whereObj, JoinType type = JoinType.LEFT)
{
queryable.WhereIndex = queryable.WhereIndex + 100;;
string joinType = type.ToString();
string joinStr = string.Format(" {0} JOIN {1} {2} ON {3} ",
/*0*/ joinType,
/*1*/ tableName,
/*2*/ shortName,
/*3*/ onWhere
);
queryable.JoinTableValue.Add(joinStr);
if (whereObj != null)
{
queryable.Params.AddRange(SqlSugarTool.GetParameters(whereObj));
}
return(queryable);
}
/// <summary>
/// 联表查询
/// </summary>
/// <typeparam name="T">第一个表的对象</typeparam>
/// <typeparam name="T2">联接表的对象</typeparam>
/// <typeparam name="T3">联接表的对象</typeparam>
/// <param name="queryable">查询对象</param>
/// <param name="expression">条件表达式</param>
/// <param name="type">Join的类型</param>
/// <returns>Queryable</returns>
public static Queryable <T> JoinTable <T, T2, T3>(this Queryable <T> queryable, Expression <Func <T, T2, T3, object> > expression, JoinType type = JoinType.LEFT)
{
queryable.DB.InitAttributes <T3>();
ResolveExpress re = new ResolveExpress();
queryable.WhereIndex = queryable.WhereIndex + 100;
re.Type = ResolveExpressType.nT;
var exLeftStr = Regex.Match(expression.ToString(), @"\((.+?)\).+").Groups[1].Value;
var exLeftArray = exLeftStr.Split(',');
var shortName1 = exLeftArray[1];
var shortName2 = exLeftArray[2];
re.ResolveExpression(re, expression, queryable.DB);
string joinTypeName = type.ToString();
string joinTableName = null;
if (queryable.DB._mappingTableList.IsValuable())
{
//别名表
if (queryable.DB._mappingTableList.IsValuable())
{
string name = typeof(T3).Name;
if (queryable.DB._mappingTableList.Any(it => it.Key == name))
{
joinTableName = queryable.DB._mappingTableList.First(it => it.Key == name).Value;
}
}
}
if (joinTableName.IsNullOrEmpty())
{
joinTableName = typeof(T3).Name;
}
string joinStr = string.Format(" {0} JOIN {1} {2} ON {3} ",
/*0*/ queryable.JoinTableValue.Count == 0 ? (" " + shortName1 + " " + joinTypeName) : joinTypeName.ToString(),
/*1*/ joinTableName.GetTranslationSqlName(),
/*2*/ shortName2,
/*3*/ re.SqlWhere.Trim().TrimStart('A').TrimStart('N').TrimStart('D')
);
queryable.JoinTableValue.Add(joinStr);
queryable.Params.AddRange(re.Paras);
return(queryable);
}
/// <summary>
/// 将源数据对象转换到新对象中
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <typeparam name="TResult"></typeparam>
/// <param name="queryable"></param>
/// <param name="expression"></param>
/// <returns></returns>
public static SqlSugar.Queryable <TResult> Select <TSource, TResult>(this SqlSugar.Queryable <TSource> queryable, Expression <Func <TSource, TResult> > expression)
{
var type = typeof(TSource);
SqlSugar.Queryable <TResult> reval = new Queryable <TResult>()
{
DB = queryable.DB,
OrderBy = queryable.OrderBy,
Params = queryable.Params,
Skip = queryable.Skip,
Take = queryable.Take,
Where = queryable.Where,
TableName = type.Name,
GroupBy = queryable.GroupBy,
Select = Regex.Match(expression.ToString(), @"(?<=\{).*?(?=\})").Value
};
reval.Select = Regex.Replace(reval.Select, @"(?<=\=).*?\.", "");
return(reval);
}
/// <summary>
/// 将源数据对象转换到新对象中
/// </summary>
/// <typeparam name="T">原数据实体类型</typeparam>
/// <typeparam name="T2">原数据实体类型</typeparam>
/// <typeparam name="T3">原数据实体类型</typeparam>
/// <typeparam name="T4">原数据实体类型</typeparam>
/// <typeparam name="T5">原数据实体类型</typeparam>
/// <typeparam name="TResult">返回值的新实体类型</typeparam>
/// <param name="queryable">查询对象</param>
/// <param name="expression">给新实体赋值的表达式</param>
/// <returns>Queryable</returns>
public static Queryable <TResult> Select <T, T2, T3, T4, T5, TResult>(this Queryable <T> queryable, Expression <Func <T, T2, T3, T4, T5, TResult> > expression)
{
var expStr = expression.ToString();
var type = typeof(T);
Queryable <TResult> reval = new Queryable <TResult>()
{
DB = queryable.DB,
OrderByValue = queryable.OrderByValue,
Params = queryable.Params,
Skip = queryable.Skip,
Take = queryable.Take,
WhereValue = queryable.WhereValue,
TableName = queryable.TableName,
GroupByValue = queryable.GroupByValue,
JoinTableValue = queryable.JoinTableValue
};
ResolveSelect.GetResult <TResult>(expStr, reval, expression);
return(reval);
}
/// <summary>
/// 根据主键查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="queryable"></param>
/// <param name="pkValues">主键集合</param>
/// <returns></returns>
public static Queryable <T> In <T>(this Queryable <T> queryable, params object[] pkValues)
{
Check.Exception(pkValues == null || pkValues.Length == 0, "In.pkValues的Count不能为0");
var type = pkValues[0].GetType();
if (type != SqlSugarTool.IntType && type != SqlSugarTool.GuidType && type.FullName.IsCollectionsList())
{
var newList = new List <object>();
foreach (var item in (IEnumerable)pkValues[0])
{
newList.Add(item);
}
pkValues = newList.ToArray();
Check.Exception(pkValues == null || pkValues.Length == 0, "In.pkValues的Count不能为0");
}
var pkName = SqlSugarTool.GetPrimaryKeyByTableName(queryable.DB, queryable.TableName);
queryable.OrderByValue = null;
Check.ArgumentNullException(pkName, "In(params object[]PkValue)查询表中不存在主键,请换In的其它重载方法。");
return(queryable.In <T, string>(pkName, pkValues.Select(it => it.ToString()).ToArray()));
}
/// <summary>
/// 将源数据对象转换到新对象中
/// </summary>
/// <typeparam name="TSource">原数据实体类型</typeparam>
/// <typeparam name="TResult">返回值的新实体类型</typeparam>
/// <param name="queryable">查询对象</param>
/// <param name="select">查询字符串（例如 id,name）</param>
/// <returns></returns>
public static Queryable <TResult> Select <TSource, TResult>(this Queryable <TSource> queryable, string select)
{
var type = typeof(TSource);
Queryable <TResult> reval = new Queryable <TResult>()
{
DB = queryable.DB,
OrderByValue = queryable.OrderByValue,
Params = queryable.Params,
Skip = queryable.Skip,
Take = queryable.Take,
WhereValue = queryable.WhereValue,
TableName = queryable.TableName,
GroupByValue = queryable.GroupByValue,
SelectValue = select
};
if (queryable.JoinTableValue.IsValuable())
{
reval.JoinTableValue = queryable.JoinTableValue;
}
return(reval);
}
/// <summary>
/// 创建拉姆达查询对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public Queryable <T> Queryable <T>() where T : new()
{
var queryable = new Queryable <T>()
{
DB = this
};
//别名表
if (_mappingTableList.IsValuable())
{
string name = typeof(T).Name;
if (_mappingTableList.Any(it => it.Key == name))
{
queryable.TableName = _mappingTableList.First(it => it.Key == name).Value;
}
}
//全局过滤器
if (CurrentFilterKey.IsValuable())
{
if (_filterFuns.IsValuable() && _filterFuns.ContainsKey(CurrentFilterKey))
{
var filterInfo = _filterFuns[CurrentFilterKey];
var filterValue = filterInfo();
string whereStr = string.Format(" AND {0} ", filterValue.Key);
queryable.Where.Add(whereStr);
if (filterValue.Value != null)
{
queryable.Params.AddRange(SqlSugarTool.GetParameters(filterValue.Value));
}
}
if (_filterColumns.IsValuable() && _filterColumns.ContainsKey(CurrentFilterKey))
{
var columns = _filterColumns[CurrentFilterKey];
queryable.Select = string.Join(",", columns);
}
}
return(queryable);
}
/// <summary>
/// 将Queryable转换为Dynamic
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="queryable">查询对象</param>
/// <returns>dynamic</returns>
public static dynamic ToDynamic <T>(this Queryable <T> queryable)
{
return(JsonConverter.ConvertJson(ToJson <T>(queryable)));
}
/// <summary>
/// 将Queryable转换为Json
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="queryable">查询对象</param>
/// <returns>json字符串</returns>
public static string ToJson <T>(this Queryable <T> queryable)
{
return(JsonConverter.DataTableToJson(ToDataTable <T>(queryable), queryable.DB.SerializerDateFormat));
}
/// <summary>
/// 将select的字段映射到T对象
/// </summary>
/// <typeparam name="T">数据实体类型</typeparam>
/// <typeparam name="Result">数据实体类型</typeparam>
/// <param name="queryable">查询对象</param>
/// <param name="select">查询字符串（例如 id,name）</param>
/// <returns></returns>
public static Queryable <Result> Select <T, Result>(this Queryable <Result> queryable, string select)
{
queryable.SelectValue = select;
return(queryable);
}
/// <summary>
/// 确定序列是否包含任何元素
/// </summary>
/// <typeparam name="T">表实体类型</typeparam>
/// <param name="queryable">查询对象</param>
/// <returns>count>0返回true</returns>
public static bool Any <T>(this Queryable <T> queryable)
{
return(queryable.Count() > 0);
}
