/// <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)); }