public static Statement CreateStatement(Select select, IEntityMapping mapping, IEnumerable <string> projectionOverride = null, bool noLock = false) { var sql = new SqlWriter(); var parameters = new Dictionary <string, object>(); var projection = projectionOverride ?? BuildProjection(select, mapping, parameters); var whereClause = BuildWhereClause(select, mapping, parameters); var orderByClause = select.HasOrderBy ? BuildOrderBy(select.OrderBy, mapping, parameters) : null; Action <SqlWriter> writeProjection = x => x.Do(projection != null, y => y.FieldList(z => z.Comma.Flush(), projection), y => y.Wildcard.Flush()); if (select.Any) { sql.Select.Cast().Trim().OpenBlock.Trim().Case.When.Exists.OpenBlock.Trim(); } if (select.From.IsTable || select.HasConditions) { sql.Select.Flush(); if (select.First || select.FirstOrDefault) { sql.Top(1); } else if (select.Single) { sql.Top(2); } else if (select.HasTop && !select.HasStart) { sql.Do(select.TopType == Select.TopValueType.Count, x => x.Top(select.Top), x => x.TopPercent(select.Top)); } if (select.Count) { sql.CountWildcard.Flush(); } else { sql.Do(writeProjection); } sql.From.Flush(); if (select.HasStart) { sql.OpenBlock.Trim().Select.Do(writeProjection).Trim().Comma. RowNumber().Over.OpenBlock.Trim().OrderBy. Do(select.HasOrderBy, x => x.Write(orderByClause), x => x.Do(projection != null, writeProjection, y => y.QuotedName(mapping.Key.GetColumnName()))).Trim(). CloseBlock.As.RowNumberAlias.From.Flush(); } if (select.HasDuplicates) { var duplicateProjection = BuildProjection(select.Duplicates.Distinct, mapping, parameters); sql.OpenBlock.Trim().Select.Do(writeProjection).Trim().Comma. RowNumber().Over.OpenBlock.Trim().Partition.By.Write(duplicateProjection).OrderBy.Write(BuildOrderBy(select.Duplicates.OrderBy, mapping, parameters)). Trim().CloseBlock.As.PartitionAlias.From.Flush(); } else if (select.HasDistinct) { var distinctProjection = BuildProjections(select.Distinct.Select(x => x.Projection), mapping, parameters); sql.OpenBlock.Trim().Select.Do(writeProjection).Trim().Comma. RowNumber().Over.OpenBlock.Trim().Partition.By.ExpressionList(z => z.Comma.Flush(), distinctProjection).OrderBy. Write(BuildOrderBy(select.Distinct.Any(x => x.HasOrder) ? select.Distinct.Where(x => x.HasOrder).Select(x => x.Order) : select.Distinct.Select(x => new OrderBy { Type = OrderBy.SourceType.Projection, Projection = x.Projection, Order = Order.Ascending }), mapping, parameters)).Trim(). CloseBlock.As.PartitionAlias.From.Flush(); } } switch (select.From.Type) { case Data.DataType.Table: sql.QuotedName(select.From.Table.Name).Write(select.From.Alias); if (noLock) { sql.With(x => x.NoLock.Flush()); } break; case Data.DataType.Query: var first = true; if (select.HasConditions) { sql.OpenBlock.Trim().Flush(); } foreach (var subQuery in select.From.Queries.Select(x => CreateStatement(x, mapping, projection, noLock))) { sql.Do(!first, x => x.Union.Flush()).Write(subQuery.Text).Flush(); parameters.AddRange(subQuery.Parameters); first = false; } if (select.HasConditions) { sql.Trim().CloseBlock.As.Write(select.From.Alias).Flush(); } break; } if (select.From.IsTable || select.HasConditions) { if (select.HasWhere || select.HasSetOperations) { sql.Where.Write(whereClause).Flush(); } if (select.Randomize) { sql.OrderBy.NewId(); } // The reason why we dont do an order by if there is a start is because the order by is // already specified in the row number definition. So we dont need to specify it again. else if (select.HasOrderBy && !select.HasStart && !select.HasDistinct) { sql.OrderBy.Write(orderByClause); } if (select.HasDuplicates) { sql.Trim().CloseBlock.As.Write(select.From.Alias).Where.PartitionAlias.GreaterThan.Value(1, SqlDbType.Int).Flush(); } else if (select.HasDistinct) { sql.Trim().CloseBlock.As.Write(select.From.Alias).Where.PartitionAlias.Equal.Value(1, SqlDbType.Int).Flush(); } if (select.HasStart) { sql.Trim().CloseBlock.As.Write(select.From.Alias).Where.RowNumberAlias.Flush(); if (select.HasTop && select.HasStart) { sql.Between(select.Start, select.Start + (select.Top - 1)); } else { sql.GreaterThanOrEqual.Value(select.Start, SqlDbType.Int); } } } if (select.Any) { sql.Trim().CloseBlock.Then.Value(1, SqlDbType.Bit).Else.Value(0, SqlDbType.Bit).End.As.Write(DataTypes.Bit.SqlName).Trim().CloseBlock.Flush(); } return(new Statement(sql.ToString(), Statement.StatementType.Text, GetResultType(select), parameters)); }