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)); }
private void AppendRowVersionCheckReturn(SqlStatement sql, DbTableInfo table, EntityRecord record) { var rvCol = table.Columns.First(c => c.Flags.IsSet(DbColumnFlags.RowVersion)); // do row count check for update only, not for insert if (record.Status == EntityStatus.Modified) { var tag = new TextSqlFragment($"'ConcurrentUpdate/{table.Entity.Name}/{record.PrimaryKey.ValuesToString()}'"); var checkRowsSql = _msDialect.SqlCheckRowCountIsOne.Format(tag); sql.Append(checkRowsSql); } // return RowVersion in parameter var rvPrmPholder = new SqlColumnValuePlaceHolder(rvCol, ParameterDirection.InputOutput); sql.PlaceHolders.Add(rvPrmPholder); rvPrmPholder.PreviewParameter = (prm, ph) => { prm.DbType = DbType.Binary; prm.Size = 8; }; var getRvSql = _msDialect.SqlGetRowVersionTemplate.Format(rvPrmPholder); sql.Append(getRvSql); sql.Append(SqlTerms.NewLine); }