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);
}
// 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 SqlStatement BuildCrudDeleteOne(DbTableInfo table)
{
var placeHolders = new SqlPlaceHolderList();
var whereCond = BuildWhereConditonForUpdateDeleteOne(table, placeHolders);
var whereClause = new CompositeSqlFragment(SqlTerms.Where, whereCond);
var sql = SqlDialect.SqlCrudTemplateDelete.Format(table.SqlFullName, whereClause);
var stmt = new SqlStatement(SqlKind.DeleteOne, sql, placeHolders, DbExecutionType.NonQuery);
return(stmt);
}
} //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));
}
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 GetColumnRefSql(ColumnExpression column, bool forOutput)
{
var colPart = new TextSqlFragment(column.ColumnInfo.ColumnNameQuoted);
var tbl = column.Table;
SqlFragment result = colPart;
// finish this with col alias
if (tbl.HasAlias())
{
result = new CompositeSqlFragment(tbl.GetAliasSql(), SqlTerms.Dot, colPart);
}
if (forOutput && column.HasAlias())
{
var colAlias = new TextSqlFragment(column.Alias);
result = new CompositeSqlFragment(result, colAlias);
}
return(result);
}
public virtual SqlFragment BuildTableForFrom(TableExpression te)
{
SqlFragment result;
if (te is SubSelectExpression)
{
result = BuildSqlForSqlExpression(te);
}
else
{
result = new TextSqlFragment(te.TableInfo.FullName);
}
if (te.HasAlias())
{
result = new CompositeSqlFragment(result, SqlTerms.Space, te.GetAliasSql());
}
return(result);
}
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));
}
public override SqlFragment BuildTableForFrom(TableExpression te)
{
var tableRef = base.BuildTableForFrom(te);
SqlFragment hint = null;
switch (te.LockType)
{
case LockType.ForUpdate: hint = _msDialect.WithUpdateLockHint; break;
case LockType.NoLock: hint = _msDialect.WithNoLockHint; break;
case LockType.SharedRead: break;//turns out we do not need any hint here if we run in Snapshot level
}
//Note: for MS SQL, table alias (if present) goes before the hint ( ... FROM Tbl t WITH(NOLOCK) ...), so it works as coded
if (hint != null)
{
tableRef = new CompositeSqlFragment(tableRef, hint);
}
return(tableRef);
}
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);
}
}//method
public virtual SqlFragment BuildSqlForSqlExpression(SqlExpression expr)
{
switch (expr.SqlNodeType)
{
case SqlExpressionType.Alias:
var aliasExpr = (AliasedExpression)expr;
return(BuildLinqExpressionSql(aliasExpr.Expression)); //Alias will be added later
case SqlExpressionType.SqlFunction:
return(BuildSqlForSqlFunctionExpression((SqlFunctionExpression)expr));
case SqlExpressionType.Column:
var colExpr = (ColumnExpression)expr;
return(GetColumnRefSql(colExpr, forOutput: false));
case SqlExpressionType.SubSelect:
var subs = (SubSelectExpression)expr;
var subSelect = BuildSqlForSqlExpression(subs.Select);
return(CompositeSqlFragment.Parenthesize(subSelect));
case SqlExpressionType.Table:
var te = (TableExpression)expr;
return(te.TableInfo.SqlFullName);
case SqlExpressionType.TableFilter:
var tfe = (TableFilterExpression)expr;
var argParts = BuildSqls(tfe.Columns);
var sqlTempl = new SqlTemplate(tfe.Filter.EntityFilter.Template.StandardForm);
var part = sqlTempl.Format(argParts);
return(part);
case SqlExpressionType.ExternalValue:
var extValue = (ExternalValueExpression)expr;
var ph = CreateSqlPlaceHolder(extValue);
// it is already added to placeholders list
return(ph);
case SqlExpressionType.Group:
var ge = (GroupExpression)expr;
return(BuildLinqExpressionSql(ge.GroupedExpression));
case SqlExpressionType.OrderBy:
return(BuildOrderByMember((OrderByExpression)expr));
case SqlExpressionType.Aggregate:
return(BuildAggregateSql((AggregateExpression)expr));
case SqlExpressionType.Select:
var selectSql = BuildSelectSql((SelectExpression)expr);
return(CompositeSqlFragment.Parenthesize(selectSql));
case SqlExpressionType.DerivedTable:
// Looks like we never come here
//!!! investigate this
//TODO: investigate DerivedTable SQL
return(SqlTerms.Star);
default:
Util.Throw("SqlExpression->SQL not implemented, SqlNodeType: {0}", expr.SqlNodeType);
return(null); //never happens
}//switch
}//method
