/// <summary>
/// 执行 SQL 语句，并返回两个实体集合
/// </summary>
/// <param name="query1">SQL 命令</param>
/// <param name="query2">SQL 命令</param>
/// <param name="query3">SQL 命令</param>
public virtual Tuple <List <T1>, List <T2>, List <T3> > ExecuteMultiple <T1, T2, T3>(IDbQueryable <T1> query1, IDbQueryable <T2> query2, IDbQueryable <T3> query3)
{
List <Command> sqlList = query1.Provider.Resolve(new List <object> {
query1, query2, query3
});
var result = this.DoExecute <Tuple <List <T1>, List <T2>, List <T3>, List <None>, List <None>, List <None>, List <None> > >(sqlList,
p => this.ExecuteMultiple <T1, T2, T3, None, None, None, None>(p, sqlList.ToList(x => x as IMapping)));
return(new Tuple <List <T1>, List <T2>, List <T3> >(result.Item1, result.Item2, result.Item3));
}
/// <summary>
/// 基于匹配键对两个序列的元素进行关联。使用默认的相等比较器对键进行比较
/// </summary>
/// <typeparam name="TOuter">第一个序列中的元素的类型</typeparam>
/// <typeparam name="TInner">第二个序列中的元素的类型</typeparam>
/// <typeparam name="TKey">键选择器函数返回的键的类型</typeparam>
/// <typeparam name="TResult">结果元素的类型</typeparam>
/// <param name="outer">要联接的第一个序列</param>
/// <param name="inner">要与第一个序列联接的序列</param>
/// <param name="outerKeySelector">用于从第一个序列的每个元素提取联接键的函数</param>
/// <param name="innerKeySelector">用于从第二个序列的每个元素提取联接键的函数</param>
/// <param name="resultSelector">用于从两个匹配元素创建结果元素的函数</param>
/// <returns></returns>
public static IDbQueryable <TResult> Join <TOuter, TInner, TKey, TResult>(this IDbQueryable <TOuter> outer, IDbQueryable <TInner> inner, Expression <Func <TOuter, TKey> > outerKeySelector, Expression <Func <TInner, TKey> > innerKeySelector, Expression <Func <TOuter, TInner, TResult> > resultSelector)
{
return(outer.CreateQuery <TResult>(new DbExpression(DbExpressionType.Join, new Expression[] {
Expression.Constant(inner),
outerKeySelector,
innerKeySelector,
resultSelector
})));
}
/// <summary>
/// 强制使用嵌套查询
/// </summary>
/// <typeparam name="TSource">source 的元素类型</typeparam>
/// <typeparam name="TResult">keySelector 的元素类型</typeparam>
/// <param name="source">查询序列</param>
/// <param name="keySelector">用于提取每个元素的键的函数</param>
/// <returns></returns>
public static IDbQueryable <TResult> AsSubquery <TSource, TResult>(this IDbQueryable <TSource> source, Expression <Func <TSource, TResult> > keySelector)
{
return(source
.CreateQuery <TResult>(DbExpressionType.AsSubquery)
.CreateQuery <TResult>(DbExpressionType.Select, keySelector));
}
/// <summary>
/// 返回序列中满足指定条件的第一个元素，如果未找到这样的元素，则返回默认值
/// </summary>
/// <typeparam name="TSource">source 的元素类型</typeparam>
/// <param name="source">查询序列</param>
/// <param name="predicate">用于测试每个元素是否满足条件的函数</param>
/// <returns></returns>
public static TSource FirstOrDefault <TSource>(this IDbQueryable <TSource> source, Expression <Func <TSource, bool> > predicate = null)
{
IDbQueryable <TSource> query = source.CreateQuery <TSource>(DbExpressionType.FirstOrDefault, predicate);
return(query.Execute <TSource>());
}
/// <summary>
/// 指示查询应该包含外键
/// </summary>
/// <typeparam name="TSource">主表类型</typeparam>
/// <typeparam name="TProperty">外键类型</typeparam>
/// <param name="source"></param>
/// <param name="path">要在查询结果中返回的相关对象列表</param>
/// <returns></returns>
public static IDbQueryable <TSource> Include <TSource, TProperty>(this IDbQueryable <TSource> source, Expression <Func <TSource, TProperty> > path)
{
return(source.CreateQuery <TSource>(DbExpressionType.Include, path));
}
/// <typeparam name="TSource">source 的元素类型</typeparam>
/// <typeparam name="TKey">keySelector 返回的键的类型</typeparam>
/// <typeparam name="TElement">每个 <see cref="IGrouping{TKey, TElement}"/> 中的元素的类型</typeparam>
/// <param name="source">查询序列</param>
/// <param name="keySelector">用于提取每个元素的键的函数</param>
/// <param name="elementSelector">用于将每个源元素映射到 <see cref="IGrouping{TKey, TElement}"/> 中的元素的函数</param>
/// <returns></returns>
public static IDbQueryable <IGrouping <TKey, TElement> > GroupBy <TSource, TKey, TElement>(this IDbQueryable <TSource> source, Expression <Func <TSource, TKey> > keySelector, Expression <Func <TSource, TElement> > elementSelector)
{
return(source.CreateQuery <IGrouping <TKey, TElement> >(new DbExpression(DbExpressionType.GroupBy, new Expression[] { keySelector, elementSelector })));
}
/// <summary>
/// 返回指定序列的元素；如果序列为空，则返回单一实例集合中的类型参数的默认值
/// </summary>
/// <typeparam name="TSource">source 的元素类型</typeparam>
/// <param name="source">查询序列</param>
/// <param name="isRightOuterJoin">是否右关联</param>
/// <returns></returns>
public static IDbQueryable <TSource> DefaultIfEmpty <TSource>(this IDbQueryable <TSource> source, bool isRightOuterJoin)
{
return(source.CreateQuery <TSource>(DbExpressionType.DefaultIfEmpty));
}
/// <summary>
/// 从 <see cref="IDbQueryable{TSource}"/> 创建 <see cref="DataSet"/>
/// </summary>
/// <typeparam name="TSource">source 的元素类型</typeparam>
/// <param name="source">查询序列</param>
/// <returns></returns>
public static DataSet ToDataSet <TSource>(this IDbQueryable <TSource> source)
{
return(source.Execute <DataSet>());
}
/// <summary>
/// 确定序列是否包含任何元素
/// </summary>
/// <typeparam name="TSource">source 的元素类型</typeparam>
/// <param name="source">查询序列</param>
/// <returns>如果源序列中存在元素通过了指定谓词中的测试，则为 true；否则为 false</returns>
public static async Task <bool> AnyAsync <TSource>(this IDbQueryable <TSource> source)
{
return(await source.AnyAsync(null));
}
/// <summary>
/// 创建 SQL 命令
/// </summary>
/// <param name="dbQueryable">查询 语句</param>
public RawCommand Resolve <T>(IDbQueryable <T> dbQueryable)
{
return(this.Resolve(dbQueryable, 0, true, null));
}
// LEFT OR INNER JOIN
private void AppendLfInJoin(ISqlBuilder jf, ISqlBuilder wf, ISqlBuilder on, DbExpression exp, TableAliasCache aliases)
{
bool useExists = false;
LambdaExpression left = exp.Expressions[1] as LambdaExpression;
LambdaExpression right = exp.Expressions[2] as LambdaExpression;
NewExpression body1 = left.Body as NewExpression;
NewExpression body2 = right.Body as NewExpression;
// t0(t1)
string alias = body1 == null
? aliases.GetTableAlias(exp.Expressions[2])
: aliases.GetTableAlias(right.Parameters[0]);//(body2.Arguments[0]);
IDbQueryable sQuery = (IDbQueryable)((exp.Expressions[0] as ConstantExpression).Value);
if (sQuery.DbExpressions.Count == 1 && sQuery.DbExpressions[0].DbExpressionType == DbExpressionType.GetTable)
{
if (!_appendedKeyword)
{
jf.AppendNewLine();
jf.Append(_keywordName);
_appendedKeyword = true;
}
else
{
jf.Append(',');
jf.AppendNewLine();
jf.Append(" ");
}
jf.Append(' ');
Type type = exp.Expressions[0].Type.GetGenericArguments()[0];
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(type);
jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
jf.Append(' ');
jf.Append(alias);
if (on.Length > 0)
{
on.Append(" AND ");
}
}
else
{
useExists = true;
if ((wf != null && wf.Length > 0) || (on != null && on.Length > 0))
{
if (wf != null && wf.Length > 0)
{
wf.Append("AND ");
}
wf.AppendNewLine();
}
wf.Append("EXISTS(");
wf.Indent += 1;
wf.AppendNewLine();
wf.Append("SELECT 1 FROM(");
var cmd = sQuery.Resolve(wf.Indent + 1, false, wf.Parameters);
wf.Append(cmd.CommandText);
wf.AppendNewLine();
wf.Append(')');
wf.Append(' ');
wf.Append(alias);
wf.Append(" WHERE ");
}
ISqlBuilder tbuilder = useExists ? wf : on;
if (body1 == null)
{
tbuilder.AppendMember(aliases, left.Body.ReduceUnary());
tbuilder.Append(" = ");
tbuilder.AppendMember(aliases, right.Body.ReduceUnary());
}
else
{
for (int index = 0; index < body1.Arguments.Count; ++index)
{
tbuilder.AppendMember(aliases, body1.Arguments[index]);
tbuilder.Append(" = ");
tbuilder.AppendMember(aliases, body2.Arguments[index]);
if (index < body1.Arguments.Count - 1)
{
tbuilder.Append(" AND ");
}
}
}
if (useExists)
{
wf.Indent -= 1;
wf.AppendNewLine();
wf.Append(')');
}
}
/// <summary>
/// 解析 SQL 命令
/// <para>
/// 返回的已经解析语义中执行批次用 null 分开
/// </para>
/// </summary>
/// <param name="dbQueryables">查询语句</param>
/// <returns></returns>
public virtual List <RawCommand> Resolve(List <object> dbQueryables)
{
List <RawCommand> sqlList = new List <RawCommand>();
ResolveToken token = null;
foreach (var obj in dbQueryables)
{
if (obj == null)
{
continue;
}
if (obj is IDbQueryable)
{
IDbQueryable dbQueryable = (IDbQueryable)obj;
dbQueryable.Parameterized = true;
if (token == null)
{
token = new ResolveToken();
}
if (token.Parameters == null)
{
token.Parameters = new List <IDbDataParameter>(8);
}
var cmd2 = dbQueryable.Resolve(0, true, token);
sqlList.Add(cmd2);
if (cmd2.Parameters != null && cmd2.Parameters.Count > 1000)
{
// 1000个参数，就要重新分批
sqlList.Add(null);
token = new ResolveToken();
token.Parameters = new List <IDbDataParameter>(8);
}
}
else if (obj is RawSql)
{
RawSql rawSql = (RawSql)obj;
if (token == null)
{
token = new ResolveToken();
}
if (token.Parameters == null)
{
token.Parameters = new List <IDbDataParameter>(8);
}
// 解析参数
object[] args = null;
if (rawSql.Parameters != null)
{
args = rawSql.Parameters.Select(x => this.DbValue.GetSqlValue(x, token)).ToArray();
}
string sql = rawSql.CommandText;
if (args != null && args.Length > 0)
{
sql = string.Format(sql, args);
}
var cmd2 = new RawCommand(sql, token.Parameters, CommandType.Text);
sqlList.Add(cmd2);
if (cmd2.Parameters != null && cmd2.Parameters.Count > 1000)
{
// 1000个参数，就要重新分批
sqlList.Add(null);
token = new ResolveToken();
token.Parameters = new List <IDbDataParameter>(8);
}
}
else if (obj is string)
{
string sql = obj.ToString();
sqlList.Add(new RawCommand(sql));
}
else
{
// 解析批量插入操作
List <IDbQueryable> bulkList = obj as List <IDbQueryable>;
if (bulkList != null && bulkList.Count > 0)
{
this.ResolveBulk(sqlList, bulkList);
}
}
}
return(sqlList);
}
// 创建 SELECT 命令
Command ParseSelectCommandImpl <T>(DbQueryableInfo_Select <T> sQueryInfo, int indent, bool isOuter, ResolveToken token)
{
// 说明：
// 1.OFFSET 前必须要有 'ORDER BY'，即 'Skip' 子句前必须使用 'OrderBy' 子句
// 2.在有统计函数的<MAX,MIN...>情况下，如果有 'Distinct' 'GroupBy' 'Skip' 'Take' 子句，则需要使用嵌套查询
// 3.'Any' 子句将翻译成 IF EXISTS...
// 4.分组再分页时需要使用嵌套查询，此时子查询不需要 'OrderBy' 子句，但最外层则需要
// 5.'Skip' 'Take' 子句视为语义结束符，在其之后的子句将使用嵌套查询
// 6.导航属性中有 1:n 关系的，需要使用嵌套查询，否则分页查询会有问题
// 8.如果只有 Skip 没有 Take，则使用 Row_Number() Over()分页语法，其它使用 LIMIT ## OFFSET 语法
// 导航属性中有1:n关系，只统计主表
// 例：AccountList = a.Client.AccountList,
DbQueryableInfo_Select <T> subQuery = sQueryInfo.SubQueryInfo as DbQueryableInfo_Select <T>;
if (sQueryInfo.HasMany && subQuery != null && subQuery.StatisExpression != null)
{
sQueryInfo = subQuery;
}
bool useStatis = sQueryInfo.StatisExpression != null;
// 没有统计函数或者使用 'Skip' 子句，则解析OrderBy
// 导航属性如果使用嵌套，除非有 TOP 或者 OFFSET 子句，否则不能用ORDER BY
string alias0 = token != null && !string.IsNullOrEmpty(token.TableAliasName) ? (token.TableAliasName + "0") : "t0";
bool useSubQuery = sQueryInfo.HasDistinct || sQueryInfo.GroupByExpression != null || sQueryInfo.Skip > 0 || sQueryInfo.Take > 0;
bool useOrderBy = (!useStatis || sQueryInfo.Skip > 0) && !sQueryInfo.HasAny && (!sQueryInfo.SubQueryByMany || (sQueryInfo.Skip > 0 || sQueryInfo.Take > 0));
IDbQueryable dbQueryable = sQueryInfo.SourceQuery;
TableAliasCache aliases = this.PrepareAlias <T>(sQueryInfo, token);
MappingCommand cmd = new MappingCommand(this, aliases, token)
{
HasMany = sQueryInfo.HasMany
};
ISqlBuilder jf = cmd.JoinFragment;
ISqlBuilder wf = cmd.WhereFragment;
ISqlBuilder sf = null;
jf.Indent = indent;
#region 嵌套查询
if (useStatis && useSubQuery)
{
// SELECT
jf.Append("SELECT ");
jf.AppendNewLine();
// SELECT COUNT(1)
var visitor2 = new StatisExpressionVisitor(this, aliases, sQueryInfo.StatisExpression, sQueryInfo.GroupByExpression, alias0);
visitor2.Write(jf);
cmd.AddNavMembers(visitor2.NavMembers);
// SELECT COUNT(1) FROM
jf.AppendNewLine();
jf.Append("FROM ( ");
indent += 1;
jf.Indent = indent;
}
#endregion
#region 择子句
// SELECT 子句
if (jf.Indent > 0)
{
jf.AppendNewLine();
}
jf.Append("SELECT ");
if (sQueryInfo.HasAny)
{
jf.Append("CASE WHEN COUNT(1) = 1 THEN 1 ELSE 0 END FROM (");
indent += 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append("SELECT 1 ");
}
if (useStatis && !useSubQuery)
{
// 如果有统计函数，并且不是嵌套的话，则直接使用SELECT <MAX,MIN...>，不需要解析选择的字段
jf.AppendNewLine();
var visitor2 = new StatisExpressionVisitor(this, aliases, sQueryInfo.StatisExpression, sQueryInfo.GroupByExpression);
visitor2.Write(jf);
cmd.AddNavMembers(visitor2.NavMembers);
}
else
{
// DISTINCT 子句
if (sQueryInfo.HasDistinct)
{
jf.Append("DISTINCT ");
}
#region 择字段
if (!sQueryInfo.HasAny)
{
// SELECT 范围
var visitor2 = new ColumnExpressionVisitor(this, aliases, sQueryInfo);
if (sQueryInfo.Skip > 0 && sQueryInfo.Take == 0)
{
sf = this.CreateSqlBuilder(token);
sf.Indent = jf.Indent + 1;
visitor2.Write(sf);
}
else
{
visitor2.Write(jf);
}
cmd.PickColumns = visitor2.PickColumns;
cmd.PickColumnText = visitor2.PickColumnText;
cmd.Navigations = visitor2.Navigations;
cmd.AddNavMembers(visitor2.NavMembers);
if (sf != null)
{
// 第一层嵌套
int index = 0;
jf.AppendNewLine();
foreach (var column in cmd.PickColumns)
{
jf.AppendMember(alias0, column.Name);
jf.AppendAs(column.NewName);
index += 1;
if (index < cmd.PickColumns.Count)
{
jf.Append(',');
jf.AppendNewLine();
}
}
jf.AppendNewLine();
jf.Append("FROM(");
// 第二层嵌套
indent += 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append("SELECT");
jf.Append(sf);
jf.Append(',');
jf.AppendNewLine();
if (sQueryInfo.OrderBys.Count == 0)
{
throw new XFrameworkException("The method 'OrderBy' must be called before 'Skip'.");
}
jf.Append("ROW_NUMBER() OVER(");
var visitor3 = new OrderByExpressionVisitor(this, aliases, sQueryInfo.OrderBys, sQueryInfo.GroupByExpression);
visitor3.Write(jf, false);
cmd.AddNavMembers(visitor3.NavMembers);
jf.Append(") Row_Number0");
}
}
#endregion
}
#endregion
#region 顺序解析
// FROM 子句
jf.AppendNewLine();
jf.Append("FROM ");
if (sQueryInfo.SubQueryInfo != null)
{
// 子查询
jf.Append("(");
var cmd2 = this.ParseSelectCommandImpl <T>(sQueryInfo.SubQueryInfo as DbQueryableInfo_Select <T>, indent + 1, false, token);
jf.Append(cmd2.CommandText);
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias0);
jf.Append(' ');
}
else
{
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(sQueryInfo.FromType);
jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
jf.Append(' ');
jf.Append(alias0);
jf.Append(' ');
}
// LEFT<INNER> JOIN 子句
ExpressionVisitorBase visitor = new JoinExpressionVisitor(this, aliases, sQueryInfo.Joins);
visitor.Write(jf);
wf.Indent = jf.Indent;
// WHERE 子句
visitor = new WhereExpressionVisitor(this, aliases, sQueryInfo.WhereExpression);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
// GROUP BY 子句
visitor = new GroupByExpressionVisitor(this, aliases, sQueryInfo.GroupByExpression);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
// HAVING 子句
visitor = new HavingExpressionVisitor(this, aliases, sQueryInfo.HavingExpression, sQueryInfo.GroupByExpression);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
// ORDER 子句
if (sQueryInfo.OrderBys.Count > 0 && useOrderBy)// && !groupByPaging)
{
visitor = new OrderByExpressionVisitor(this, aliases, sQueryInfo.OrderBys, sQueryInfo.GroupByExpression);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
}
#endregion
#region 分页查询
// LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。
// LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。
// 初始记录行的偏移量是 0(而不是 1)： 为了与 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。
// Limit n,-1 语法不支持，使用ROW_Number()语法代替
if (sQueryInfo.Take > 0)
{
wf.AppendNewLine().AppendFormat("LIMIT {0}", this.DbValue.GetSqlValue(sQueryInfo.Take, token));
wf.AppendFormat(" OFFSET {0}", this.DbValue.GetSqlValue(sQueryInfo.Skip, token));
}
#endregion
#region 嵌套查询
if (useStatis && useSubQuery)
{
cmd.CombineFragments();
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias0);
}
#endregion
#region 嵌套导航
// TODO Include 从表，没分页，OrderBy 报错
if (sQueryInfo.HasMany && subQuery != null && subQuery.OrderBys.Count > 0 && subQuery.StatisExpression == null && !(subQuery.Skip > 0 || subQuery.Take > 0))
{
cmd.CombineFragments();
visitor = new OrderByExpressionVisitor(this, aliases, subQuery.OrderBys);//, null, "t0");
visitor.Write(jf);
}
#endregion
#region 并集查询
// UNION 子句
if (sQueryInfo.Unions != null && sQueryInfo.Unions.Count > 0)
{
cmd.CombineFragments();
for (int index = 0; index < sQueryInfo.Unions.Count; index++)
{
jf.AppendNewLine();
jf.Append("UNION ALL");
if (indent == 0)
{
jf.AppendNewLine();
}
Command cmd2 = this.ParseSelectCommandImpl <T>(sQueryInfo.Unions[index] as DbQueryableInfo_Select <T>, indent, isOuter, token);
jf.Append(cmd2.CommandText);
}
}
#endregion
#region 分页查询
if (sf != null)
{
// 合并 WHERE
cmd.CombineFragments();
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias0);
jf.AppendNewLine();
jf.Append("WHERE ");
if (sQueryInfo.Skip > 0)
{
jf.AppendMember(alias0, "Row_Number0");
jf.Append(" > ");
jf.Append(this.DbValue.GetSqlValue(sQueryInfo.Skip, token));
}
}
#endregion
#region Any 子句
// 'Any' 子句
if (sQueryInfo.HasAny)
{
// 产生 WHERE 子句
cmd.CombineFragments();
// 如果没有分页，则显式指定只查一笔记录
if (sQueryInfo.Take == 0 && sQueryInfo.Skip == 0)
{
jf.AppendNewLine();
jf.Append("LIMIT 0,1");
}
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias0);
}
#endregion
return(cmd);
}
/// <summary>
/// 异步执行 SQL 语句，并返回两个实体集合
/// </summary>
/// <param name="query1">SQL 命令</param>
/// <param name="query2">SQL 命令</param>
/// <param name="query3">SQL 命令</param>
public virtual async Task <Tuple <List <T1>, List <T2>, List <T3> > > ExecuteAsync <T1, T2, T3>(IDbQueryable <T1> query1, IDbQueryable <T2> query2, IDbQueryable <T3> query3)
{
List <DbRawCommand> sqlList = query1.Provider.Translate(new List <object> {
query1, query2, query3
});
var result = await this.DoExecuteAsync <Tuple <List <T1>, List <T2>, List <T3>, List <None>, List <None>, List <None>, List <None> > >(sqlList,
async p => await this.ExecuteAsync <T1, T2, T3, None, None, None, None>(p, sqlList.ToList(x => x as IMapDescriptor)));
return(new Tuple <List <T1>, List <T2>, List <T3> >(result.Item1, result.Item2, result.Item3));
}
/// <summary>
/// 从 <see cref="IDbQueryable{TSource}"/> 创建 <see cref="DataSet"/>
/// </summary>
/// <typeparam name="TSource">source 的元素类型</typeparam>
/// <param name="source">查询序列</param>
/// <returns></returns>
public static async Task <DataSet> ToDataSetAsync <TSource>(this IDbQueryable <TSource> source)
{
return(await source.ExecuteAsync <DataSet>());
}
/// <summary>
/// 确定序列是否包含任何元素
/// </summary>
/// <typeparam name="TSource">source 的元素类型</typeparam>
/// <param name="source">查询序列</param>
/// <returns></returns>
/// <param name="predicate">用于测试每个元素是否满足条件的函数</param>
/// <returns>如果源序列中存在元素通过了指定谓词中的测试，则为 true；否则为 false</returns>
public static async Task <bool> AnyAsync <TSource>(this IDbQueryable <TSource> source, Expression <Func <TSource, bool> > predicate)
{
IDbQueryable <bool> query = source.CreateQuery <bool>(DbExpressionType.Any, predicate);
return(await query.ExecuteAsync <bool>());
}
/// <summary>
/// 根据指定的键选择器函数对序列中的元素进行分组，并且从每个组及其键中创建结果值
/// </summary>
/// <typeparam name="TSource">source 的元素类型</typeparam>
/// <typeparam name="TKey">keySelector 返回的键的类型</typeparam>
/// <param name="source">查询序列</param>
/// <param name="keySelector">用于提取每个元素的键的函数</param>
/// <returns></returns>
public static IDbQueryable <IGrouping <TKey, TSource> > GroupBy <TSource, TKey>(this IDbQueryable <TSource> source, Expression <Func <TSource, TKey> > keySelector)
{
return(source.CreateQuery <IGrouping <TKey, TSource> >(new DbExpression(DbExpressionType.GroupBy, keySelector)));
}
/// <summary>
/// 返回序列中的元素数量
/// </summary>
/// <typeparam name="TSource">source 的元素类型</typeparam>
/// <param name="source">查询序列</param>
/// <returns></returns>
public static async Task <int> CountAsync <TSource>(this IDbQueryable <TSource> source)
{
int num = await source.CountAsync(null);
return(num);
}
/// <summary>
/// 返回指定序列的元素；如果序列为空，则返回单一实例集合中的类型参数的默认值
/// </summary>
/// <typeparam name="TSource">source 的元素类型</typeparam>
/// <param name="source">查询序列</param>
/// <returns></returns>
public static IDbQueryable <TSource> DefaultIfEmpty <TSource>(this IDbQueryable <TSource> source)
{
return(source.CreateQuery <TSource>(DbExpressionType.DefaultIfEmpty));
}
/// <summary>
/// 返回指定序列中满足条件的元素数量
/// </summary>
/// <typeparam name="TSource">source 的元素类型</typeparam>
/// <param name="source">查询序列</param>
/// <param name="predicate">用于测试每个元素是否满足条件的函数</param>
/// <returns></returns>
public static async Task <int> CountAsync <TSource>(this IDbQueryable <TSource> source, Expression <Func <TSource, bool> > predicate)
{
IDbQueryable <int> query = source.CreateQuery <int>(DbExpressionType.Count, predicate);
return(await query.ExecuteAsync <int>());
}
/// <summary>
/// 通过使用默认的相等比较器对值进行比较返回序列中的非重复元素
/// </summary>
/// <typeparam name="TSource">source 的元素类型</typeparam>
/// <param name="source">查询序列</param>
/// <returns></returns>
public static IDbQueryable <TSource> Distinct <TSource>(this IDbQueryable <TSource> source)
{
return(source.CreateQuery <TSource>(DbExpressionType.Distinct));
}
/// <summary>
/// 从 <see cref="IDbQueryable{TSource}"/> 创建一个数组
/// </summary>
/// <typeparam name="TSource">source 的元素类型</typeparam>
/// <param name="source">查询序列</param>
/// <returns></returns>
public static async Task <TSource[]> ToArrayAsync <TSource>(this IDbQueryable <TSource> source)
{
IList <TSource> listAsync = await source.ToListAsync <TSource>();
return(((List <TSource>)listAsync).ToArray());
}
/// <summary>
/// 强制使用嵌套查询
/// </summary>
/// <typeparam name="TSource">source 的元素类型</typeparam>
/// <param name="source">查询序列</param>
/// <returns></returns>
public static IDbQueryable <TSource> AsSubquery <TSource>(this IDbQueryable <TSource> source)
{
return(source.CreateQuery <TSource>(DbExpressionType.AsSubquery));
}
/// <summary>
/// 从 <see cref="IDbQueryable{TSource}"/> 创建一个 <see cref="List{TSource}"/>
/// </summary>
/// <typeparam name="TSource">source 的元素类型</typeparam>
/// <param name="source">查询序列</param>
/// <returns></returns>
public static async Task <List <TSource> > ToListAsync <TSource>(this IDbQueryable <TSource> source)
{
return(await source.DbContext.Database.ExecuteAsync <List <TSource> >(source)); //.ExecuteListAsync(source);
}
/// <summary>
/// 指示查询应该包含外键
/// </summary>
/// <typeparam name="TSource">主表类型</typeparam>
/// <typeparam name="TProperty">外键类型</typeparam>
/// <typeparam name="TResult">外键的结果元素</typeparam>
/// <param name="source">主表</param>
/// <param name="path">外键</param>
/// <param name="keySelector">选择字段，例如：x => x.FieldName。其中 Lambda 的 参数 x 可以任意指定，它不参与表别名解析。指定 null 表示选择所有字段</param>
/// <param name="navFilter">
/// 从表的过滤条件。注意这里的表达式不能含有诸如 a=> a.Nav.FieldName == value 这种有导航属性的过滤谓词。
/// 解析的 SQL 会直接拼接在 On 后面，如 ON a.FieldName = b.FieldName AND navFilter。
/// 如果想拼在 WHERE 后面，请使用 IDbQueryable.Where(a=>a.Nav.FieldName == condition) 语法。
/// </param>
/// <returns></returns>
public static IDbQueryable <TSource> Include <TSource, TProperty, TResult>(this IDbQueryable <TSource> source,
Expression <Func <TSource, TProperty> > path, Expression <Func <TProperty, TResult> > keySelector, Expression <Func <TProperty, bool> > navFilter)
{
return(source.CreateQuery <TSource>(new DbExpression(DbExpressionType.Include, new Expression[] { path, keySelector, navFilter })));
}
/// <summary>
/// 确定序列是否包含任何元素
/// </summary>
/// <typeparam name="TSource">source 的元素类型</typeparam>
/// <param name="source">查询序列</param>
/// <returns>如果源序列中存在元素通过了指定谓词中的测试，则为 true；否则为 false</returns>
public static bool Any <TSource>(this IDbQueryable <TSource> source)
{
return(source.Any(null));
}
/// <summary>
/// 返回泛型 <see cref="IDbQueryable{TSource}"/> 中的所有值之和
/// </summary>
/// <typeparam name="TSource">source 的元素类型</typeparam>
/// <typeparam name="TResult">keySelector 的元素类型</typeparam>
/// <param name="source">查询序列</param>
/// <param name="keySelector">应用于每个元素的转换函数</param>
/// <returns></returns>
public static TResult Sum <TSource, TResult>(this IDbQueryable <TSource> source, Expression <Func <TSource, TResult> > keySelector)
{
IDbQueryable <TResult> query = source.CreateQuery <TResult>(DbExpressionType.Sum, keySelector);
return(query.Execute <TResult>());
}
/// <summary>
/// 确定序列是否包含任何元素
/// </summary>
/// <typeparam name="TSource">source 的元素类型</typeparam>
/// <param name="source">查询序列</param>
/// <returns></returns>
/// <param name="predicate">用于测试每个元素是否满足条件的函数</param>
/// <returns>如果源序列中存在元素通过了指定谓词中的测试，则为 true；否则为 false</returns>
public static bool Any <TSource>(this IDbQueryable <TSource> source, Expression <Func <TSource, bool> > predicate)
{
IDbQueryable <bool> query = source.CreateQuery <bool>(DbExpressionType.Any, predicate);
return(query.Execute <bool>());
}
/// <summary>
/// 根据键按升序对序列的元素排序
/// <para>
/// 示例： source = source.Where(predicate).OrderBy<TSource, TSource2, int>((a, b) => b.UserId != null ? b.Sequence : a.Sequence);
/// </para>
/// </summary>
/// <typeparam name="TSource">source 的元素类型</typeparam>
/// <typeparam name="TSource2">source 中的关联语义的元素类型</typeparam>
/// <typeparam name="TKey">keySelector 返回的键的类型</typeparam>
/// <param name="source">查询序列</param>
/// <param name="keySelector">用于从元素中提取键的函数</param>
/// <returns></returns>
public static IDbQueryable <TSource> OrderBy <TSource, TSource2, TKey>(this IDbQueryable <TSource> source, Expression <Func <TSource, TSource2, TKey> > keySelector)
{
return(source.CreateQuery <TSource>(DbExpressionType.OrderBy, keySelector));
}
/// <summary>
/// 执行SQL 语句，并返回 <see cref="IDataReader"/> 对象
/// </summary>
/// <param name="query">查询语义</param>
/// <returns></returns>
public IDataReader ExecuteReader(IDbQueryable query)
{
IDbCommand cmd = this.CreateCommand(query.Resolve());
return(this.ExecuteReader(cmd));
}
