/// <summary>
/// Initializes a new instance of the <see cref="Select"/> class.
/// </summary>
/// <param name="provider">The provider.</param>
/// <param name="columns">The columns.</param>
public Select(IDataProvider provider, params string[] columns)
{
this.sqlFragment = new SqlFragment(_provider);
_provider = provider;
SelectColumnList = columns;
SQLCommand = this.sqlFragment.SELECT;
}
public static void FormatExpressionList(
IEnumerable <Expression> expressions,
SqlFragment parent,
ScriptBuilder scriptBuilder,
TSqlVisitor visitor,
string appendOptional = null)
{
int i = 0;
foreach (Expression expression in expressions)
{
if (expression == null)
{
continue;
}
AppendDelimiter(scriptBuilder, ref i);
scriptBuilder.AppendFragment(expression, parent, visitor);
if (appendOptional != null)
{
scriptBuilder.Append(appendOptional);
}
}
}
// 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 InCondition(SqlFragment columnOrExpr) : base()
{
// Build the fragment
AppendFragment(columnOrExpr).AppendText(" IN (");
Finalized = false;
HasAtLeastOneParameter = false;
}
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);
}
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));
}
// 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));
}
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 SqlBuffer(SqlFragment where, SqlFragment orderBy, int?skip, int?take)
{
this.Where = where;
this.OrderBy = orderBy;
this.Skip = skip;
this.Take = take;
}
public void SetName(string columnName)
{
ColumnName = columnName;
ColumnNameQuoted = Table.DbModel.Driver.SqlDialect.QuoteName(columnName);
base.LogRefName = DbModelHelper.JoinNames(Schema, Table.TableName, columnName);
SqlColumnNameQuoted = new TextSqlFragment(ColumnNameQuoted);
}
/// <inheritdoc/>
protected override SqlFragment GenerateForRelation(GenerateContext context, DbExpression content)
{
var data = (RelationContent)context.Data;
var metadata = data.Items.Navigate;
SqlFragment result = null;
if (!metadata.IsComposite)
{
result = GenerateForRelationUpdate(context);
}
else
{
if (data.IsAddRelation)
{
result = GenerateForRelationInsert(context);
}
else
{
result = GenerateForRelationDelete(context);
}
}
foreach (var member in data.CommitObject.Members.OfType <CommitMemberFragment>())
{
if (metadata.Target.ClrType == member.Property.DeclaringType)
{
member.Metadata = metadata.Target.Members[member.Property.Name];
}
else
{
member.Metadata = metadata.Source.Members[member.Property.Name];
}
}
return(result);
}
public CompositeComparison(SqlFragment leftSideColumnOrExpression, string operator1, object value1, string operator2, object value2)
{
this.AppendFragment(leftSideColumnOrExpression)
.AppendText(" " + operator1 + " ")
.AppendParameter(value1)
.AppendText(" " + operator2 + " ")
.AppendParameter(value2);
}
/// <summary>
/// Expressions the specified SQL expression.
/// </summary>
/// <param name="sqlExpression">The SQL expression.</param>
/// <returns></returns>
public Select Expression(string sqlExpression)
{
_provider = ProviderFactory.GetProvider();
this.sqlFragment = new SqlFragment(_provider);
SQLCommand = this.sqlFragment.SELECT;
Expressions.Add(sqlExpression);
return(this);
}
/// <summary>
/// Initializes a new instance of the <see cref="Select"/> class.
/// </summary>
/// <param name="aggregates">The aggregates.</param>
public Select(params Aggregate[] aggregates)
{
_provider = ProviderFactory.GetProvider();
this.sqlFragment = new SqlFragment(_provider);
SQLCommand = this.sqlFragment.SELECT;
foreach (Aggregate agg in aggregates)
{
Aggregates.Add(agg);
}
}
/// <summary>
/// Initializes a new instance of the <see cref="Select"/> class.
/// </summary>
/// <param name="provider">The provider.</param>
/// <param name="aggregates">The aggregates.</param>
public Select(IDataProvider provider, params Aggregate[] aggregates)
{
_provider = provider;
this.sqlFragment = new SqlFragment(_provider);
SQLCommand = this.sqlFragment.SELECT;
foreach (Aggregate agg in aggregates)
{
Aggregates.Add(agg);
}
}
// 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));
}
protected override SqlFragment BuildLimitSql(SqlFragment limit, SqlFragment offset)
{
if (limit == null)
{
return(_dialect.OffsetTemplate.Format(offset));
}
else
{
return(_dialect.OffsetLimitTemplate.Format(offset, limit));
}
}
public static void FormatAssignList(Assignment[] assignArray, SqlFragment parent, ScriptBuilder scriptBuilder, TSqlVisitor visitor)
{
int i = 0;
foreach (Assignment assign in assignArray) {
if (assign == null) {
continue;
}
AppendDelimiter(scriptBuilder, ref i);
scriptBuilder.AppendFragment(assign, parent, visitor);
}
}
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));
}
/// <summary>
/// Builds a IS NULL or a IS NOT NULL condition, according to <paramref name="isNotNull"/>
/// </summary>
/// <param name='column'>
/// The name of the column.
/// </param>
/// <param name='isNotNull'>
/// If set to true, creates a IS NOT NULL condition, otherwise it creates a IS NULL condition.
/// </param>
public NullCondition(SqlFragment columnOrExpr, bool isNotNull) : base()
{
// Build the fragment
if (isNotNull)
{
this.AppendFragment(columnOrExpr).AppendText(" IS NOT NULL");
}
else
{
this.AppendFragment(columnOrExpr).AppendText(" IS NULL");
}
}
} //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));
}
internal override object Clone(SqlNodeCloneContext context)
{
if (context.NodeMapping.ContainsKey(this))
{
return(context.NodeMapping[this]);
}
var expression = (SqlExpression)Expression.Clone(context);
var clone = new SqlFragment(expression);
context.NodeMapping[this] = clone;
return(clone);
}
}//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 SqlStatement BuildLinqDelete()
{
var whereList = Command.BaseSelect.Where;
SqlFragment sqlWhere = SqlTerms.Empty;
if (whereList.Count > 0)
{
sqlWhere = LinqSqlBuilder.BuildWhereClause(Command.BaseSelect, whereList);
}
var tblSql = Command.TargetTable.SqlFullName;
var deleteSql = SqlDialect.SqlCrudTemplateDelete.Format(tblSql, sqlWhere);
return(CreateNonQueryStatement(deleteSql, SqlKind.LinqDelete));
}
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 static void FormatColumnList(
IEnumerable<Column> columns,
SqlFragment parent,
ScriptBuilder scriptBuilder, TSqlVisitor visitor)
{
int i = 0;
foreach (Column column in columns) {
if (column == null) {
continue;
}
AppendDelimiter(scriptBuilder, ref i);
scriptBuilder.AppendFragment(column, parent, visitor);
}
}
public static void FormatAssignList(Assignment[] assignArray, SqlFragment parent, ScriptBuilder scriptBuilder, TSqlVisitor visitor)
{
int i = 0;
foreach (Assignment assign in assignArray)
{
if (assign == null)
{
continue;
}
AppendDelimiter(scriptBuilder, ref i);
scriptBuilder.AppendFragment(assign, parent, visitor);
}
}
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));
}
/// <inheritdoc/>
protected override SqlFragment GenerateForInsertContent(GenerateContext context, DbExpression content)
{
var data = (InsertContent)context.Data;
SqlFragment result = null;
RegisterExpressionForCommit(context, content, data.CommitObject);
if (data.Unit is CommitKeyUnit keyunit)
{
result = base.GenerateForInsertSimple(context, data);
}
else
{
result = base.GenerateForInsertIdentitySingle(context, data);
}
InitialCommitObject(data);
return(result);
}
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);
}
protected virtual SqlFragment BuildTableJoin(TableJoinType joinType, SqlFragment tableRef, SqlFragment joinCond)
{
SqlFragment joinDecl = null;
switch (joinType)
{
case TableJoinType.Inner: joinDecl = SqlTerms.InnerJoin; break;
case TableJoinType.LeftOuter: joinDecl = SqlTerms.LeftJoin; break;
case TableJoinType.RightOuter: joinDecl = SqlTerms.RightJoin; break;
default:
Util.Check(false, "Join type {0} not supported.", joinType);
break;
}
return(new CompositeSqlFragment(SqlTerms.NewLine, SqlTerms.Indent, joinDecl, tableRef, SqlTerms.On, joinCond));
}
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 static void FormatExpressionList(
IEnumerable<Expression> expressions,
SqlFragment parent,
ScriptBuilder scriptBuilder,
TSqlVisitor visitor,
string appendOptional = null)
{
int i = 0;
foreach (Expression expression in expressions) {
if (expression == null) {
continue;
}
AppendDelimiter(scriptBuilder, ref i);
scriptBuilder.AppendFragment(expression, parent, visitor);
if (appendOptional != null) {
scriptBuilder.Append(appendOptional);
}
}
}
public override void Build(SqlFragment parent, TSqlVisitor visitor)
{
visitor.Checksum_AggAllAggregateFunction(parent, this);
}
public override void Build(SqlFragment parent, TSqlVisitor visitor)
{
visitor.NotInSingleExprSelectStatementPredicate(parent, this);
}
public override void Build(SqlFragment parent, TSqlVisitor visitor)
{
visitor.DeleteTopFromTable(parent, this);
}
public override void Build(SqlFragment parent, TSqlVisitor visitor)
{
visitor.SystemExpression(parent, this);
}
public override void Build(SqlFragment parent, TSqlVisitor visitor)
{
visitor.VisitIsNullPredicate(parent, this);
}
public override void Build(SqlFragment parent, TSqlVisitor visitor)
{
visitor.IsGreaterThanPredicate(parent, this);
}
public override void Build(SqlFragment parent, TSqlVisitor visitor)
{
visitor.NullConstantExpression(parent, this);
}
public override void Build(SqlFragment parent, TSqlVisitor visitor)
{
visitor.AvgAggregateFunction(parent, this);
}
public override void Build(SqlFragment parent, TSqlVisitor visitor)
{
visitor.SingleExpSingleRowSelectStatementExpression(parent, this);
}
public override void Build(SqlFragment parent, TSqlVisitor visitor)
{
visitor.PlusArithmeticExpressionOperator(parent, this);
}
public override void Build(SqlFragment parent, TSqlVisitor visitor)
{
visitor.BetweenPredicate(parent, this);
}
public override void Build(SqlFragment parent, TSqlVisitor visitor)
{
visitor.InValuesPredicate(parent, this);
}
public override void Build(SqlFragment parent, TSqlVisitor visitor)
{
visitor.DefaultValueExpression(parent, this);
}
public override void Build(SqlFragment parent, TSqlVisitor visitor)
{
visitor.ConvertFunction(parent, this);
}
public abstract void Build(SqlFragment parent, TSqlVisitor visitor);
public override void Build(SqlFragment parent, TSqlVisitor visitor)
{
visitor.VisitAddAssign(parent, this);
}
public override void Build(SqlFragment parent, TSqlVisitor visitor)
{
visitor.UpdateTopTable(parent, this);
}
public override void Build(SqlFragment parent, TSqlVisitor visitor)
{
visitor.VisitIsLessThanOrEqualToPredicate(parent, this);
}
public override void Build(SqlFragment parent, TSqlVisitor visitor)
{
visitor.NotExistsPredicate(parent, this);
}
public override void Build(SqlFragment parent, TSqlVisitor visitor)
{
visitor.EndsWithPredicate(parent, this);
}
public override void Build(SqlFragment parent, TSqlVisitor visitor)
{
visitor.OrConditionalOperator(parent, this);
}
public override void Build(SqlFragment parent, TSqlVisitor visitor)
{
visitor.VisitVarbinaryDataType(parent, this);
}
public override void Build(SqlFragment parent, TSqlVisitor visitor)
{
visitor.TableValuedFunction(parent, this);
}
