/// <summary>
/// 转换字符串为特定类型的查询对象，其中类型由 T 参数定义
/// <para>
/// 例：SELECT FieldName FROM TableName WHERE Condition={0} AND Condition1={1}
/// </para>
/// </summary>
/// <param name="sql">SQL 命令</param>
/// <param name="args">参数列表</param>
public IDbQueryable <T> GetTable <T>(string sql, params object[] args)
{
DbQueryable <T> query = new DbQueryable <T>(this, new List <DbExpression>
{
new DbExpression(DbExpressionType.GetTable, new[] { Expression.Constant(sql), Expression.Constant(args, typeof(object[])) }),
});
return(query);
}
/// <summary>
/// 构造一个 <see cref="IDbQueryable"/> 对象，该对象可计算指定表达式树所表示的查询
/// </summary>
/// <typeparam name="TResult">返回的元素类型</typeparam>
/// <param name="dbExpression">查询表达式</param>
/// <returns></returns>
public IDbQueryable <TResult> CreateQuery <TResult>(DbExpression dbExpression)
{
var collection = new List <DbExpression>(this.DbExpressions.Count + (dbExpression != null ? 1 : 0));
collection.AddRange(_dbExpressions);
if (dbExpression != null)
{
collection.Add(dbExpression);
}
IDbQueryable <TResult> query = new DbQueryable <TResult>(this.DbContext, collection);
return(query);
}
/// <summary>
/// 创建 SQL 命令
/// </summary>
/// <param name="source">查询 语句</param>
/// <param name="indent">缩进</param>
/// <param name="isOutermost">是否外层查询，内层查询不需要结束符(;)</param>
/// <param name="context">解析SQL命令上下文</param>
/// <returns></returns>
internal DbRawCommand Translate <T>(IDbQueryable <T> source, int indent, bool isOutermost, ITranslateContext context)
{
DbQueryable <T> dbQueryable = (DbQueryable <T>)source;
// 当前查询语义如果不设置参数化，默认使用参数化
if (!dbQueryable.HasSetParameterized)
{
dbQueryable.Parameterized = true;
}
// 如果解析上下文是空，默认创建一个上下文，用来承载 DbContext （查询上下文）
if (context == null)
{
context = this.CreateTranslateContext(dbQueryable.DbContext);
}
// 如果使用参数化查询并且参数列表为空，默认创建了一个新的参数列表
if (dbQueryable.Parameterized && context.Parameters == null)
{
context.Parameters = new List <IDbDataParameter>(8);
}
// 解析查询语义
IDbQueryTree tree = DbQueryableParser.Parse <T>(dbQueryable);
// 查询
if (tree is DbQuerySelectTree)
{
return(this.TranslateSelectCommand((DbQuerySelectTree)tree, indent, isOutermost, context));
}
// 新增
else if (tree is DbQueryInsertTree)
{
return(this.TranslateInsertCommand <T>((DbQueryInsertTree)tree, context));
}
// 更新
else if (tree is DbQueryUpdateTree)
{
return(this.TranslateUpdateCommand <T>((DbQueryUpdateTree)tree, context));
}
// 删除
else if (tree is DbQueryDeleteTree)
{
return(this.TranslateDeleteCommand <T>((DbQueryDeleteTree)tree, context));
}
throw new NotImplementedException();
}
/// <summary>
/// 解析左关联
/// </summary>
protected virtual void AppendLfInJoin(DbExpression dbExpression)
{
_builder.Append(' ');
DbQueryable dbQueryable = (DbQueryable)((dbExpression.Expressions[0] as ConstantExpression).Value);
dbQueryable.Parameterized = _builder.Parameterized;
if (dbQueryable.DbExpressions.Count == 1 && dbQueryable.DbExpressions[0].DbExpressionType == DbExpressionType.GetTable)
{
// 区别 GetTable 有三个重载
var expressions = dbQueryable.DbExpressions[0].Expressions;
if (expressions.Length == 2 && ((expressions[0] as ConstantExpression).Value as string) != null)
{
string text = (expressions[0] as ConstantExpression).Value as string;
object[] @params = (expressions[1] as ConstantExpression).Value as object[];
var provider = ((DbQueryProvider)_builder.Provider);
var context = _builder.TranslateContext;
// 解析参数
object[] args = null;
if (@params != null)
{
args = @params.Select(x => provider.Constor.GetSqlValue(x, context)).ToArray();
}
string sql = text;
if (args != null && args.Length > 0)
{
sql = string.Format(sql, args);
}
var cmd = new DbRawCommand(sql, context.Parameters, CommandType.Text);
_builder.Append("(");
_builder.Append(cmd.CommandText);
_builder.AppendNewLine();
_builder.Append(')');
}
else
{
Type type = dbExpression.Expressions[0].Type.GetGenericArguments()[0];
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(type);
_builder.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary);
}
}
else
{
// 嵌套
var cmd = dbQueryable.Translate(_builder.Indent + 1, false, _builder.TranslateContext);
_builder.Append("(");
_builder.Append(cmd.CommandText);
_builder.AppendNewLine();
_builder.Append(')');
}
var left = dbExpression.Expressions[1] as LambdaExpression;
var right = dbExpression.Expressions[2] as LambdaExpression;
// t0(t1)
string alias = !(left.Body.NodeType == ExpressionType.New || left.Body.NodeType == ExpressionType.MemberInit)
? _ag.GetTableAlias(dbExpression.Expressions[2])
: _ag.GetTableAlias(right.Parameters[0]);
_builder.Append(' ');
_builder.Append(alias);
_builder.Append(' ');
// ON a.Name = b.Name AND a.Id = b.Id
_builder.Append("ON ");
if (left.Body.NodeType == ExpressionType.New)
{
var body1 = left.Body as NewExpression;
var body2 = right.Body as NewExpression;
for (int index = 0; index < body1.Arguments.Count; ++index)
{
base.Visit(body1.Arguments[index]);
_builder.Append(" = ");
base.Visit(body2.Arguments[index]);
if (index < body1.Arguments.Count - 1)
{
_builder.Append(" AND ");
}
}
}
else if (left.Body.NodeType == ExpressionType.MemberInit)
{
var body1 = left.Body as MemberInitExpression;
var body2 = right.Body as MemberInitExpression;
for (int index = 0; index < body1.Bindings.Count; ++index)
{
base.Visit((body1.Bindings[index] as MemberAssignment).Expression);
_builder.Append(" = ");
base.Visit((body2.Bindings[index] as MemberAssignment).Expression);
if (index < body1.Bindings.Count - 1)
{
_builder.Append(" AND ");
}
}
}
else
{
base.Visit(left.Body.ReduceUnary());
_builder.Append(" = ");
base.Visit(right.Body.ReduceUnary());
}
}
/// <summary>
/// 返回特定类型的对象的集合，其中类型由 T 参数定义
/// <para>
/// 适用场景：
/// 多个导航属性类型一致，用此重载可解决 a.Navigation.Property 的表别名定位问题
/// </para>
/// </summary>
/// <typeparam name="T">元素类型</typeparam>
/// <typeparam name="TNavMember">导航属性类型</typeparam>
/// <param name="path">导航属性，注意在一组上下文中第一个 GetTable 的这个参数将被自动忽略</param>
/// <returns></returns>
public IDbQueryable <TNavMember> GetTable <T, TNavMember>(Expression <Func <T, TNavMember> > path)
{
DbQueryable <TNavMember> query = new DbQueryable <TNavMember>(this, new List <DbExpression>
{
new DbExpression(DbExpressionType.GetTable, new[] { Expression.Constant(typeof(TNavMember)), (Expression)path }),
});
return(query);
//// 在 select 语义保证外键属性不为空，否则在 ko 绑定的时候会产生不可预料的异常
//var query =
// from a in context.GetTable<Client>()
// // 最新订单
// join b in newestQuery on a.ClientId equals b.ClientId into u_b
// // 注册推荐码
// join c in context.GetTable<Client, RecommendationCode>(a => a.RegRecommendationCode) on a.RegRecommendationId equals c.RecommendationId into u_c
// // 注册推荐码对应的合作商
// from c in u_c.DefaultIfEmpty()
// join d in context.GetTable<Partner>() on new { A0 = (int)RecommendationType.ServiceAgent, B0 = c.ServiceAgentId } equals new { A0 = d.PartnerType, B0 = d.PartnerId } into u_d
// join e in context.GetTable<Partner>() on new { A0 = (int)RecommendationType.SaleAgent, B0 = c.SaleAgentId } equals new { A0 = e.PartnerType, B0 = e.PartnerId } into u_e
// // 推荐码
// join f in context.GetTable<Client, RecommendationCode>(a => a.RecommendationCode) on a.RecommendationId equals f.RecommendationId into u_f
// // 推荐码对应的合作商
// from f in u_f.DefaultIfEmpty()
// join g in context.GetTable<Partner>() on new { A0 = (int)RecommendationType.ServiceAgent, B0 = f.ServiceAgentId } equals new { A0 = g.PartnerType, B0 = g.PartnerId } into u_g
// join h in context.GetTable<Partner>() on new { A0 = (int)RecommendationType.SaleAgent, B0 = f.SaleAgentId } equals new { A0 = h.PartnerType, B0 = h.PartnerId } into u_h
// from b in u_b.DefaultIfEmpty()
// from d in u_d.DefaultIfEmpty()
// from e in u_e.DefaultIfEmpty()
// from g in u_g.DefaultIfEmpty()
// from h in u_h.DefaultIfEmpty()
// select new Client(a)
// {
// PartnerShortName = h.PartnerId != null ? h.ShortName : (g.PartnerId != null ? g.ShortName : (e.PartnerId != null ? e.ShortName : d.ShortName)),
// Employee = new Employee { Deletable = canEidtEmployee, EmployeeCode = a.Employee.EmployeeCode, EmployeeName = a.Employee.EmployeeName },
// Operation = new Employee { Deletable = canEidtOperation, EmployeeName = a.Operation.EmployeeName },
// CS = new Employee { Deletable = canEidtCs, EmployeeName = a.CS.EmployeeName },
// Manager = new Employee { EmployeeName = a.Manager.EmployeeName },
// Signature = new Employee { EmployeeName = a.Signature.EmployeeName },
// Package = new Package { PackageType = a.Package.PackageType, PackageCode = a.Package.PackageCode, PackageName = a.Package.PackageName },
// RegRecommendationCode = new RecommendationCode
// {
// SaleType = c.SaleType,
// RecommendationName = c.RecommendationName
// //RecommendationName = a.RegRecommendationCode.RecommendationName
// },
// RecommendationCode = new RecommendationCode
// {
// SaleType = f.SaleType,
// RecommendationName = f.RecommendationName
// //RecommendationName = a.RecommendationCode.RecommendationName //f.RecommendationName
// },
// NewestOrder = new SaleOrder
// {
// OrderId = b.OrderId,
// OrderNo = b.OrderNo,
// EmployeeId = b.EmployeeId,
// EmployeeName = b.EmployeeName,
// PayDate = b.PayDate,
// PayAmount = b.PayAmount,
// ClientId = b.ClientId,
// Currency = b.Currency
// }
// };
//query = query.Where(predicate);
//return query;
}
/// <summary>
/// 解析 SQL 命令
/// <para>
/// 返回的已经解析语义中执行批次用 null 分开
/// </para>
/// </summary>
/// <param name="dbQueryables">查询语句</param>
/// <returns></returns>
public virtual List <DbRawCommand> Translate(List <object> dbQueryables)
{
ITranslateContext context = null;
var sqlList = new List <DbRawCommand>();
foreach (var obj in dbQueryables)
{
if (obj == null)
{
continue;
}
if (obj is IDbQueryable)
{
DbQueryable dbQuery = (DbQueryable)obj;
dbQuery.Parameterized = true;
if (context == null)
{
context = this.CreateTranslateContext(dbQuery.DbContext);
}
if (context.Parameters == null)
{
context.Parameters = new List <IDbDataParameter>(8);
}
var cmd = dbQuery.Translate(0, true, context);
sqlList.Add(cmd);
if (cmd.Parameters != null && cmd.Parameters.Count > 1000)
{
// 1000个参数，就要重新分批
sqlList.Add(null);
context = this.CreateTranslateContext(dbQuery.DbContext);
context.Parameters = new List <IDbDataParameter>(8);
}
}
else if (obj is DbRawSql)
{
DbRawSql rawSql = (DbRawSql)obj;
if (context == null)
{
context = this.CreateTranslateContext(rawSql.DbContext);
}
if (context.Parameters == null)
{
context.Parameters = new List <IDbDataParameter>(8);
}
// 解析参数
object[] args = null;
if (rawSql.Parameters != null)
{
args = rawSql.Parameters.Select(x => this.Constor.GetSqlValue(x, context)).ToArray();
}
string sql = rawSql.CommandText;
if (args != null && args.Length > 0)
{
sql = string.Format(sql, args);
}
var cmd = new DbRawCommand(sql, context.Parameters, CommandType.Text);
sqlList.Add(cmd);
if (cmd.Parameters != null && cmd.Parameters.Count > 1000)
{
// 1000个参数，就要重新分批
sqlList.Add(null);
context = this.CreateTranslateContext(rawSql.DbContext);
context.Parameters = new List <IDbDataParameter>(8);
}
}
else if (obj is string)
{
string sql = obj.ToString();
sqlList.Add(new DbRawCommand(sql));
}
else
{
// 解析批量插入操作
List <IDbQueryable> bulkList = obj as List <IDbQueryable>;
if (bulkList != null && bulkList.Count > 0)
{
this.TranslateBulk(sqlList, bulkList);
}
}
}
return(sqlList);
}
// LEFT OR INNER JOIN
private void AppendLfInJoin(ISqlBuilder jf, ISqlBuilder on, DbExpression dbExpression)
{
DbQueryable dbQueryable = (DbQueryable)((dbExpression.Expressions[0] as ConstantExpression).Value);
dbQueryable.Parameterized = jf.Parameterized;
if (!usedKeyword)
{
jf.AppendNewLine();
jf.Append(_keywordName);
usedKeyword = true;
}
else
{
jf.Append(',');
jf.AppendNewLine();
jf.Append(_pad);
}
if (dbQueryable.DbExpressions.Count == 1 && dbQueryable.DbExpressions[0].DbExpressionType == DbExpressionType.GetTable)
{
// 区别 GetTable 有三个重载
var expressions = dbQueryable.DbExpressions[0].Expressions;
if (expressions.Length == 2 && ((expressions[0] as ConstantExpression).Value as string) != null)
{
string text = (expressions[0] as ConstantExpression).Value as string;
object[] @params = (expressions[1] as ConstantExpression).Value as object[];
var provider = ((DbQueryProvider)jf.Provider);
var context = jf.TranslateContext;
// 解析参数
object[] args = null;
if (@params != null)
{
args = @params.Select(x => provider.Constor.GetSqlValue(x, context)).ToArray();
}
string sql = text;
if (args != null && args.Length > 0)
{
sql = string.Format(sql, args);
}
var cmd = new DbRawCommand(sql, context.Parameters, CommandType.Text);
jf.Append("( ");
jf.Append(_dbExpressionType == DbExpressionType.Delete ? cmd.CommandText.TrimStart() : cmd.CommandText);
jf.Append(')');
}
else
{
Type type = dbExpression.Expressions[0].Type.GetGenericArguments()[0];
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(type);
jf.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary);
}
}
else
{
// 嵌套
var cmd = dbQueryable.Translate(jf.Indent + _dbExpressionType == DbExpressionType.Delete ? 2 : 1, false, jf.TranslateContext);
jf.Append("( ");
jf.Append(_dbExpressionType == DbExpressionType.Delete ? cmd.CommandText.TrimStart() : cmd.CommandText);
jf.Append(')');
}
LambdaExpression left = dbExpression.Expressions[1] as LambdaExpression;
LambdaExpression right = dbExpression.Expressions[2] as LambdaExpression;
// t0(t1)
string alias = !(left.Body.NodeType == ExpressionType.New || left.Body.NodeType == ExpressionType.MemberInit)
? _ag.GetTableAlias(dbExpression.Expressions[2])
: _ag.GetTableAlias(right.Parameters[0]);
jf.Append(' ');
jf.Append(alias);
if (on.Length > 0)
{
on.Append(" AND ");
}
if (left.Body.NodeType == ExpressionType.New)
{
NewExpression body1 = left.Body as NewExpression;
NewExpression body2 = right.Body as NewExpression;
for (int index = 0; index < body1.Arguments.Count; ++index)
{
on.AppendMember(_ag, body1.Arguments[index]);
on.Append(" = ");
on.AppendMember(_ag, body2.Arguments[index]);
if (index < body1.Arguments.Count - 1)
{
on.Append(" AND ");
}
}
}
else if (left.Body.NodeType == ExpressionType.MemberInit)
{
MemberInitExpression body1 = left.Body as MemberInitExpression;
MemberInitExpression body2 = right.Body as MemberInitExpression;
for (int index = 0; index < body1.Bindings.Count; ++index)
{
on.AppendMember(_ag, (body1.Bindings[index] as MemberAssignment).Expression);
on.Append(" = ");
on.AppendMember(_ag, (body2.Bindings[index] as MemberAssignment).Expression);
if (index < body1.Bindings.Count - 1)
{
on.Append(" AND ");
}
}
}
else
{
on.AppendMember(_ag, left.Body.ReduceUnary());
on.Append(" = ");
on.AppendMember(_ag, right.Body.ReduceUnary());
}
}
