protected override IBuildContext BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall,
BuildInfo buildInfo)
{
var sequenceExpr = methodCall.Arguments[0];
var sequence = builder.BuildSequence(new BuildInfo(buildInfo, sequenceExpr));
var groupingType = methodCall.Type.GetGenericArgumentsEx()[0];
var keySelector = (LambdaExpression)methodCall.Arguments[1].Unwrap();
var elementSelector = (LambdaExpression)methodCall.Arguments[2].Unwrap();
if (methodCall.Arguments[0].NodeType == ExpressionType.Call)
{
var call = (MethodCallExpression)methodCall.Arguments[0];
if (call.Method.Name == "Select")
{
var type = ((LambdaExpression)call.Arguments[1].Unwrap()).Body.Type;
if (type.IsGenericTypeEx() &&
type.GetGenericTypeDefinition() == typeof(ExpressionBuilder.GroupSubQuery <,>))
{
sequence = new SubQueryContext(sequence);
}
}
}
var key = new KeyContext(buildInfo.Parent, keySelector, sequence);
var groupSql = builder.ConvertExpressions(key, keySelector.Body.Unwrap(), ConvertFlags.Key);
if (sequence.Select.Select.IsDistinct ||
sequence.Select.GroupBy.Items.Count > 0 ||
groupSql.Any(_ => !(_.Sql is ISqlField || _.Sql is IColumn)))
{
sequence = new SubQueryContext(sequence);
key = new KeyContext(buildInfo.Parent, keySelector, sequence);
groupSql = builder.ConvertExpressions(key, keySelector.Body.Unwrap(), ConvertFlags.Key);
}
foreach (var sql in groupSql)
{
sequence.Select.GroupBy.Expr(sql.Sql);
}
foreach (
var join in
QueryVisitor.FindOnce <IJoinedTable>(sequence.Select.From).Where(f => f.JoinType == EJoinType.Inner))
{
join.IsWeak = false;
}
var element = new SelectContext(buildInfo.Parent, elementSelector, sequence /*, key*/);
var groupBy = new GroupByContext(buildInfo.Parent, sequenceExpr, groupingType, sequence, key, element);
return(groupBy);
}
private SelectQuery(ISelectQuery clone, Dictionary <ICloneableElement, ICloneableElement> objectTree,
Predicate <ICloneableElement> doClone)
{
objectTree.Add(clone, this);
SourceID = Interlocked.Increment(ref SourceIDCounter);
ICloneableElement parentClone;
if (clone.ParentSelect != null)
{
ParentSelect = objectTree.TryGetValue(clone.ParentSelect, out parentClone)
? (ISelectQuery)parentClone
: clone.ParentSelect;
}
EQueryType = clone.EQueryType;
if (IsInsert)
{
Insert = (IInsertClause)clone.Insert.Clone(objectTree, doClone);
}
if (IsUpdate)
{
Update = (IUpdateClause)clone.Update.Clone(objectTree, doClone);
}
if (IsDelete)
{
Delete = (IDeleteClause)clone.Delete.Clone(objectTree, doClone);
}
Select = new SelectClause(this, clone.Select, objectTree, doClone);
From = new FromClause(this, clone.From, objectTree, doClone);
Where = new WhereClause(this, clone.Where, objectTree, doClone);
GroupBy = new GroupByClause(this, clone.GroupBy, objectTree, doClone);
Having = new WhereClause(this, clone.Having, objectTree, doClone);
OrderBy = new OrderByClause(this, clone.OrderBy, objectTree, doClone);
Parameters.AddRange(clone.Parameters.Select(p => (ISqlParameter)p.Clone(objectTree, doClone)));
IsParameterDependent = clone.IsParameterDependent;
foreach (var query in QueryVisitor.FindOnce <ISelectQuery>(this).Where(sq => sq.ParentSelect == clone))
{
query.ParentSelect = this;
}
}
public override ISelectQuery Finalize(ISelectQuery selectQuery)
{
CheckAliases(selectQuery, 30);
foreach (var parameter in QueryVisitor.FindOnce <ISqlParameter>(selectQuery.Select))
{
parameter.IsQueryParameter = false;
}
selectQuery = base.Finalize(selectQuery);
switch (selectQuery.EQueryType)
{
case EQueryType.Delete:
return(GetAlternativeDelete(selectQuery));
case EQueryType.Update:
return(GetAlternativeUpdate(selectQuery));
default:
return(selectQuery);
}
}
public void SetAliases()
{
_aliases = null;
var objs = new HashSet <IQueryElement>();
Parameters.Clear();
foreach (var element in QueryVisitor.FindOnce <IQueryElement>(this))
{
switch (element.ElementType)
{
case EQueryElementType.SqlParameter:
{
var p = (ISqlParameter)element;
if (p.IsQueryParameter)
{
if (!objs.Contains(element))
{
objs.Add(element);
p.Name = GetAlias(p.Name, "p");
}
Parameters.Add(p);
}
else
{
IsParameterDependent = true;
}
}
break;
case EQueryElementType.Column:
{
if (!objs.Contains(element))
{
objs.Add(element);
var c = (IColumn)element;
if (c.Alias != "*")
{
c.Alias = GetAlias(c.Alias, "c");
}
}
}
break;
case EQueryElementType.TableSource:
{
var table = (ITableSource)element;
if (!objs.Contains(table))
{
objs.Add(table);
table.Alias = GetAlias(table.Alias, "t");
}
}
break;
case EQueryElementType.SqlQuery:
{
var sql = (ISelectQuery)element;
if (sql.HasUnion)
{
for (var i = 0; i < sql.Select.Columns.Count; i++)
{
var col = sql.Select.Columns[i];
var index = i;
sql.Unions.ForEach(
node =>
{
var union = node.Value.SelectQuery.Select;
objs.Remove(union.Columns[index]);
union.Columns[index].Alias = col.Alias;
});
}
}
break;
}
}
}
}
public ISelectQuery ProcessParameters()
{
if (!IsParameterDependent)
{
return(this);
}
var query = new QueryVisitor().Convert(
this,
e =>
{
switch (e.ElementType)
{
case EQueryElementType.SqlParameter:
{
var p = (ISqlParameter)e;
if (p.Value == null)
{
return(new SqlValue(null));
}
}
break;
case EQueryElementType.ExprExprPredicate:
{
var ee = (IExprExpr)e;
if (ee.EOperator == EOperator.Equal || ee.EOperator == EOperator.NotEqual)
{
object value1;
object value2;
var expr1 = ee.Expr1 as ISqlValue;
if (expr1 != null)
{
value1 = expr1.Value;
}
else
{
var parameter = ee.Expr1 as ISqlParameter;
if (parameter != null)
{
value1 = parameter.Value;
}
else
{
break;
}
}
var sqlValue = ee.Expr2 as ISqlValue;
if (sqlValue != null)
{
value2 = sqlValue.Value;
}
else
{
var parameter = ee.Expr2 as ISqlParameter;
if (parameter != null)
{
value2 = parameter.Value;
}
else
{
break;
}
}
var value = Equals(value1, value2);
if (ee.EOperator == EOperator.NotEqual)
{
value = !value;
}
return(new Expr(new SqlValue(value), SqlQuery.Precedence.Comparison));
}
}
break;
case EQueryElementType.InListPredicate:
return(ConvertInListPredicate((IInList)e));
}
return(null);
});
if (query == this)
{
return(query);
}
query.Parameters.Clear();
foreach (var parameter in QueryVisitor.FindOnce <ISqlParameter>(query).Where(p => p.IsQueryParameter))
{
query.Parameters.Add(parameter);
}
return(query);
}
protected virtual bool BuildCommand <T>(
DataConnection dataConnection, Expression <Func <T, bool> > deletePredicate, bool delete, IEnumerable <T> source,
string tableName, string databaseName, string schemaName)
where T : class
{
var table = dataConnection.MappingSchema.GetEntityDescriptor(typeof(T));
var sqlBuilder = dataConnection.DataProvider.CreateSqlBuilder();
Columns = table.Columns
.Select(c => new ColumnInfo
{
Column = c,
Name = (string)sqlBuilder.Convert(c.ColumnName, ConvertType.NameToQueryField)
})
.ToList();
StringBuilder.Append("MERGE INTO ");
sqlBuilder.BuildTableName(StringBuilder,
(string)sqlBuilder.Convert(databaseName ?? table.DatabaseName, ConvertType.NameToDatabase),
(string)sqlBuilder.Convert(schemaName ?? table.SchemaName, ConvertType.NameToOwner),
(string)sqlBuilder.Convert(tableName ?? table.TableName, ConvertType.NameToQueryTable));
StringBuilder
.AppendLine(" Target")
;
if (!BuildUsing(dataConnection, source))
{
return(false);
}
StringBuilder
.AppendLine("ON")
.AppendLine("(")
;
foreach (var column in Columns.Where(c => c.Column.IsPrimaryKey))
{
StringBuilder
.AppendFormat("\tTarget.{0} = Source.{0} AND", column.Name)
.AppendLine()
;
}
StringBuilder.Length -= 4 + Environment.NewLine.Length;
StringBuilder
.AppendLine()
.AppendLine(")")
;
var updateColumns =
Columns.Where(
c =>
!c.Column.IsPrimaryKey && (IsIdentitySupported && c.Column.IsIdentity || !c.Column.SkipOnUpdate))
.ToList();
if (updateColumns.Count > 0)
{
StringBuilder
.AppendLine("-- update matched rows")
.AppendLine("WHEN MATCHED THEN")
.AppendLine("\tUPDATE")
.AppendLine("\tSET")
;
var maxLen = updateColumns.Max(c => c.Name.Length);
foreach (var column in updateColumns)
{
StringBuilder
.AppendFormat("\t\t{0} ", column.Name)
;
StringBuilder.Append(' ', maxLen - column.Name.Length);
StringBuilder
.AppendFormat("= Source.{0},", column.Name)
.AppendLine()
;
}
StringBuilder.Length -= 1 + Environment.NewLine.Length;
}
var insertColumns =
Columns.Where(c => IsIdentitySupported && c.Column.IsIdentity || !c.Column.SkipOnInsert).ToList();
StringBuilder
.AppendLine()
.AppendLine("-- insert new rows")
.Append("WHEN NOT MATCHED ").Append(ByTargetText).AppendLine("THEN")
.AppendLine("\tINSERT")
.AppendLine("\t(")
;
foreach (var column in insertColumns)
{
StringBuilder.AppendFormat("\t\t{0},", column.Name).AppendLine();
}
StringBuilder.Length -= 1 + Environment.NewLine.Length;
StringBuilder
.AppendLine()
.AppendLine("\t)")
.AppendLine("\tVALUES")
.AppendLine("\t(")
;
foreach (var column in insertColumns)
{
StringBuilder.AppendFormat("\t\tSource.{0},", column.Name).AppendLine();
}
StringBuilder.Length -= 1 + Environment.NewLine.Length;
StringBuilder
.AppendLine()
.AppendLine("\t)")
;
if (delete)
{
var predicate = "";
if (deletePredicate != null)
{
var inlineParameters = dataConnection.InlineParameters;
try
{
dataConnection.InlineParameters = true;
var q = dataConnection.GetTable <T>().Where(deletePredicate);
var ctx = q.GetContext();
var sql = ctx.Select;
var tableSet = new HashSet <ISqlTable>();
var tables = new List <ISqlTable>();
var fromTable = (ISqlTable)sql.From.Tables.First.Value.Source;
foreach (var tableSource in QueryVisitor.FindOnce <ITableSource>(sql.From))
{
tableSet.Add((ISqlTable)tableSource.Source);
tables.Add((ISqlTable)tableSource.Source);
}
var whereClause = new QueryVisitor().Convert(sql.Where, e =>
{
if (e.ElementType == EQueryElementType.SqlQuery)
{
}
if (e.ElementType == EQueryElementType.SqlField)
{
var fld = (ISqlField)e;
var tbl = (ISqlTable)fld.Table;
if (tbl != fromTable && tableSet.Contains(tbl))
{
var tempCopy = sql.Clone();
var tempTables = new List <ITableSource>();
tempTables.AddRange(QueryVisitor.FindOnce <ITableSource>(tempCopy.From));
var tt = tempTables[tables.IndexOf(tbl)];
tempCopy.Select.Columns.Clear();
tempCopy.Select.Add(((SqlTable)tt.Source).Fields[fld.Name]);
tempCopy.Where.Search.Conditions.Clear();
var keys = tempCopy.From.Tables.First.Value.Source.GetKeys(true);
foreach (ISqlField key in keys)
{
tempCopy.Where.Field(key).Equal.Field(fromTable.Fields[key.Name]);
}
tempCopy.ParentSelect = sql;
return(tempCopy);
}
}
return(e);
}).Search.Conditions.ToList();
sql.Where.Search.Conditions.Clear();
sql.Where.Search.Conditions.AddRange(whereClause);
sql.From.Tables.First.Value.Alias = "Target";
ctx.SetParameters();
var pq =
(DataConnection.PreparedQuery)((IDataContext)dataConnection).SetQuery(new QueryContext
{
SelectQuery = sql,
SqlParameters = sql.Parameters.ToArray()
});
var cmd = pq.Commands[0];
predicate = "AND " + cmd.Substring(cmd.IndexOf("WHERE") + "WHERE".Length);
}
finally
{
dataConnection.InlineParameters = inlineParameters;
}
}
StringBuilder
.AppendLine("-- delete rows that are in the target but not in the sourse")
.AppendLine("WHEN NOT MATCHED BY Source {0}THEN".Args(predicate))
.AppendLine("\tDELETE")
;
}
return(true);
}
