/// <summary>
/// Recupera os dados da instancia.
/// </summary>
/// <param name="info"></param>
/// <param name="context"></param>
void ISerializable.GetObjectData(SerializationInfo info, StreamingContext context)
{
info.AddValue("Left", _left);
info.AddValue("Right", _right);
info.AddValue("Conditional", _conditional);
info.AddValue("Type", _type.ToString());
}
private SelectQueryAble <T> JoinParser(Expression expression, Expression expressionBody, JoinType joinType, params Type[] types)
{
if (expression != null && expressionBody != null)
{
foreach (var type in types)
{
if (!TableTypeCollections.Contains(type))
{
var tableName = types[types.Length - 1].GetDapperTableName(SqlBuilder._SqlFormater);
throw new Exception($"select query tables can not found {tableName}");
}
}
var joinStr = string.Empty;
if (joinType != JoinType.NONE)
{
joinStr = joinType.ToString();
}
var joinTableName = types[types.Length - 1].GetDapperTableName(SqlBuilder._SqlFormater);
var joinTableAlias = SqlBuilder.GetTableAlias(joinTableName);
SqlBuilder.AppendJoinSql($"{joinStr} JOIN {(joinTableName + " " + joinTableAlias)} ON ");
SqlVistorProvider.Join(expressionBody, SqlBuilder);
}
return(this);
}
internal Query(Query query, string from, string to, JoinType joinType, int linkedEntityDepth)
{
var entityName = EntityNameAttribute.GetFromType <T>();
this.query = query;
this.linkedEntityDepth = linkedEntityDepth;
EntityRootElement = new XElement("link-entity");
EntityRootElement.Add(new XAttribute("name", entityName));
EntityRootElement.Add(new XAttribute("alias", entityName));
EntityRootElement.Add(new XAttribute("from", from));
EntityRootElement.Add(new XAttribute("to", to));
EntityRootElement.Add(new XAttribute("link-type", joinType.ToString().ToLower()));
if (linkedEntityDepth < 2)
{
var createdOn = new XElement("attribute");
createdOn.Add(new XAttribute("name", "createdon"));
EntityRootElement.Add(createdOn);
var modifiedOn = new XElement("attribute");
modifiedOn.Add(new XAttribute("name", "modifiedon"));
EntityRootElement.Add(modifiedOn);
}
}
ISqlSelect ISqlSelect.Join <TJoin>(JoinType joinType, ISqlFilter condition, SqlAlias <TJoin> joinAlias)
{
ISqlSelect result;
switch (joinType)
{
case JoinType.Inner:
result = InnerJoin(condition, joinAlias);
break;
case JoinType.Left:
result = LeftJoin(condition, joinAlias);
break;
case JoinType.Right:
result = RightJoin(condition, joinAlias);
break;
case JoinType.Full:
result = FullJoin(condition, joinAlias);
break;
default:
throw new NotSupportedException($"{joinType.ToString()} is not supported");
}
return(result);
}
internal Dictionary <string, string> ToPostParams()
{
return(new Dictionary <string, string> {
["field_join_type"] = FieldJoinType.ToString("g"),
["text"] = Text,
["string_join_type"] = StringJoinType.ToString("g"),
["keywords"] = Keywords.ToYesNo(),
["title"] = Title.ToYesNo(),
["description"] = Description.ToYesNo(),
["md5"] = MD5.ToYesNo(),
["keyword_id"] = KeywordId?.ToString(),
["username"] = Username,
["user_id"] = UserId?.ToString(),
["favs_user_id"] = FavsUserId?.ToString(),
["unread_submissions"] = UnreadSubmissions.ToYesNo(),
["type"] = Type?.ToString("d"),
["sales"] = Sales?.ToString("g"),
["pool_id"] = PoolId?.ToString(),
["orderby"] = OrderBy.ToString("g"),
["dayslimit"] = DaysLimit?.ToString(),
["random"] = Random.ToYesNo(),
["scraps"] = Scraps.ToString("g"),
["count_limit"] = CountLimit.ToString()
});
}
public override string ToString()
{
#pragma warning disable IDE0071 // Simplify interpolation
return($"{JoinType.ToString()}Join subquery {Table.SchemaAndTableName} AS {Alias}");
#pragma warning restore IDE0071 // Simplify interpolation
}
public virtual string ToQueryString()
{
StringBuilder sb = new StringBuilder();
On.ToList().ForEach((s) => {
sb.Append(s.ToQueryString());
});
return string.Format("{0} join {1} on 1 = 1 and {2}", Type.ToString(), Table, sb.ToString());
}
public override string ToString()
{
string[] textArray1 =
{
Left.Table.Name, " ", JoinType.ToString(), " join ", Right.Table.Name, " on ",
Left.Name, " = ", Right.Name
};
return(string.Concat(textArray1));
}
private string ConvertJoinTypeToString(JoinType joinType)
{
Regex regex = new Regex(@"
(?<=[A-Z])(?=[A-Z][a-z]) |
(?<=[^A-Z])(?=[A-Z]) |
(?<=[A-Za-z])(?=[^A-Za-z])", RegexOptions.IgnorePatternWhitespace);
return(regex.Replace(joinType.ToString(), " ").ToUpper());
}
public IQueryable <T1, T2> Join(Expression <Func <T1, T2, bool> > expression, JoinType join = JoinType.Inner)
{
var onExpression = ExpressionUtil.BuildExpression(expression, _param, false);
var table1Name = EntityUtil.GetTable <T1>().TableName;
var table2Name = EntityUtil.GetTable <T2>().TableName;
var joinType = string.Format("{0} JOIN", join.ToString().ToUpper());
Join(string.Format("{0} {1} {2} ON {3}", table1Name, joinType, table2Name, onExpression));
return(this);
}
public virtual string ToQueryString()
{
StringBuilder sb = new StringBuilder();
foreach (Equal s in On)
{
sb.Append(s.ToQueryString());
}
return(string.Format("{0} join [{1}] on 1 = 1 {2}", Type.ToString(), Table, sb.ToString().Replace("[", "").Replace("]", "")));
}
internal static string Join(
string originalTableName,
string joinTableName,
string leftField,
string rightField,
JoinType joinType)
{
return(" {0} JOIN {1} ON {2} = {3}".FormatWith(
joinType.ToString(),
Table(joinTableName),
Field(originalTableName, leftField),
Field(joinTableName, rightField)));
}
private static string GetSqlJoinExpression(JoinType joinType)
{
switch (joinType)
{
case JoinType.InnerJoin:
return("INNER JOIN");
case JoinType.LeftJoin:
return("LEFT OUTER JOIN");
case JoinType.RightJoin:
return("RIGHT OUTER JOIN");
default:
throw new ArgumentOutOfRangeException(joinType.ToString());
}
}
private string GetStringFromJoinType(JoinType joinType)
{
switch (joinType)
{
case JoinType.Inner:
return("INNER");
case JoinType.Left:
return("LEFT");
case JoinType.Right:
return("RIGHT");
}
throw new InvalidOperationException("The join type: {0} is not supported."
.FormatString(joinType.ToString()));
}
/// <summary>
/// 联表查询
/// </summary>
/// <typeparam name="T">第一个表的对象</typeparam>
/// <typeparam name="T2">联接表的对象</typeparam>
/// <typeparam name="T3">联接表的对象</typeparam>
/// <param name="queryable">查询对象</param>
/// <param name="expression">条件表达式</param>
/// <param name="type">Join的类型</param>
/// <returns>Queryable</returns>
public static Queryable <T> JoinTable <T, T2, T3>(this Queryable <T> queryable,
Expression <Func <T, T2, T3, object> > expression, JoinType type = JoinType.LEFT)
{
queryable.DB.InitAttributes <T3>();
var re = new ResolveExpress();
queryable.WhereIndex = queryable.WhereIndex + 100;
re.Type = ResolveExpressType.nT;
var exLeftStr = Regex.Match(expression.ToString(), @"\((.+?)\).+").Groups[1].Value;
var exLeftArray = exLeftStr.Split(',');
var shortName1 = exLeftArray[1];
var shortName2 = exLeftArray[2];
re.ResolveExpression(re, expression, queryable.DB);
var joinTypeName = type.ToString();
string joinTableName = null;
if (queryable.DB._mappingTableList.IsValuable())
{
//别名表
if (queryable.DB._mappingTableList.IsValuable())
{
var name = typeof(T3).Name;
if (queryable.DB._mappingTableList.Any(it => it.Key == name))
{
joinTableName = queryable.DB._mappingTableList.First(it => it.Key == name).Value;
}
}
}
if (joinTableName.IsNullOrEmpty())
{
joinTableName = typeof(T3).Name;
}
var joinStr = string.Format(" {0} JOIN {1} {2} ON {3} ",
/*0*/ queryable.JoinTableValue.Count == 0 ? (" " + shortName1 + " " + joinTypeName) : joinTypeName,
/*1*/ joinTableName.GetTranslationSqlName(),
/*2*/ shortName2,
/*3*/ re.SqlWhere.Trim().TrimStart('A').TrimStart('N').TrimStart('D')
);
queryable.JoinTableValue.Add(joinStr);
queryable.Params.AddRange(re.Paras);
return(queryable);
}
private Queryable <T1, T> GetJoin <Tj>(JoinType joinType, Expression expression, int index)
{
LambdaExpression lambda = expression as LambdaExpression;
ReadOnlyCollection <ParameterExpression> parameters = lambda.Parameters;
this.CheckShortName(parameters[0].ToString());
if (joinBuilder == null)
{
joinBuilder = new StringBuilder();
}
var tjShortName = parameters[index].ToString();
this.SetType <Tj>(tjShortName);
string join = ExpressHelper.SqlWhere(lambda.Body);
joinBuilder.AppendFormat(" {0} join [{1}] {2} on ({3})", joinType.ToString(), ObjectUtil.GetTableName <Tj>(!mapColumns), tjShortName, join);
return(this);
}
/// <summary>
/// 联表查询根据字符串
/// </summary>
/// <typeparam name="T">第一个表的对象</typeparam>
/// <param name="queryable">查询对象</param>
/// <param name="tableName">表名（可是以表或也可以是SQL语句加括号）</param>
/// <param name="shortName">表名简写</param>
/// <param name="onWhere">on后面的条件</param>
/// <param name="whereObj">匿名参数(例如:new{id=1,name="张三"})</param>
/// <param name="type">Join的类型</param>
/// <returns>Queryable</returns>
public static Queryable <T> JoinTable <T>(this Queryable <T> queryable, string tableName, string shortName, string onWhere, object whereObj, JoinType type = JoinType.LEFT)
{
queryable.WhereIndex = queryable.WhereIndex + 100;;
string joinType = type.ToString();
string joinStr = string.Format(" {0} JOIN {1} {2} ON {3} ",
/*0*/ joinType,
/*1*/ tableName,
/*2*/ shortName,
/*3*/ onWhere
);
queryable.JoinTableValue.Add(joinStr);
if (whereObj != null)
{
queryable.Params.AddRange(SqlSugarTool.GetParameters(whereObj));
}
return(queryable);
}
/// <summary>
/// Converts a JoinType to SQL text.
/// </summary>
/// <param name="join">The join.</param>
/// <returns>SQL join keyword</returns>
private static string GetJoinString(JoinType join)
{
switch (join)
{
// Sometimes merge is better than hash,
// but more importantly, we must make sure that it never chooses loop
case JoinType.Inner:
return("join");
case JoinType.Left:
return("left join");
case JoinType.Right:
return("right join");
case JoinType.Full:
return("full join");
default:
throw new InvalidOperationException(join.ToString( ));
}
}
public static void SpatialJoin(object targetFC, object joinFC, string outputFcPath, JoinType joinType, string matchOption, IGPFieldMapping fieldMapping = null)
{
var geoprocessor = new Geoprocessor()
{
OverwriteOutput = true,
AddOutputsToMap = false
};
try
{
Delete(outputFcPath);
SpatialJoin spatialJoin = new SpatialJoin
{
join_features = joinFC,
target_features = targetFC,
join_type = joinType.ToString(),
match_option = matchOption,
out_feature_class = outputFcPath,
field_mapping = fieldMapping
};
var result = geoprocessor.Execute(spatialJoin, null);
}
catch (Exception)
{
if (geoprocessor.MessageCount > 0)
{
string excMessage = null;
for (int i = 0; i < geoprocessor.MessageCount; i++)
{
excMessage += geoprocessor.GetMessage(i) + Environment.NewLine;
}
throw new Exception(excMessage);
}
}
}
public override void LoadParameters(StringBuilder sb, List <System.Data.Common.DbParameter> pars, JoinType joinType)
{
if (sb.Length > 1)
{
sb.Append(" " + joinType.ToString() + " ");
}
sb.Append("(");
foreach (Item item in GroupConditions)
{
if (item is ConditionItem)
{
item.LoadParameters(sb, pars, JoinType.OR);
}
else if (item is ConditionAndGroup)
{
ConditionAndGroup group = item as ConditionAndGroup;
group.IsSubGroup = true;
group.LoadParameters(sb, pars, JoinType.OR);
}
}
sb.Append(")");
}
public void AddJoinByType(string tableSchemaName, string originalTableName, string joinTableName, string leftField, string rightField, JoinType joinType, string tableAlias)
{
var existsTableNameRealOverride = !String.IsNullOrEmpty(_tableNameRealOverride);
var tableNameSource = existsTableNameRealOverride ? _tableNameRealOverride : originalTableName;
var tableSchemaNameToUse = _sqlAdapter.GetSchemaName(tableSchemaName);
var tableNameToJoin = _sqlAdapter.GetTableName(joinTableName);
var columnJoinLeft = _sqlAdapter.GetColumnName(tableNameSource, leftField);
var existsTableAlias = !String.IsNullOrEmpty(tableAlias);
var joinTableNameToUse = existsTableAlias ? tableAlias : joinTableName;
var columnJoinRight = _sqlAdapter.GetColumnName(joinTableNameToUse, rightField);
var typeJoinNormalized = joinType.ToString().ToUpper();
var charToUse = _formatting == SqlBuilderFormatting.Indented ? "\n" : "";
var joinClause = new StringBuilder($"{charToUse}{typeJoinNormalized} JOIN {tableSchemaNameToUse}.{tableNameToJoin} ");
if (existsTableAlias)
{
joinClause.Append($"AS {tableAlias} ");
}
joinClause.Append($"ON ({columnJoinLeft} = {columnJoinRight}) ");
_sqlQueryBuilderExtension.AddJoinExpression(joinClause.ToString());
}
/// <summary>
/// 联表查询
/// </summary>
/// <typeparam name="T">第一个表的对象</typeparam>
/// <typeparam name="T2">联接的表对象</typeparam>
/// <param name="queryable"></param>
/// <param name="expression">表达示</param>
/// <param name="type"></param>
/// <returns></returns>
public static Queryable <T> JoinTable <T, T2, T3>(this Queryable <T> queryable, Expression <Func <T, T2, T3, object> > expression, JoinType type = JoinType.LEFT)
{
ResolveExpress re = new ResolveExpress();
queryable.WhereIndex = queryable.WhereIndex + 100;
re.Type = ResolveExpressType.nT;
var exLeftStr = Regex.Match(expression.ToString(), @"\((.+?)\).+").Groups[1].Value;
var exLeftArray = exLeftStr.Split(',');
var shortName1 = exLeftArray[1];
var shortName2 = exLeftArray[2];
re.ResolveExpression(re, expression);
string joinTableName = type.ToString();
string joinStr = string.Format(" {0} JOIN {1} {2} ON {3} ",
/*0*/ queryable.JoinTable.Count == 0 ? (" " + shortName1 + " " + joinTableName) : joinTableName.ToString(),
/*1*/ typeof(T3).Name,
/*2*/ shortName2,
/*3*/ re.SqlWhere.Trim().TrimStart('A').TrimStart('N').TrimStart('D')
);
queryable.JoinTable.Add(joinStr);
queryable.Params.AddRange(re.Paras);
return(queryable);
}
public override string ToString()
{
return(Left.Table.Name + " " + JoinType.ToString() + " join " + Right.Table.Name + " on " + Left.Name + " = " + Right.Name);
}
/// <summary>
/// Join
/// </summary>
/// <param name="sqlable"></param>
/// <param name="leftFiled">join左边连接字段</param>
/// <param name="RightFiled">join右边连接字段</param>
/// <param name="type">join类型</param>
/// <returns></returns>
public static Sqlable Join <T>(this Sqlable sqlable, string shortName, string leftFiled, string RightFiled, JoinType type)
{
Check.ArgumentNullException(sqlable.Sql, "语法错误，正确用法：sqlable.Form(“table”).Join");
sqlable.Sql.AppendFormat(" {0} JOIN {1} {2} {3} ON {4} = {5} ", type.ToString(), typeof(T).Name, shortName, sqlable.DB.IsNoLock.GetLockString(), leftFiled, RightFiled);
return(sqlable);
}
public override string ToString()
{
var entity = MetadataProvider.Instance.GetTableName(JoinEntityType) + " " + JoinAlias.Value;
return($"{JoinType.ToString().ToUpper()} JOIN\r\n {entity} ON {JoinCondition.RawSql }");
}
/// <summary>
/// Generates the body of the Where clause, without the starting ' where ';
/// </summary>
/// <param name="includeWhere">If literal word ' where ' should be included.</param>
/// <param name="skipJoin">If the [and/or] should be skipped.</param>
/// <returns></returns>
public string GetSql(bool includeWhere = true, bool skipJoin = true)
{
// No clause if no valid value.
if ((Inner == null || Inner.Count == 0) && (Column == null || Column == ""))
{
return("");
}
var result = "";
if (includeWhere)
{
result += " where ";
}
// If we're a parenthetical group
if (Inner != null && Inner.Count > 0)
{
var first = true;
if (skipJoin)
{
result += " ( ";
}
else
{
result += " " + Join.ToString().ToLower() + " ( ";
}
foreach (var where in Inner)
{
if (first)
{
// First item in a parenthetical group or top level has its
// Join ignored - it is implicitly [AND] logically.
result += where.GetSql(false, true);
first = false;
}
else
{
result += where.GetSql(false, false);
}
}
result += " ) ";
}
else
{
// We're a standard single term where clause
if (!skipJoin)
{
// [AND/OR]
result += " " + Join.ToString().ToLower() + " ";
}
// [AND/OR] [COLUMN]
result += " " + Column + " ";
// [AND/OR] [COLUMN] [=/LIKE]
if (Comparer == ComparisonType.Equal)
{
result += " = ";
}
else
{
result += " like ";
}
// [AND/OR] [COLUMN] [=/LIKE] [$COLUMN]
result += " $" + Column + " ";
}
return(result);
}
public override string ToString()
{
return(string.Format("{0} JOIN {1} ON {2}", JoinType.ToString().ToUpper(), Table, On));
}
public override string ToString()
{
// kicks out a query-friendly string represented by this JOIN object
var sb = new StringBuilder();
if (JoinType == JoinType.None || JoinType == JoinType.Unknown)
{
// table1, table2 ...
if (FromTable != null)
{
if (IsFirstInClause)
{
sb.Append(" ").Append(FromTable.TableName).Append(" ").Append(FromTable.AliasName);
}
else
{
sb.Append(",\r\n ").Append(FromTable.TableName).Append(" ").Append(FromTable.AliasName);
}
}
if (ToTable != null)
{
sb.Append(",\r\n ").Append(ToTable.TableName).Append(" ").Append(ToTable.AliasName);
}
else
{
sb.Append("\r\n");
}
}
else
{
// table1 inner join table2 ...
if (IsFirstInClause)
{
sb.Append(" ").Append(FromTable.TableName).Append(" ").Append(FromTable.AliasName);
object text = null;
if (FromTable.ExtendedProperties.TryGetValue("text", out text))
{
sb.Append(text as string);
}
}
if (ToTable != null)
{
if (IsFirstInClause)
{
sb.Append("\r\n");
}
sb.Append(" ").Append(JoinType.ToString().ToUpper()).Append(" JOIN ")
.Append(ToTable.TableName).Append(" ").Append(ToTable.AliasName);
object text = null;
if (ToTable.ExtendedProperties.TryGetValue("text", out text))
{
sb.Append(text as string);
}
sb.Append("\r\n ON ");
foreach (var cond in _conditionals)
{
sb.Append(cond.ToString());
}
}
}
var rv = sb.ToString();
return(rv);
// return FromTable.TableName + "." + FromField.TableFieldName + " " + JoinType + " join " + ToField.TableName + "." + ToField.TableFieldName;
}
protected internal override string GetFromAndJoinOn()
{
var dp = DatabaseProvider.Resolve(_sqlhelper._sqlType);
var pd1 = PocoData.ForType(typeof(T1));
var pd2 = PocoData.ForType(typeof(T2));
var pd3 = PocoData.ForType(typeof(T3));
var pd4 = PocoData.ForType(typeof(T4));
var pd5 = PocoData.ForType(typeof(T5));
StringBuilder sb = new StringBuilder();
sb.AppendFormat("FROM {0} AS t1 ", dp.GetTableName(pd1, _sqlhelper._tableNameManger));
if (string.IsNullOrEmpty(on2))
{
sb.AppendFormat(",{0} AS t2", dp.GetTableName(pd2, _sqlhelper._tableNameManger));
}
if (string.IsNullOrEmpty(on3))
{
sb.AppendFormat(",{0} AS t3", dp.GetTableName(pd3, _sqlhelper._tableNameManger));
}
if (string.IsNullOrEmpty(on4))
{
sb.AppendFormat(",{0} AS t4", dp.GetTableName(pd4, _sqlhelper._tableNameManger));
}
if (string.IsNullOrEmpty(on5))
{
sb.AppendFormat(",{0} AS t5", dp.GetTableName(pd5, _sqlhelper._tableNameManger));
}
if (string.IsNullOrEmpty(on2) == false)
{
sb.AppendFormat(" {1} JOIN {0} AS t2 ON {2}", dp.GetTableName(pd2, _sqlhelper._tableNameManger), join2.ToString(), on2);
}
if (string.IsNullOrEmpty(on3) == false)
{
sb.AppendFormat(" {1} JOIN {0} AS t3 ON {2}", dp.GetTableName(pd3, _sqlhelper._tableNameManger), join3.ToString(), on3);
}
if (string.IsNullOrEmpty(on4) == false)
{
sb.AppendFormat(" {1} JOIN {0} AS t4 ON {2}", dp.GetTableName(pd4, _sqlhelper._tableNameManger), join4.ToString(), on4);
}
if (string.IsNullOrEmpty(on5) == false)
{
sb.AppendFormat(" {1} JOIN {0} AS t5 ON {2}", dp.GetTableName(pd5, _sqlhelper._tableNameManger), join5.ToString(), on5);
}
sb.Append(" ");
sb.Append(_joinOnString);
return(sb.ToString());
}
public static Select Join(this Select select, JoinType joinType, string joinTo, string predicate)
{
return(select.Join($"{joinType.ToString().ToLowerInvariant()} join {joinTo} on {predicate}"));
}
