public ISelectResult <TResult> Select <TResult>(Expression <Func <T, TResult> > sel) { var dynamicParameters = new DynamicParameters(); var selExp = new JoinSelectExpression(sel, new Dictionary <string, string> { { "", $"{this.DBModel.GetDBModel_SqlProvider().GetProviderOption().OpenQuote}jtmp{this.DBModel.GetDBModel_SqlProvider().GetProviderOption().CloseQuote}" } }, dynamicParameters, DBModel.GetDBModel_SqlProvider()); var selSql = string.Join(",", selExp.QueryColumns).Replace($"{this.DBModel.GetDBModel_SqlProvider().GetProviderOption().OpenQuote}jtmp{this.DBModel.GetDBModel_SqlProvider().GetProviderOption().CloseQuote}.", ""); var sql = new StringBuilder($"SELECT {selSql} FROM {this.DBModel.GetDBModel_SqlProvider().GetProviderOption().OpenQuote}{DBTool.GetTableName(this.DBModel)}{this.DBModel.GetDBModel_SqlProvider().GetProviderOption().CloseQuote} {DBTool.GetTableWith(this)} WHERE 1=1"); var countSql = new StringBuilder($"SELECT COUNT(1) FROM {DBTool.GetTableName(this.DBModel)} {DBTool.GetTableWith(this)} WHERE 1=1"); var whereExp = new WhereExpression(WhereExpression, "", dynamicParameters, DBModel.GetDBModel_SqlProvider()); sql.Append(whereExp.SqlCmd); countSql.Append(whereExp.SqlCmd); if (OrderbyList.Count > 0) { sql.Append($" ORDER BY "); var olst = OrderbyList.Select(m => { var order = new JoinOrderExpression(m.Field, new Dictionary <string, string> { { "", "" } }, dynamicParameters, DBModel.GetDBModel_SqlProvider()); return(order.SqlCmd + " " + m.OrderBy); }); sql.Append(string.Join(",", olst) + " "); } sql.Append(DBModel.GetDBModel_SqlProvider().GetLimit(StarSize, Rows)); var sr = new SelectResult <TResult>(sql.ToString(), countSql.ToString(), dynamicParameters); sr.DBModel = DBModel; return(sr); }
private IJoinQuery <TResult> Join <TR, TKey, TResult>(string type, IQuery <TR> rightQuery, Expression <Func <T, TKey> > leftKeySelector, Expression <Func <TR, TKey> > rightKeySelector, Expression <Func <T, TR, TResult> > resultSelector) where TR : BaseDBModel, new() { var dynamicParameters = new DynamicParameters(); var left = new JoinExpression(leftKeySelector, new Dictionary <string, string> { { "", "t1" } }, dynamicParameters, DBModel.GetDBModel_SqlProvider()); var right = new JoinExpression(rightKeySelector, new Dictionary <string, string> { { "", "t2" } }, dynamicParameters, DBModel.GetDBModel_SqlProvider()); StringBuilder sqlJoin = new StringBuilder(); foreach (var v in left.JoinDic) { if (sqlJoin.Length > 0) { sqlJoin.Append(" AND "); } sqlJoin.Append("("); sqlJoin.Append(v.Value); sqlJoin.Append("="); sqlJoin.Append(right.JoinDic[v.Key]); sqlJoin.Append(")"); } var joinStr = $"{DBTool.GetTableName(DBModel)} t1 {DBTool.GetTableWith(this)} {type} JOIN {DBTool.GetTableName(rightQuery.DBModel)} t2 {DBTool.GetTableWith(rightQuery)} ON {sqlJoin}"; var sel = new JoinResultMapExpression(resultSelector, new Dictionary <string, string> { { "", "t1" } }, "t2", dynamicParameters, DBModel.GetDBModel_SqlProvider()); StringBuilder sqlWhere = new StringBuilder(); var where = new WhereExpression(this.WhereExpression, "t1", dynamicParameters, DBModel.GetDBModel_SqlProvider()); if (!string.IsNullOrEmpty(where.SqlCmd)) { sqlWhere.Append(where.SqlCmd); } where = new WhereExpression(rightQuery.WhereExpression, "t2", dynamicParameters, DBModel.GetDBModel_SqlProvider()); if (!string.IsNullOrEmpty(where.SqlCmd)) { sqlWhere.Append(where.SqlCmd); } return(new JoinQueryInfo <TResult>(DBModel, joinStr, 2, sel.MapList, sqlWhere.ToString(), dynamicParameters)); }
public ISelectResult <TResult> Select <TResult>(Expression <Func <T, TResult> > select, bool firstTableSelectAll = false) { var p = new DynamicParameters(); p.AddDynamicParams(Param); var selectResult = new JoinSelectExpression(select, Map, p, DBModel.GetDBModel_SqlProvider()); var selSql = string.Join(",", selectResult.QueryColumns); if (firstTableSelectAll) { if (!string.IsNullOrEmpty(selSql)) { selSql = "t1.*," + selSql; } else { selSql = "t1.*"; } } var isDBModel = typeof(BaseDBModel).IsAssignableFrom(typeof(TResult)); if (isDBModel) { selSql = "0 as DBModel_IsBeginChange," + selSql; } var whereExpression = new JoinWhereExpression(WhereExpression, Map, p, DBModel.GetDBModel_SqlProvider()); var sql = new StringBuilder($"SELECT {selSql} FROM {JoinStr} WHERE 1=1 {Where} {whereExpression.SqlCmd} "); var countSql = new StringBuilder($"SELECT count(1) FROM {JoinStr} WHERE 1=1 {Where} {whereExpression.SqlCmd} "); if (OrderbyList.Count > 0) { sql.Append($" ORDER BY "); var olst = OrderbyList.Select(m => { var order = new JoinOrderExpression(m.Field, Map, p, DBModel.GetDBModel_SqlProvider()); return(order.SqlCmd + " " + m.OrderBy); }); sql.Append(string.Join(",", olst) + " "); } sql.Append(DBModel.GetDBModel_SqlProvider().GetLimit(StarSize, Rows)); return(new SelectResult <TResult>(sql.ToString(), countSql.ToString(), p)); }