protected SqlFragment BuildWhereConditonForUpdateDeleteOne(DbTableInfo table, SqlPlaceHolderList placeHolders)
{
var pkCols = table.PrimaryKey.KeyColumns;
var hasRowVersion = table.Entity.Flags.HasFlag(EntityFlags.HasRowVersion);
SqlPlaceHolder colPh;
// short way for one-column PK
if (pkCols.Count == 1 && !hasRowVersion)
{
var pkCol = pkCols[0].Column;
colPh = new SqlColumnValuePlaceHolder(pkCol);
placeHolders.Add(colPh);
return(new CompositeSqlFragment(pkCol.SqlColumnNameQuoted, SqlTerms.Equal, colPh));
}
//general case:
// add row version to column list if there's row version. We must compare row version in WHERE clause
var allCols = pkCols.Select(kc => kc.Column).ToList();
if (hasRowVersion)
{
var rvCol = table.Columns.First(c => c.Flags.IsSet(DbColumnFlags.RowVersion));
allCols.Add(rvCol);
}
var conds = new List <SqlFragment>();
foreach (var col in allCols)
{
var colSql = new SqlColumnValuePlaceHolder(col);
placeHolders.Add(colSql);
conds.Add(new CompositeSqlFragment(col.SqlColumnNameQuoted, SqlTerms.Equal, colSql));
}
return(SqlFragment.CreateList(SqlTerms.And, conds));
}
// InsertMany will never be reused, so we prefer to use literals, not parameters
public virtual SqlStatement BuildCrudInsertMany(DbTableInfo table, IList <EntityRecord> records, IColumnValueFormatter formatter)
{
// list of column placeholders
var insertColPhs = GetColumnPlaceholdersForInsert(table, records);
//values rows
var rowFragments = new List <SqlFragment>();
var colValueSqls = new List <SqlFragment>();
foreach (var rec in records)
{
colValueSqls.Clear();
foreach (var colPh in insertColPhs)
{
colValueSqls.Add(new TextSqlFragment(formatter.FormatColumnValuePlaceHolder(colPh, rec)));
}
var valuesRow = CompositeSqlFragment.Parenthesize(SqlFragment.CreateList(SqlTerms.Comma, colValueSqls));
rowFragments.Add(valuesRow);
}
// assemble SQL
// column list
var insertColsSqls = insertColPhs.Select(ph => ph.Column.SqlColumnNameQuoted).ToList();
var colListSql = SqlFragment.CreateList(SqlTerms.Comma, insertColsSqls);
// values rows
var allValuesSql = SqlFragment.CreateList(SqlTerms.CommaNewLineIndent, rowFragments);
var sql = SqlDialect.SqlCrudTemplateInsert.Format(table.SqlFullName, colListSql, allValuesSql);
// assemble Insert statement
var stmt = new SqlStatement(SqlKind.InsertMany, sql, null, DbExecutionType.NonQuery, null, QueryOptions.NoQueryCache);
return(stmt);
}
public virtual SqlFragment BuildGroupByClause(SelectExpression selectExpression)
{
var exprs = selectExpression.Group;
if (exprs.Count == 0)
{
return(null);
}
var parts = new List <SqlFragment>();
foreach (var ge in exprs)
{
foreach (var c in ge.Columns)
{
parts.Add(BuildLinqExpressionSql(c));
}
}
// this might happen with fake grouping to get aggregates on entire table
if (parts.Count == 0)
{
return(null);
}
var gbList = SqlFragment.CreateList(SqlTerms.Comma, parts);
return(new CompositeSqlFragment(SqlTerms.GroupBy, gbList));
}
public virtual SqlStatement BuildCrudUpdateOne(DbTableInfo table, EntityRecord rec)
{
var placeHolders = new SqlPlaceHolderList();
// find modified columns
var setExprs = new List <SqlFragment>();
foreach (var col in table.UpdatableColumns)
{
if (!rec.IsValueChanged(col.Member))
{
continue;
}
var valueSql = new SqlColumnValuePlaceHolder(col);
placeHolders.Add(valueSql);
setExprs.Add(new CompositeSqlFragment(col.SqlColumnNameQuoted, SqlTerms.Equal, valueSql));
}
var setList = SqlFragment.CreateList(SqlTerms.Comma, setExprs);
var whereCond = BuildWhereConditonForUpdateDeleteOne(table, placeHolders);
var whereClause = new CompositeSqlFragment(SqlTerms.Where, whereCond);
var sql = SqlDialect.SqlCrudTemplateUpdate.Format(table.SqlFullName, setList, whereClause);
var stmt = new SqlStatement(SqlKind.UpdateOne, sql, placeHolders, DbExecutionType.NonQuery,
SqlDialect.PrecedenceHandler, QueryOptions.NoQueryCache);
return(stmt);
}
// Builds one-table update
public virtual SqlStatement BuildLinqUpdateSimple()
{
var setValueClauses = new List <SqlFragment>();
for (int i = 0; i < Command.TargetColumns.Count; i++)
{
var col = Command.TargetColumns[i];
bool isPk = col.Flags.IsSet(DbColumnFlags.PrimaryKey);
if (isPk)
{
continue; //we ignore PK columns
}
var colNamePart = col.SqlColumnNameQuoted;
var outExprPart = LinqSqlBuilder.BuildLinqExpressionSql(Command.SelectOutputValues[i]);
var clause = SqlDialect.SqlTemplateColumnAssignValue.Format(colNamePart, outExprPart);
setValueClauses.Add(clause);
}
var setClause = SqlFragment.CreateList(SqlTerms.Comma, setValueClauses);
var where = Command.BaseSelect.Where;
var sqlWhere = LinqSqlBuilder.BuildWhereClause(Command.BaseSelect, where);
var tablePart = Command.TargetTable.SqlFullName;
var sqlUpdate = SqlDialect.SqlCrudTemplateUpdate.Format(tablePart, setClause, sqlWhere);
return(CreateNonQueryStatement(sqlUpdate, SqlKind.LinqUpdate));
}
// Builds one-table update
public virtual SqlStatement BuildLinqInsert()
{
var tblName = Command.TargetTable.SqlFullName;
var colParts = Command.TargetColumns.Select(c => c.SqlColumnNameQuoted).ToList();
var colList = SqlFragment.CreateList(SqlTerms.Comma, colParts);
var selectSql = LinqSqlBuilder.BuildSelectSql(Command.BaseSelect);
var sqlInsert = this.SqlDialect.SqlLinqTemplateInsertFromSelect.Format(tblName, colList, selectSql);
return(CreateNonQueryStatement(sqlInsert, SqlKind.LinqInsert));
}
public override SqlFragment BuildSqlForSqlFunctionExpression(SqlFunctionExpression expr)
{
switch (expr.FunctionType)
{
case SqlFunctionType.Concat:
// there can be multiple args, > 2, can't use template here
var argSqls = BuildSqls(expr.Operands);
var args = SqlFragment.CreateList(SqlTerms.Comma, argSqls);
return(_msDialect.ConcatTemplate.Format(args));
}
return(base.BuildSqlForSqlFunctionExpression(expr));
}
} //method
public override SqlFragment BuildSqlForSqlFunctionExpression(SqlFunctionExpression expr)
{
switch (expr.FunctionType)
{
case SqlFunctionType.Concat:
// there can be multiple args, > 2, can't use template here
var argSqls = BuildSqls(expr.Operands);
var list = SqlFragment.CreateList(_oracleDialect.ConcatOperator, argSqls);
return(CompositeSqlFragment.Parenthesize(list));
}
return(base.BuildSqlForSqlFunctionExpression(expr));
}
}//class
public virtual SqlFragment BuildSqlForSqlFunctionExpressionNoTemplate(SqlFunctionExpression expr)
{
//concat - special case; turn all args into comma-delimited list
if (expr.FunctionType == SqlFunctionType.Concat)
{
var args = expr.GetOperands();
var sqlArgs = args.Select(a => BuildLinqExpressionSql(a)).ToList();
var argsPart = SqlFragment.CreateList(SqlDialect.SqlConcatListDelimiter, sqlArgs);
return(SqlDialect.SqlTemplateConcatMany.Format(argsPart));
}
Util.Throw("Unsupported SqlFunction type: {0}, expr: {1} ", expr.FunctionType, expr);
return(null);
}
public virtual SqlFragment BuildSelectOutputClause(SelectExpression select)
{
var flist = new List <SqlFragment>();
flist.Add(SqlTerms.Select);
if (select.Flags.IsSet(SelectExpressionFlags.Distinct))
{
flist.Add(SqlTerms.Distinct);
}
var outCols = BuildSelectOutputList(select);
flist.Add(outCols);
return(SqlFragment.CreateList(SqlTerms.Space, flist));
}
public virtual SqlFragment BuildWhereClause(SelectExpression selectExpression, IList <Expression> wheres)
{
if (wheres.Count == 0)
{
return(null);
}
var whereParts = new List <SqlFragment>();
foreach (var whereExpression in wheres)
{
whereParts.Add(BuildLinqExpressionSql(whereExpression));
}
var whereAll = whereParts.Count == 1 ? whereParts[0] : SqlFragment.CreateList(SqlTerms.And, whereParts);
return(new CompositeSqlFragment(SqlTerms.Where, whereAll));
}
public virtual SqlFragment BuildSelectOutputList(SelectExpression select)
{
var outCols = new List <SqlFragment>();
if (select.Flags.IsSet(SelectExpressionFlags.Distinct))
{
//RI: changed code
var g = select.Group[0];
foreach (var col in g.Columns)
{
var sqlCol = GetColumnRefSql(col, forOutput: true);
outCols.Add(sqlCol);
}
var outColsFragment = SqlFragment.CreateList(SqlTerms.Comma, outCols);
return(outColsFragment);
}
//Regular
var ops = select.GetOperands().ToList();
// No explicit columns
if (ops.Count == 0)
{
if (select.Group.Count > 0)
{
return(this.SqlDialect.SqlNullAsEmpty);
}
else
{
return(SqlTerms.Star);
}
}
foreach (var outExpr in ops)
{
var outSql = BuildLinqExpressionSql(outExpr);
var alias = GetAlias(outExpr);
if (!string.IsNullOrEmpty(alias))
{
var aliasPart = new TextSqlFragment(SqlDialect.QuoteName(alias));
outSql = new CompositeSqlFragment(outSql, SqlTerms.As, aliasPart);
}
outCols.Add(outSql);
}
var outColsPart = SqlFragment.CreateList(SqlTerms.Comma, outCols);
return(outColsPart);
}
public virtual SqlFragment BuildHavingClause(SelectExpression selectExpression)
{
var exprs = selectExpression.Having;
if (exprs.Count == 0)
{
return(null);
}
var parts = new List <SqlFragment>();
foreach (var expr in exprs)
{
parts.Add(BuildLinqExpressionSql(expr));
}
//TODO: rework this with explicit merge of exprs using AND expr
var all = SqlFragment.CreateList(SqlTerms.And, parts);
return(new CompositeSqlFragment(SqlTerms.Having, all));
}
public virtual SqlStatement BuildLinqUpdateWithSubquery()
{
Util.Check(DbModel.Driver.Supports(Data.Driver.DbFeatures.UpdateFromSubQuery),
"The database server does not support UPDATE statements with 'FROM <subquery>' clause, cannot translate this LINQ query.");
var setValueClauses = new List <SqlFragment>();
var whereExprSqls = new List <SqlFragment>();
for (int i = 0; i < Command.TargetColumns.Count; i++)
{
var outExpr = Command.SelectOutputValues[i] as SqlExpression;
var col = Command.TargetColumns[i];
// change alias on PK columns - this is necessary to avoid ambiguous names. MS SQL does not like this
bool isPk = col.Flags.IsSet(DbColumnFlags.PrimaryKey);
if (isPk)
{
outExpr.Alias += "_";
}
var colNamePart = col.SqlColumnNameQuoted;
var outExprAliasPart = new TextSqlFragment(SqlDialect.QuoteName(outExpr.Alias));
var equalExprSql = SqlDialect.SqlTemplateColumnAssignAliasValue.Format(colNamePart, _fromAlias, outExprAliasPart);
if (isPk)
{
whereExprSqls.Add(equalExprSql);
}
else
{
setValueClauses.Add(equalExprSql);
}
}
var setClause = SqlFragment.CreateList(SqlTerms.Comma, setValueClauses);
SqlFragment whereClause = SqlTerms.Empty;
if (whereExprSqls.Count > 0)
{
var whereCond = SqlFragment.CreateList(SqlTerms.And, whereExprSqls);
whereClause = new CompositeSqlFragment(SqlTerms.Where, whereCond);
}
var fromClauseSql = LinqSqlBuilder.BuildSelectSql(Command.BaseSelect);
var tableNameSql = Command.TargetTable.SqlFullName;
var sqlUpdate = SqlDialect.SqlCrudTemplateUpdateFrom.Format(tableNameSql, setClause, fromClauseSql, _fromAlias, whereClause);
return(CreateNonQueryStatement(sqlUpdate, SqlKind.LinqUpdate));
}
// Inject TOP() clause
public override SqlFragment BuildSelectOutputClause(SelectExpression select)
{
var flist = new List <SqlFragment>();
flist.Add(SqlTerms.Select);
if (select.Flags.IsSet(SelectExpressionFlags.MsSqlUseTop))
{
var limitSql = BuildLinqExpressionSql(select.Limit);
var topSql = _msDialect.TopTemplate.Format(limitSql);
flist.Add(topSql);
}
if (select.Flags.IsSet(SelectExpressionFlags.Distinct))
{
flist.Add(SqlTerms.Distinct);
}
var outCols = BuildSelectOutputList(select);
flist.Add(outCols);
return(SqlFragment.CreateList(SqlTerms.Space, flist));
}
public virtual SqlFragment BuildSelectSql(SelectExpression translatedSelect)
{
var lockType = translatedSelect.Command.LockType;
translatedSelect = PreviewSelect(translatedSelect, lockType);
var selectOut = BuildSelectOutputClause(translatedSelect);
var tables = GetSortedTables(translatedSelect);
var from = BuildFromClause(translatedSelect, tables);
var where = BuildWhereClause(translatedSelect, translatedSelect.Where);
var groupBy = BuildGroupByClause(translatedSelect);
var having = BuildHavingClause(translatedSelect);
var orderBy = BuildOrderByClause(translatedSelect);
var lockClause = BuildLockClause(translatedSelect, lockType);
var limit = BuildLimitClause(translatedSelect);
var all = (new SqlFragment[] { selectOut, from, where, groupBy, having, orderBy, lockClause, limit })
.Where(f => f != null).ToList();
var sql = SqlFragment.CreateList(SqlTerms.NewLine, all);
return(sql);
}
public virtual SqlFragment BuildOrderByClause(SelectExpression select)
{
//Special case - fake OrderBy clause 'ORDER BY (SELECT 1)', used in SQLs with Take/skip in MS SQL, Postgres
if (select.Flags.IsSet(SelectExpressionFlags.NeedsFakeOrderBy))
{
return(this.SqlDialect.SqlFakeOrderByClause);
}
if (select.OrderBy.Count == 0)
{
return(null);
}
var parts = new List <SqlFragment>();
foreach (var e in select.OrderBy)
{
parts.Add(BuildLinqExpressionSql(e));
}
var all = SqlFragment.CreateList(SqlTerms.Comma, parts);
return(this.SqlDialect.SqlTemplateOrderBy.Format(all));
}
public virtual SqlFragment BuildSqlForStandardExpression(Expression expr)
{
var args = expr.GetOperands();
var sqlArgs = args.Select(a => BuildLinqExpressionSql(a)).ToArray();
switch (expr)
{
case ConstantExpression ce:
return(GetConstantLiteral(ce.Value, ce.Type));
case NewExpression ne:
//RI: We might have NewExpression here! Query: (from b in books select new {b.Title}).Count();
// in this case the NewExpression is 'hidden' inside subquery and it is not visible to CutOutOperands
// We just return list of arguments (columns) of New expression
return(SqlFragment.CreateList(SqlTerms.Comma, sqlArgs));
case UnaryExpression ue:
if (ue.NodeType == ExpressionType.Convert || ue.NodeType == ExpressionType.ConvertChecked)
{
if (this.SqlDialect.IsConversionRequired(ue))
{
return(BuildConvertSql(ue));
}
else
{
return(BuildLinqExpressionSql(ue.Operand)); //no conversion
}
}
break;
}
//Binary, unary expr based on templates
var template = SqlDialect.GetExpressionTemplate(expr);
if (template != null)
{
return(template.Format(sqlArgs));
}
Util.Throw("Not supported expression, cannot convert to SQL, expr type {0}, expr: {1} ", expr.NodeType, expr);
return(null);
}//method
public virtual SqlStatement BuildCrudInsertOne(DbTableInfo table, EntityRecord record)
{
// list of column names
var insertCols = GetColumnsToInsert(table, record);
var insertColsSqls = insertCols.Select(c => c.SqlColumnNameQuoted).ToList();
var colListSql = SqlFragment.CreateList(SqlTerms.Comma, insertColsSqls);
// values and placeholders
var placeHolders = new SqlPlaceHolderList();
var colSqls = new List <SqlFragment>();
foreach (var insertCol in insertCols)
{
var ph = new SqlColumnValuePlaceHolder(insertCol);
placeHolders.Add(ph);
colSqls.Add(ph);
}
var valuesFragm = CompositeSqlFragment.Parenthesize(SqlFragment.CreateList(SqlTerms.Comma, colSqls));
// format SQL
var sql = SqlDialect.SqlCrudTemplateInsert.Format(table.SqlFullName, colListSql, valuesFragm);
var stmt = new SqlStatement(SqlKind.InsertOne, sql, placeHolders, DbExecutionType.NonQuery);
return(stmt);
}
public virtual SqlFragment BuildSelectSql(SelectExpression select)
{
var sql = BuildSelectSqlSimple(select);
if (select.ChainedSet == null)
{
return(sql);
}
// build multi-set SQL (UNION of multiple)
var sqls = new List <SqlFragment>();
sqls.Add(sql);
var nextSet = select.ChainedSet;
while (nextSet != null)
{
sqls.Add(SqlDialect.GetMultiSetOperator(nextSet.MultiSetType));
sqls.Add(BuildSelectSqlSimple(nextSet.ChainedSelect));
nextSet = nextSet.ChainedSelect.ChainedSet;
}
var resultSql = SqlFragment.CreateList(SqlTerms.NewLine, sqls);
return(resultSql);
}
