private SqlExpression BuildVersionFilter(PersistRequestBuilderContext context, SqlTableRef filteredTable, List <PersistParameterBinding> currentBindings)
{
SqlExpression result = null;
foreach (var column in context.Type.GetVersionColumns())
{
var fieldIndex = GetFieldIndex(context.Type, column);
if (!context.Task.AvailableFields[fieldIndex])
{
continue;
}
PersistParameterBinding binding;
if (!context.VersionParameterBindings.TryGetValue(column, out binding))
{
var typeMapping = driver.GetTypeMapping(column);
binding = new PersistParameterBinding(typeMapping, fieldIndex, ParameterTransmissionType.Regular, PersistParameterBindingType.VersionFilter);
context.VersionParameterBindings.Add(column, binding);
}
var filteredColumn = filteredTable[column.Name];
if (filteredColumn.IsNullReference())
{
continue;
}
var filterValue = binding.ParameterReference;
// Handle decimal precision issue
if (Type.GetTypeCode(column.ValueType) == TypeCode.Decimal)
{
filterValue = SqlDml.Cast(filterValue, driver.MapValueType(column));
}
result &= SqlDml.Variant(binding, filteredColumn == filterValue, SqlDml.IsNull(filteredColumn));
currentBindings.Add(binding);
}
return(result);
}
private SqlExpression GetJoinExpression(SqlExpression leftExpression, SqlExpression rightExpression, JoinProvider provider, int index)
{
if (provider.EqualColumns.Length > index)
{
if (providerInfo.Supports(ProviderFeatures.DateTimeEmulation))
{
if (provider.EqualColumns[index].First.Type == typeof(DateTime))
{
leftExpression = SqlDml.Cast(leftExpression, SqlType.DateTime);
}
if (provider.EqualColumns[index].Second.Type == typeof(DateTime))
{
rightExpression = SqlDml.Cast(rightExpression, SqlType.DateTime);
}
}
if (providerInfo.Supports(ProviderFeatures.DateTimeOffsetEmulation))
{
if (provider.EqualColumns[index].First.Type == typeof(DateTimeOffset))
{
leftExpression = SqlDml.Cast(leftExpression, SqlType.DateTimeOffset);
}
if (provider.EqualColumns[index].Second.Type == typeof(DateTimeOffset))
{
rightExpression = SqlDml.Cast(rightExpression, SqlType.DateTimeOffset);
}
}
}
return(leftExpression == rightExpression);
}
public void InsertAndSelectTest()
{
var schema = ExtractDefaultSchema();
EnsureTableNotExists(schema, TableName);
var table = schema.CreateTable(TableName);
var idColumnType = Driver.TypeMappings[typeof(int)].MapType();
var idColumn = table.CreateColumn(IdColumnName, idColumnType);
table.CreatePrimaryKey("PK_" + TableName, idColumn);
for (int columnIndex = 0; columnIndex < typeMappings.Length; columnIndex++)
{
var mapping = typeMappings[columnIndex];
var column = table.CreateColumn(GetColumnName(columnIndex), mapping.MapType());
column.IsNullable = true;
}
ExecuteNonQuery(SqlDdl.Create(table));
var tableRef = SqlDml.TableRef(table);
using (var insertCommand = Connection.CreateCommand()) {
var insertQuery = SqlDml.Insert(tableRef);
var idParameter = insertCommand.CreateParameter();
idParameter.DbType = DbType.Int32;
idParameter.ParameterName = IdParameterName;
insertCommand.Parameters.Add(idParameter);
insertQuery.Values.Add(tableRef[IdColumnName], SqlDml.ParameterRef(IdParameterName));
var parameters = new List <DbParameter>();
for (int columnIndex = 0; columnIndex < typeMappings.Length; columnIndex++)
{
var mapping = typeMappings[columnIndex];
var parameterName = GetParameterName(columnIndex);
SqlExpression parameterExpression = SqlDml.ParameterRef(parameterName);
if (mapping.ParameterCastRequired)
{
parameterExpression = SqlDml.Cast(parameterExpression, mapping.MapType());
}
insertQuery.Values.Add(tableRef[GetColumnName(columnIndex)], parameterExpression);
var parameter = insertCommand.CreateParameter();
parameter.ParameterName = parameterName;
parameters.Add(parameter);
insertCommand.Parameters.Add(parameter);
}
var insertQueryText = Driver.Compile(insertQuery).GetCommandText();
insertCommand.CommandText = insertQueryText;
for (int rowIndex = 0; rowIndex < testValues[0].Length; rowIndex++)
{
idParameter.Value = rowIndex;
for (int columnIndex = 0; columnIndex < typeMappings.Length; columnIndex++)
{
typeMappings[columnIndex].BindValue(parameters[columnIndex], testValues[columnIndex][rowIndex]);
}
insertCommand.ExecuteNonQuery();
}
}
var resultQuery = SqlDml.Select(tableRef);
resultQuery.Columns.Add(SqlDml.Asterisk);
resultQuery.OrderBy.Add(tableRef[IdColumnName]);
VerifyResults(Connection.CreateCommand(resultQuery));
}
protected override SqlProvider VisitFreeText(FreeTextProvider provider)
{
SqlFreeTextTable fromTable;
QueryParameterBinding[] bindings;
var stringTypeMapping = Driver.GetTypeMapping(StringType);
var criteriaBinding = new QueryParameterBinding(
stringTypeMapping, provider.SearchCriteria.Invoke, QueryParameterBindingType.Regular);
var index = provider.PrimaryIndex.Resolve(Handlers.Domain.Model);
var table = Mapping[index.ReflectedType];
var columns = provider.Header.Columns.Select(column => column.Name).ToList();
if (provider.TopN == null)
{
fromTable = SqlDml.FreeTextTable(table, criteriaBinding.ParameterReference, columns);
bindings = new[] { criteriaBinding };
}
else
{
var intTypeMapping = Driver.GetTypeMapping(Int32Type);
var topNBinding = new QueryParameterBinding(intTypeMapping, context => provider.TopN.Invoke(context), QueryParameterBindingType.Regular);
fromTable = SqlDml.FreeTextTable(table, criteriaBinding.ParameterReference, columns, topNBinding.ParameterReference);
bindings = new[] { criteriaBinding, topNBinding };
}
var fromTableRef = SqlDml.QueryRef(fromTable);
var select = SqlDml.Select(fromTableRef);
select.Columns.Add(fromTableRef.Columns[0]);
select.Columns.Add(SqlDml.Cast(fromTableRef.Columns[1], SqlType.Double), "RANK");
return(CreateProvider(select, bindings, provider));
}
protected override SqlProvider VisitFreeText(FreeTextProvider provider)
{
var rankColumnName = provider.Header.Columns.Last().Name;
var stringTypeMapping = Driver.GetTypeMapping(typeof(string));
var binding = new QueryParameterBinding(stringTypeMapping,
provider.SearchCriteria.Invoke, QueryParameterBindingType.Regular);
SqlSelect select = SqlDml.Select();
var realPrimaryIndex = provider.PrimaryIndex.Resolve(Handlers.Domain.Model);
var index = realPrimaryIndex.ReflectedType.Indexes.PrimaryIndex;
var query = BuildProviderQuery(index);
var table = Mapping[realPrimaryIndex.ReflectedType];
var fromTable = SqlDml.FreeTextTable(table, binding.ParameterReference, table.Columns.Select(column => column.Name).AddOne(rankColumnName).ToList());
var fromTableRef = SqlDml.QueryRef(fromTable);
foreach (var column in query.Columns)
{
select.Columns.Add(fromTableRef.Columns[column.Name] ?? column);
}
select.Columns.Add(SqlDml.Cast(fromTableRef.Columns[rankColumnName], SqlType.Double), rankColumnName);
select.From = fromTableRef;
if (provider.TopN != null)
{
select.Limit = provider.TopN.Invoke();
select.OrderBy.Add(select.Columns[rankColumnName], false);
}
return(CreateProvider(select, binding, provider));
}
public void SelectConstantsTest()
{
var queries = new List <SqlSelect>();
var command = Connection.CreateCommand();
for (int rowIndex = 0; rowIndex < testValues[0].Length; rowIndex++)
{
var query = SqlDml.Select();
queries.Add(query);
query.Columns.Add(SqlDml.Literal(rowIndex), IdColumnName);
for (int columnIndex = 0; columnIndex < testValues.Length; columnIndex++)
{
var columnName = GetColumnName(columnIndex);
var value = testValues[columnIndex][rowIndex];
var mapping = typeMappings[columnIndex];
var valueExpression = value == null
? (SqlExpression)SqlDml.Null
: mapping.ParameterCastRequired
? (SqlExpression)SqlDml.Cast(SqlDml.Literal(value), mapping.MapType())
: SqlDml.Literal(value);
query.Columns.Add(valueExpression, columnName);
}
}
var unionQueryRef = SqlDml.QueryRef(queries.Cast <ISqlQueryExpression>().Aggregate(SqlDml.UnionAll));
var resultQuery = SqlDml.Select(unionQueryRef);
resultQuery.Columns.Add(SqlDml.Asterisk);
resultQuery.OrderBy.Add(unionQueryRef[IdColumnName]);
command.CommandText = Driver.Compile(resultQuery).GetCommandText();
VerifyResults(command, true);
}
private static SqlExpression DateTimeOffsetToLocalDateTime(SqlExpression dateTimeOffset)
{
return(SqlDml.Cast(
SqlDml.DateTimePlusInterval(
Switchoffset(dateTimeOffset, "+00:00"),
SqlDml.DateTimeMinusDateTime(SqlDml.Native("getdate()"), SqlDml.Native("getutcdate()"))),
SqlType.DateTime));
}
protected override SqlExpression ProcessAggregate(SqlProvider source, List <SqlExpression> sourceColumns, AggregateColumn aggregateColumn)
{
var result = base.ProcessAggregate(source, sourceColumns, aggregateColumn);
if (aggregateColumn.AggregateType == AggregateType.Sum || aggregateColumn.AggregateType == AggregateType.Avg)
{
result = SqlDml.Cast(result, Driver.MapValueType(aggregateColumn.Type));
}
return(result);
}
public void SqlCastCloneTest()
{
SqlCast c = SqlDml.Cast(SqlDml.Literal(1), SqlType.Decimal, 6, 4);
SqlCast cClone = (SqlCast)c.Clone();
Assert.AreNotEqual(c, cClone);
Assert.AreNotEqual(c.Operand, cClone.Operand);
Assert.AreEqual(c.NodeType, cClone.NodeType);
Assert.AreEqual(c.Operand.NodeType, cClone.Operand.NodeType);
Assert.AreEqual(c.Type, cClone.Type);
}
private static SqlExpression DateTimeOffsetToLocalDateTime(SqlExpression dateTimeOffset)
{
return(SqlDml.Cast(
SqlDml.DateTimePlusInterval(
DateTimeOffsetToUtcDateTime(dateTimeOffset),
SqlDml.DateTimeMinusDateTime(
DateTimeOffsetToUtcDateTime(
SqlDml.Native("SYSTIMESTAMP")),
SqlDml.Native("SYSTIMESTAMP"))),
SqlType.DateTime));
}
internal static SqlExpression GenericIntervalConstruct(
SqlExpression days,
SqlExpression hours,
SqlExpression minutes,
SqlExpression seconds,
SqlExpression milliseconds)
{
var m = milliseconds + 1000L * (seconds + 60L * (minutes + 60L * (hours + 24L * days)));
var nanoseconds = NanosecondsPerMillisecond * SqlDml.Cast(m, SqlType.Int64);
return(SqlDml.IntervalConstruct(nanoseconds));
}
private static SqlExpression DateTimeOffsetTruncate(SqlExpression dateTimeOffset)
{
return(SqlDml.Cast(
DateAddMillisecond(
DateAddSecond(
DateAddMinute(
DateAddHour(dateTimeOffset,
-SqlDml.Extract(SqlDateTimeOffsetPart.Hour, dateTimeOffset)),
-SqlDml.Extract(SqlDateTimeOffsetPart.Minute, dateTimeOffset)),
-SqlDml.Extract(SqlDateTimeOffsetPart.Second, dateTimeOffset)),
-SqlDml.Extract(SqlDateTimeOffsetPart.Millisecond, dateTimeOffset)),
SqlType.DateTime));
}
private static SqlExpression TryCastToDecimalPS(SqlExpression value, short precision, short scale)
{
var context = ExpressionTranslationContext.Current;
var provider = context.ProviderInfo.ProviderName;
if (provider.Equals(WellKnown.Provider.PostgreSql, StringComparison.Ordinal) ||
provider.Equals(WellKnown.Provider.Oracle, StringComparison.Ordinal))
{
// to fit result into .Net decimal since Npgsql client libarary does not provide a way to make in on reading
return(SqlDml.Cast(value, SqlType.Decimal, precision, scale));
}
return(value);
}
private static SqlExpression DateTimeOffsetToLocalTime(SqlExpression dateTimeOffset)
{
return(SqlDml.FunctionCall("FROM_TZ",
SqlDml.Cast(
dateTimeOffset
-
(SysExtractUtc(SqlDml.Native("CURRENT_TIMESTAMP"))
- SysExtractUtc(
SqlDml.FunctionCall("FROM_TZ",
SqlDml.Cast(SqlDml.Native("CURRENT_TIMESTAMP"), SqlType.DateTime),
DateTimeOffsetExtractPart(dateTimeOffset, "TZR")))),
SqlType.DateTime)
, SqlDml.Native("sessiontimezone")));
}
protected override SqlExpression ProcessAggregate(SqlProvider source, List <SqlExpression> sourceColumns, AggregateColumn aggregateColumn)
{
var result = base.ProcessAggregate(source, sourceColumns, aggregateColumn);
if (aggregateColumn.AggregateType == AggregateType.Avg)
{
switch (Type.GetTypeCode(aggregateColumn.Type))
{
case TypeCode.Single:
case TypeCode.Double:
result = SqlDml.Cast(result, Driver.MapValueType(aggregateColumn.Type));
break;
}
}
return(result);
}
private SqlExpression GetOrderByExpression(SqlExpression expression, SortProvider provider, int index)
{
if (provider.Header.Columns.Count <= index)
{
return(expression);
}
if (providerInfo.Supports(ProviderFeatures.DateTimeEmulation) && provider.Header.Columns[index].Type == typeof(DateTime))
{
return(SqlDml.Cast(expression, SqlType.DateTime));
}
if (providerInfo.Supports(ProviderFeatures.DateTimeOffsetEmulation) && provider.Header.Columns[index].Type == typeof(DateTimeOffset))
{
return(SqlDml.Cast(expression, SqlType.DateTimeOffset));
}
return(expression);
}
/// <inheritdoc/>
public override void Visit(SqlFunctionCall node)
{
switch (node.FunctionType)
{
case SqlFunctionType.Concat:
var exprs = new SqlExpression[node.Arguments.Count];
node.Arguments.CopyTo(exprs, 0);
Visit(SqlDml.Concat(exprs));
return;
case SqlFunctionType.DateTimeTruncate:
Visit(SqlDml.Cast(node.Arguments[0], new SqlValueType("Date")));
return;
case SqlFunctionType.IntervalToMilliseconds:
Visit(CastToLong(node.Arguments[0]) / NanosecondsPerMillisecond);
return;
case SqlFunctionType.IntervalConstruct:
case SqlFunctionType.IntervalToNanoseconds:
Visit(CastToLong(node.Arguments[0]));
return;
case SqlFunctionType.DateTimeAddMonths:
Visit(DateAddMonth(node.Arguments[0], node.Arguments[1]));
return;
case SqlFunctionType.DateTimeAddYears:
Visit(DateAddYear(node.Arguments[0], node.Arguments[1]));
return;
case SqlFunctionType.DateTimeConstruct:
Visit(DateAddDay(DateAddMonth(DateAddYear(SqlDml.Cast(SqlDml.Literal(new DateTime(2001, 1, 1)), SqlType.DateTime),
node.Arguments[0] - 2001),
node.Arguments[1] - 1),
node.Arguments[2] - 1));
return;
case SqlFunctionType.DateTimeToStringIso:
Visit(DateTimeToStringIso(node.Arguments[0]));
return;
}
base.Visit(node);
}
private SqlExpression VisitParameterAccess(Expression e, bool smartNull)
{
var type = e.Type;
// In rare cases (when calculated column is just parameter access) we need to strip cast to object.
if (e.NodeType == ExpressionType.Convert && e.Type == typeof(object))
{
type = ((UnaryExpression)e).Operand.Type;
}
bool optimizeBooleanParameter = type == typeof(bool);
type = type.StripNullable();
var typeMapping = driver.GetTypeMapping(type);
var expression = parameterExtractor.ExtractParameter <object>(e);
var bindingType = optimizeBooleanParameter
? QueryParameterBindingType.BooleanConstant
: (smartNull
? QueryParameterBindingType.SmartNull
: QueryParameterBindingType.Regular);
var binding = RegisterParameterBinding(typeMapping, expression, bindingType);
SqlExpression result;
if (optimizeBooleanParameter)
{
result = SqlDml.Variant(binding, SqlFalse, SqlTrue);
if (fixBooleanExpressions)
{
result = booleanExpressionConverter.IntToBoolean(result);
}
}
else
{
result = binding.ParameterReference;
if (type == typeof(bool) && fixBooleanExpressions)
{
result = booleanExpressionConverter.IntToBoolean(result);
}
else if (typeMapping.ParameterCastRequired)
{
result = SqlDml.Cast(result, typeMapping.MapType());
}
}
return(result);
}
public void AddTest()
{
SqlLiteral <int> l1 = SqlDml.Literal(1);
SqlLiteral <int> l2 = SqlDml.Literal(2);
SqlBinary b = l1 + l2;
Assert.AreEqual(b.NodeType, SqlNodeType.Add);
b = b - ~l1;
Assert.AreEqual(b.NodeType, SqlNodeType.Subtract);
Assert.AreEqual(b.Right.NodeType, SqlNodeType.BitNot);
SqlSelect s = SqlDml.Select();
s.Columns.Add(1, "id");
b = b / s;
Assert.AreEqual(b.NodeType, SqlNodeType.Divide);
Assert.AreEqual(b.Right.NodeType, SqlNodeType.SubSelect);
SqlCast c = SqlDml.Cast(l1, SqlType.Decimal);
Assert.AreEqual(c.NodeType, SqlNodeType.Cast);
SqlFunctionCall l = SqlDml.CharLength(SqlDml.Literal("name"));
b = c % l;
Assert.AreEqual(b.NodeType, SqlNodeType.Modulo);
Assert.AreEqual(b.Right.NodeType, SqlNodeType.FunctionCall);
b = l1 * (-l2);
Assert.AreEqual(b.NodeType, SqlNodeType.Multiply);
Assert.AreEqual(b.Right.NodeType, SqlNodeType.Negate);
SqlBatch batch = SqlDml.Batch();
SqlVariable v1 = SqlDml.Variable("v1", SqlType.Double);
batch.Add(v1.Declare());
batch.Add(SqlDml.Assign(v1, 1.0));
s = SqlDml.Select();
s.Columns.Add(b, "value");
batch.Add(s);
}
public SqlExpression BooleanToInt(SqlExpression expression)
{
// optimization: omitting BooleanToInt(IntToBoolean(x)) sequences
if (expression.NodeType == SqlNodeType.NotEquals)
{
var binary = (SqlBinary)expression;
var left = binary.Left;
var right = binary.Right as SqlLiteral <int>;
if (!ReferenceEquals(right, null) && right.Value == 0)
{
return(left);
}
}
var result = SqlDml.Case();
result.Add(expression, 1);
result.Else = 0;
return(SqlDml.Cast(result, booleanType));
}
public override void Visit(SqlExtract node)
{
if (node.DateTimeOffsetPart == SqlDateTimeOffsetPart.DayOfWeek)
{
Visit((DatePartWeekDay(node.Operand) + DateFirst + 6) % 7);
return;
}
switch (node.DateTimeOffsetPart)
{
case SqlDateTimeOffsetPart.TimeZoneHour:
Visit(DateTimeOffsetTimeZoneInMinutes(node.Operand) / 60);
return;
case SqlDateTimeOffsetPart.TimeZoneMinute:
Visit(DateTimeOffsetTimeZoneInMinutes(node.Operand) % 60);
return;
case SqlDateTimeOffsetPart.Date:
DateTimeOffsetTruncate(node.Operand).AcceptVisitor(this);
return;
case SqlDateTimeOffsetPart.DateTime:
DateTimeOffsetTruncateOffset(node.Operand).AcceptVisitor(this);
return;
case SqlDateTimeOffsetPart.LocalDateTime:
DateTimeOffsetToLocalDateTime(node.Operand).AcceptVisitor(this);
return;
case SqlDateTimeOffsetPart.UtcDateTime:
SqlDml.Cast(Switchoffset(node.Operand, "+00:00"), SqlType.DateTime).AcceptVisitor(this);
return;
case SqlDateTimeOffsetPart.Offset:
DateTimeOffsetPartOffset(node.Operand).AcceptVisitor(this);
return;
}
base.Visit(node);
}
public void SqlCastReplacingTest()
{
SqlCast c = SqlDml.Cast(1, SqlType.Float);
SqlCast cReplacing = SqlDml.Cast(2, SqlType.Char);
c.ReplaceWith(cReplacing);
bool passed = false;
try {
c.ReplaceWith(1);
}
catch {
passed = true;
}
Assert.IsTrue(passed);
Assert.AreNotEqual(c, cReplacing);
Assert.AreEqual(c.NodeType, cReplacing.NodeType);
Assert.AreEqual(c.Operand, cReplacing.Operand);
Assert.AreEqual(c.Type, cReplacing.Type);
}
private SqlExpression VisitCast(UnaryExpression cast, SqlExpression operand)
{
var sourceType = cast.Operand.Type.StripNullable();
var targetType = cast.Type.StripNullable();
if (sourceType == targetType || targetType == typeof(object) || sourceType == typeof(object))
{
return(operand);
}
if (IsEnumUnderlyingType(sourceType, targetType) || IsEnumUnderlyingType(targetType, sourceType))
{
return(operand);
}
// Special case for boolean cast
if (fixBooleanExpressions && IsBooleanExpression(cast.Operand))
{
var result = SqlDml.Case();
result.Add(operand, 1);
result.Else = 0;
operand = result;
}
return(SqlDml.Cast(operand, driver.MapValueType(targetType)));
}
public void SelectParametersTest()
{
int parameterIndex = 0;
var queries = new List <SqlSelect>();
var command = Connection.CreateCommand();
for (int rowIndex = 0; rowIndex < testValues[0].Length; rowIndex++)
{
var query = SqlDml.Select();
queries.Add(query);
query.Columns.Add(SqlDml.Literal(rowIndex), IdColumnName);
for (int columnIndex = 0; columnIndex < testValues.Length; columnIndex++)
{
var mapping = typeMappings[columnIndex];
var columnName = GetColumnName(columnIndex);
var parameterName = GetParameterName(parameterIndex++);
SqlExpression parameterExpression = SqlDml.ParameterRef(parameterName);
if (mapping.ParameterCastRequired)
{
parameterExpression = SqlDml.Cast(parameterExpression, mapping.MapType());
}
query.Columns.Add(parameterExpression, columnName);
var parameter = command.CreateParameter();
parameter.ParameterName = parameterName;
typeMappings[columnIndex].BindValue(parameter, testValues[columnIndex][rowIndex]);
command.Parameters.Add(parameter);
}
}
var unionQueryRef = SqlDml.QueryRef(queries.Cast <ISqlQueryExpression>().Aggregate(SqlDml.UnionAll));
var resultQuery = SqlDml.Select(unionQueryRef);
resultQuery.Columns.Add(SqlDml.Asterisk);
resultQuery.OrderBy.Add(unionQueryRef[IdColumnName]);
command.CommandText = Driver.Compile(resultQuery).GetCommandText();
VerifyResults(command);
}
protected override SqlExpression VisitUnary(UnaryExpression expression)
{
var operand = Visit(expression.Operand);
if (expression.Method != null)
{
return(CompileMember(expression.Method, null, operand));
}
switch (expression.NodeType)
{
case ExpressionType.ArrayLength:
if (expression.Operand.Type != typeof(byte[]))
{
throw new NotSupportedException(string.Format(Strings.ExTypeXIsNotSupported, expression.Operand.Type));
}
return(SqlDml.Cast(SqlDml.BinaryLength(operand), driver.MapValueType(typeof(int))));
case ExpressionType.Negate:
case ExpressionType.NegateChecked:
return(SqlDml.Negate(operand));
case ExpressionType.UnaryPlus:
return(operand);
case ExpressionType.Not:
return(IsBooleanExpression(expression.Operand)
? SqlDml.Not(operand)
: SqlDml.BitNot(operand));
case ExpressionType.Convert:
case ExpressionType.ConvertChecked:
return(VisitCast(expression, operand));
}
return(operand);
}
protected override SqlProvider VisitFreeText(FreeTextProvider provider)
{
var rankColumnName = provider.Header.Columns[provider.Header.Columns.Count - 1].Name;
var stringTypeMapping = Driver.GetTypeMapping(WellKnownTypes.StringType);
var binding = new QueryParameterBinding(stringTypeMapping,
provider.SearchCriteria.Invoke, QueryParameterBindingType.Regular);
var select = SqlDml.Select();
var realPrimaryIndex = provider.PrimaryIndex.Resolve(Handlers.Domain.Model);
var index = realPrimaryIndex.ReflectedType.Indexes.PrimaryIndex;
var query = BuildProviderQuery(index);
var table = Mapping[realPrimaryIndex.ReflectedType];
var fromTable = SqlDml.FreeTextTable(table, binding.ParameterReference,
table.Columns.Select(column => column.Name).Append(rankColumnName).ToArray(table.Columns.Count + 1));
var fromTableRef = SqlDml.QueryRef(fromTable);
foreach (var column in query.Columns)
{
select.Columns.Add(fromTableRef.Columns[column.Name] ?? column);
}
select.Columns.Add(SqlDml.Cast(fromTableRef.Columns[rankColumnName], SqlType.Double), rankColumnName);
select.From = fromTableRef;
if (provider.TopN == null)
{
return(CreateProvider(select, binding, provider));
}
var intTypeMapping = Driver.GetTypeMapping(typeof(int));
var topNBinding = new QueryParameterBinding(
intTypeMapping, context => provider.TopN.Invoke(context), QueryParameterBindingType.Regular);
select.Limit = topNBinding.ParameterReference;
select.OrderBy.Add(select.Columns[rankColumnName], false);
return(CreateProvider(select, new[] { binding, topNBinding }, provider));
}
public void ComposeQuery()
{
using (var session = Domain.OpenSession())
using (var tx = session.OpenTransaction()) {
var builder = session.Services.Get <QueryBuilder>();
Assert.That(builder, Is.Not.Null);
var binding = builder.CreateParameterBinding(typeof(int), context => 43);
SqlSelect select;
if (StorageProviderInfo.Instance.CheckProviderIs(StorageProvider.Firebird))
{
//require cast of parameters on sql level
select = SqlDml.Select(SqlDml.Cast(binding.ParameterReference, SqlType.Int32));
}
else
{
select = SqlDml.Select(binding.ParameterReference);
}
var compiled = builder.CompileQuery(select);
Assert.That(compiled, Is.Not.Null);
var request = builder.CreateRequest(compiled, new[] { binding });
Assert.That(request, Is.Not.Null);
var command = builder.CreateCommand(request);
Assert.That(command, Is.Not.Null);
using (command) {
var result = Convert.ToInt32(command.ExecuteScalar());
Assert.That(result, Is.EqualTo(43));
}
tx.Complete();
}
}
protected override SqlExpression ProcessAggregate(
SqlProvider source, List <SqlExpression> sourceColumns, AggregateColumn aggregateColumn)
{
var aggregateType = aggregateColumn.Type;
var result = base.ProcessAggregate(source, sourceColumns, aggregateColumn);
if (aggregateColumn.AggregateType == AggregateType.Avg)
{
var originType = source.Origin.Header.Columns[aggregateColumn.SourceIndex].Type;
// floats are promoted to doubles, but we need the same type
if (originType == aggregateType && originType != typeof(float))
{
return(result);
}
var sqlType = Driver.MapValueType(aggregateType);
return(SqlDml.Cast(SqlDml.Avg(SqlDml.Cast(sourceColumns[aggregateColumn.SourceIndex], sqlType)), sqlType));
}
// cast to decimal is dangerous, because 'decimal' defaults to integer type
if (aggregateColumn.AggregateType == AggregateType.Sum && aggregateType != typeof(decimal))
{
return(SqlDml.Cast(result, Driver.MapValueType(aggregateType)));
}
return(result);
}
public override void Visit(SqlFunctionCall node)
{
const double nanosecondsPerSecond = 1000000000.0;
switch (node.FunctionType)
{
case SqlFunctionType.PadLeft:
case SqlFunctionType.PadRight:
SqlHelper.GenericPad(node).AcceptVisitor(this);
return;
case SqlFunctionType.Rand:
SqlDml.FunctionCall(translator.Translate(SqlFunctionType.Rand)).AcceptVisitor(this);
return;
case SqlFunctionType.Square:
SqlDml.Power(node.Arguments[0], 2).AcceptVisitor(this);
return;
case SqlFunctionType.IntervalConstruct:
((node.Arguments[0] / SqlDml.Literal(nanosecondsPerSecond)) * OneSecondInterval).AcceptVisitor(this);
return;
case SqlFunctionType.IntervalToMilliseconds:
SqlHelper.IntervalToMilliseconds(node.Arguments[0]).AcceptVisitor(this);
return;
case SqlFunctionType.IntervalToNanoseconds:
SqlHelper.IntervalToNanoseconds(node.Arguments[0]).AcceptVisitor(this);
return;
case SqlFunctionType.IntervalAbs:
SqlHelper.IntervalAbs(node.Arguments[0]).AcceptVisitor(this);
return;
case SqlFunctionType.DateTimeConstruct:
var newNode = (SqlDml.Literal(new DateTime(2001, 1, 1))
+ OneYearInterval * (node.Arguments[0] - 2001)
+ OneMonthInterval * (node.Arguments[1] - 1)
+ OneDayInterval * (node.Arguments[2] - 1));
newNode.AcceptVisitor(this);
return;
case SqlFunctionType.DateTimeTruncate:
(SqlDml.FunctionCall("date_trunc", "day", node.Arguments[0])).AcceptVisitor(this);
return;
case SqlFunctionType.DateTimeAddMonths:
(node.Arguments[0] + node.Arguments[1] * OneMonthInterval).AcceptVisitor(this);
return;
case SqlFunctionType.DateTimeAddYears:
(node.Arguments[0] + node.Arguments[1] * OneYearInterval).AcceptVisitor(this);
return;
case SqlFunctionType.DateTimeToStringIso:
DateTimeToStringIso(node.Arguments[0]).AcceptVisitor(this);
return;
case SqlFunctionType.DateTimeOffsetTimeOfDay:
DateTimeOffsetTimeOfDay(node.Arguments[0]).AcceptVisitor(this);
return;
case SqlFunctionType.DateTimeOffsetAddMonths:
SqlDml.Cast(node.Arguments[0] + node.Arguments[1] * OneMonthInterval, SqlType.DateTimeOffset).AcceptVisitor(this);
return;
case SqlFunctionType.DateTimeOffsetAddYears:
SqlDml.Cast(node.Arguments[0] + node.Arguments[1] * OneYearInterval, SqlType.DateTimeOffset).AcceptVisitor(this);
return;
case SqlFunctionType.DateTimeOffsetConstruct:
ConstructDateTimeOffset(node.Arguments[0], node.Arguments[1]).AcceptVisitor(this);
return;
case SqlFunctionType.DateTimeToDateTimeOffset:
SqlDml.Cast(node.Arguments[0], SqlType.DateTimeOffset).AcceptVisitor(this);
return;
}
base.Visit(node);
}
protected SqlExpression ConstructDateTimeOffsetFromExpressions(SqlExpression datetimeStringExpression, SqlExpression offsetStringExpression)
{
return(SqlDml.Cast(SqlDml.Concat(datetimeStringExpression, " ", offsetStringExpression), SqlType.DateTimeOffset));
}