/// <summary>
/// 执行表连接查询，该方法可连接两个不同的表
/// </summary>
/// <typeparam name="TModel1">要连接的数据库实体对象</typeparam>
/// <typeparam name="TModel2">要连接的数据库实体对象</typeparam>
/// <param name="alisName">主表的别名</param>
/// <param name="unionAlisName">连接表的别名</param>
/// <param name="unionType">连接类型</param>
/// <param name="predicate">On 的查询表达式</param>
/// <returns></returns>
public QueryContext <T> Union <TModel1, TModel2>(string alisName, string unionAlisName, UnionType unionType, Expression <Func <TModel1, TModel2, bool> > predicate)
{
Type type = typeof(TModel1);
var last = UnionList.Where(f => f.Model.Equals(type) && f.UnionAlisName == alisName).FirstOrDefault();
if (alisName != masterAlisName && last == null)
{
ExpressionUnionModel u2 = new ExpressionUnionModel
{
Model = typeof(TModel1),
MasterType = typeof(T),
Body = predicate.Body,
UnionType = unionType,
AlisName = alisName,
UnionAlisName = alisName
};
UnionList.Add(u2);
}
ExpressionUnionModel us = new ExpressionUnionModel
{
Model = typeof(TModel2),
MasterType = typeof(TModel1),
Body = predicate.Body,
UnionType = unionType,
AlisName = alisName,
UnionAlisName = unionAlisName
};
UnionList.Add(us);
return(this);
}
/// <summary>
/// 将查询命令和条件转换为 SQL 语句
/// </summary>
/// <returns></returns>
public override string ToString()
{
Type mastertype = typeof(T);
string tableName = MyStagingUtils.GetMapping(mastertype);
// master table
StringBuilder sqlText = new StringBuilder();
sqlText.AppendLine($"SELECT {string.Join(",", Fields)} FROM {tableName} {masterAlisName}");
// union
int _index = 2;
foreach (var item in UnionList)
{
DbExpressionVisitor expression = new DbExpressionVisitor
{
TypeMaster = item.MasterType,
AliasMaster = item.AlisName,
AliasUnion = item.UnionAlisName
};
expression.Visit(item.Body);
string unionTableName = MyStagingUtils.GetMapping(item.Model);
sqlText.AppendLine(item.UnionType.ToString().Replace("_", " ") + " " + unionTableName + " " + expression.AliasUnion + " ON " + expression.SqlText.Builder.ToString());
ParamList.AddRange(expression.SqlText.Parameters);
_index++;
}
// condition
if (WhereExpressionList.Count > 0)
{
foreach (var item in WhereExpressionList)
{
DbExpressionVisitor expression = new DbExpressionVisitor();
if (UnionList.Count == 0)
{
expression.TypeMaster = item.Model;
expression.AliasMaster = masterAlisName;
}
else
{
ExpressionUnionModel union = null;
if (item.UnionAlisName == null)
{
union = UnionList.FirstOrDefault(f => f.Model == item.Model);
}
else
{
union = UnionList.FirstOrDefault(f => f.Model == item.Model && f.UnionAlisName == item.UnionAlisName);
}
if (union == null && typeof(T) == item.Model)
{
expression.TypeMaster = item.Model;
expression.AliasMaster = masterAlisName;
}
else if (union != null)
{
expression.AliasMaster = union.AlisName;
expression.AliasUnion = union.UnionAlisName;
}
else
{
throw new NotSupportedException($"找不到 where {item.Body.ToString()}条件的表，不支持的表查询条件");
}
}
expression.Visit(item.Body);
WhereList.Add(expression.SqlText.Builder.ToString().ToLower());
ParamList.AddRange(expression.SqlText.Parameters);
}
}
if (WhereList.Count > 0)
{
sqlText.AppendLine("WHERE " + string.Join("\nAND ", WhereList));
}
if (!string.IsNullOrEmpty(GroupByText))
{
sqlText.AppendLine(GroupByText);
}
if (!string.IsNullOrEmpty(GroupByText) && !string.IsNullOrEmpty(HavingText))
{
sqlText.AppendLine(HavingText);
}
if (!string.IsNullOrEmpty(OrderByText))
{
sqlText.AppendLine(OrderByText);
}
if (!string.IsNullOrEmpty(LimitText))
{
sqlText.AppendLine(LimitText);
}
this.commandtext = sqlText.ToString();
return(this.commandtext);
}
