public void Build(ISqExtensionBuilder builder)
{
var part = builder.GetValue <Sql.DateParts>(0);
var startDate = builder.GetExpression(1);
var endDate = builder.GetExpression(2);
var secondsExpr = builder.Mul <int>(builder.Sub <int>(
new SqlFunction(typeof(int), "Days", endDate),
new SqlFunction(typeof(int), "Days", startDate)),
new SqlValue(86400));
var midnight = builder.Sub <int>(
new SqlFunction(typeof(int), "MIDNIGHT_SECONDS", endDate),
new SqlFunction(typeof(int), "MIDNIGHT_SECONDS", startDate));
var resultExpr = builder.Add <int>(secondsExpr, midnight);
switch (part)
{
case Sql.DateParts.Day: resultExpr = builder.Div(resultExpr, 86400); break;
case Sql.DateParts.Hour: resultExpr = builder.Div(resultExpr, 3600); break;
case Sql.DateParts.Minute: resultExpr = builder.Div(resultExpr, 60); break;
case Sql.DateParts.Second: break;
case Sql.DateParts.Millisecond:
resultExpr = builder.Add <int>(
builder.Mul(resultExpr, 1000),
builder.Div(
builder.Sub <int>(
new SqlFunction(typeof(int), "MICROSECOND", endDate),
new SqlFunction(typeof(int), "MICROSECOND", startDate)),
1000));
break;
default:
throw new ArgumentOutOfRangeException();
}
builder.ResultExpression = resultExpr;
}
public void Build(ISqExtensionBuilder builder)
{
var tableExpr = builder.GetExpression(0);
SqlTable sqlTable = null;
if (tableExpr is SqlTable t)
{
sqlTable = t;
}
else if (tableExpr is SqlField field)
{
sqlTable = field.Table as SqlTable;
}
else if (tableExpr is SqlColumn column)
{
sqlTable = QueryHelper.GetUnderlyingField(column)?.Table as SqlTable;
}
//TODO: review, maybe we need here TableSource
if (sqlTable == null)
{
throw new LinqToDBException("Can not find Table associated with expression");
}
var qualified = builder.Arguments.Length <= 1 ? TableQualification.Full : builder.GetValue <TableQualification>(1);
var isExpression = builder.Member.Name == "TableExpr";
var name = sqlTable.PhysicalName;
if (qualified != TableQualification.None)
{
var sb = new StringBuilder();
builder.DataContext.CreateSqlProvider().ConvertTableName(sb,
(qualified & TableQualification.DatabaseName) != 0 ? sqlTable.Database : null,
(qualified & TableQualification.SchemaName) != 0 ? sqlTable.Schema : null,
sqlTable.PhysicalName);
name = sb.ToString();
}
builder.ResultExpression = isExpression
? new SqlExpression(name, Precedence.Primary)
: (ISqlExpression) new SqlValue(name);
}
public void Build(ISqExtensionBuilder builder)
{
var tableExpr = builder.Arguments[0].EvaluateExpression();
var tableType = ((MethodInfo)builder.Member).GetGenericArguments()[0];
var helperType = typeof(TableHelper <>).MakeGenericType(tableType);
var tableHelper = (TableHelper)Activator.CreateInstance(helperType, tableExpr);
var qualified = builder.Arguments.Length <= 1 ? TableQualification.Full : builder.GetValue <TableQualification>(1);
var isExpression = builder.Member.Name == "TableExpr";
if (isExpression)
{
if (qualified == TableQualification.None)
{
builder.ResultExpression = new SqlExpression(typeof(string), tableHelper.TableName, Precedence.Primary);
}
else
{
var table = new SqlTable(tableType);
table.PhysicalName = tableHelper.TableName;
table.Database = (qualified & TableQualification.DatabaseName) != 0 ? tableHelper.DatabaseName : null;
table.Schema = (qualified & TableQualification.SchemaName) != 0 ? tableHelper.SchemaName : null;
builder.ResultExpression = table;
}
}
else
{
var name = tableHelper.TableName;
if (qualified != TableQualification.None)
{
var sb = new StringBuilder();
builder.DataContext.CreateSqlProvider().ConvertTableName(sb,
(qualified & TableQualification.DatabaseName) != 0 ? tableHelper.DatabaseName : null,
(qualified & TableQualification.SchemaName) != 0 ? tableHelper.SchemaName : null,
name);
name = sb.ToString();
}
builder.ResultExpression = new SqlValue(name);
}
}
public void Build(ISqExtensionBuilder builder)
{
var tableOrColumnExpr = builder.GetExpression(0);
var sqlField = tableOrColumnExpr as SqlField;
if (sqlField == null)
{
throw new LinqToDBException("Can not find Table or Column associated with expression");
}
if (sqlField.Name != "*")
{
builder.ResultExpression = sqlField;
return;
}
var sqlTable = (SqlTable)sqlField.Table !;
builder.ResultExpression = new SqlField(sqlTable, sqlTable.TableName.Name);
}
public void Build(ISqExtensionBuilder builder)
{
var part = builder.GetValue <DateParts>("part");
var partStr = part switch
{
DateParts.Year => "yyyy",
DateParts.Quarter => "q",
DateParts.Month => "m",
DateParts.DayOfYear => "y",
DateParts.Day => "d",
DateParts.Week => "ww",
DateParts.WeekDay => "w",
DateParts.Hour => "h",
DateParts.Minute => "n",
DateParts.Second => "s",
_ => throw new InvalidOperationException($"Unexpected datepart: {part}"),
};
builder.AddExpression("part", partStr);
}
public void Build(ISqExtensionBuilder builder)
{
var part = builder.GetValue <DateParts>(0);
var startDate = builder.GetExpression(1);
var endDate = builder.GetExpression(2);
var expStr = part switch
{
DateParts.Year => "(DATE_PART('year', {1}::date) - DATE_PART('year', {0}::date))",
DateParts.Month => "((DATE_PART('year', {1}::date) - DATE_PART('year', {0}::date)) * 12 + (DATE_PART('month', {1}'::date) - DATE_PART('month', {0}::date)))",
DateParts.Week => "TRUNC(DATE_PART('day', {1}::timestamp - {0}::timestamp) / 7)",
DateParts.Day => "EXTRACT(EPOCH FROM ({1}::timestamp - {0}::timestamp)) / 86400",
DateParts.Hour => "EXTRACT(EPOCH FROM ({1}::timestamp - {0}::timestamp)) / 3600",
DateParts.Minute => "EXTRACT(EPOCH FROM ({1}::timestamp - {0}::timestamp)) / 60",
DateParts.Second => "EXTRACT(EPOCH FROM ({1}::timestamp - {0}::timestamp))",
DateParts.Millisecond => "ROUND(EXTRACT(EPOCH FROM ({1}::timestamp - {0}::timestamp)) * 1000)",
_ => throw new InvalidOperationException($"Unexpected datepart: {part}"),
};
builder.ResultExpression = new SqlExpression(typeof(int), expStr, Precedence.Multiplicative, startDate, endDate);
}
public void Build(ISqExtensionBuilder builder)
{
var tableExpr = builder.GetExpression(0);
var sqlTable = tableExpr switch
{
SqlTable t => t,
SqlField field => field.Table as SqlTable,
SqlColumn column => QueryHelper.ExtractField(column)?.Table as SqlTable,
_ => null
};
//TODO: review, maybe we need here TableSource
if (sqlTable == null)
{
throw new LinqToDBException("Can not find Table associated with expression");
}
var qualified = builder.Arguments.Length <= 1 ? TableQualification.Full : builder.GetValue <TableQualification>(1);
var isExpression = builder.Member.Name == "TableExpr";
var name = sqlTable.PhysicalName !;
if (qualified != TableQualification.None)
{
var sb = new StringBuilder();
builder.DataContext.CreateSqlProvider().ConvertTableName(sb,
(qualified & TableQualification.ServerName) != 0 ? sqlTable.Server : null,
(qualified & TableQualification.DatabaseName) != 0 ? sqlTable.Database : null,
(qualified & TableQualification.SchemaName) != 0 ? sqlTable.Schema : null,
sqlTable.PhysicalName !,
(qualified & TableQualification.TableOptions) != 0 ? sqlTable.TableOptions : TableOptions.NotSet);
name = sb.ToString();
}
builder.ResultExpression = isExpression
? new SqlExpression(name, Precedence.Primary)
: new SqlValue(name);
}
}
public void Build(ISqExtensionBuilder builder)
{
var arguments = (NewArrayExpression)builder.Arguments[1];
if (arguments.Expressions.Count == 0 && builder.BuilderValue != null)
{
builder.ResultExpression = new SqlExpression(typeof(string), "''");
}
else if (arguments.Expressions.Count == 1 && builder.BuilderValue != null)
{
builder.ResultExpression = IsNullExpression((string)builder.BuilderValue, builder.ConvertExpressionToSql(arguments.Expressions[0]));
}
else
{
var items = arguments.Expressions.Select(builder.ConvertExpressionToSql);
foreach (var item in items)
{
builder.AddParameter("argument", item);
}
}
}
public void Build(ISqExtensionBuilder builder)
{
string partStr;
var part = builder.GetValue <DateParts>("part");
switch (part)
{
case DateParts.Year: partStr = "To_Number(To_Char({date}, 'YYYY'))"; break;
case DateParts.Quarter: partStr = "To_Number(To_Char({date}, 'Q'))"; break;
case DateParts.Month: partStr = "To_Number(To_Char({date}, 'MM'))"; break;
case DateParts.DayOfYear: partStr = "To_Number(To_Char({date}, 'DDD'))"; break;
case DateParts.Day: partStr = "To_Number(To_Char({date}, 'DD'))"; break;
case DateParts.Week: partStr = "To_Number(To_Char({date}, 'WW'))"; break;
case DateParts.WeekDay: partStr = "DayOfWeek({date})"; break;
case DateParts.Hour: partStr = "To_Number(To_Char({date}, 'HH24'))"; break;
case DateParts.Minute: partStr = "To_Number(To_Char({date}, 'MI'))"; break;
case DateParts.Second: partStr = "To_Number(To_Char({date}, 'SS'))"; break;
case DateParts.Millisecond:
{
builder.Expression = "To_Number(To_Char({date}, 'FF'))";
builder.ResultExpression = builder.Div(builder.ConvertToSqlExpression(Precedence.Primary), 1000);
return;
}
default:
throw new InvalidOperationException($"Unexpected datepart: {part}");
}
builder.Expression = partStr;
}
public void Build(ISqExtensionBuilder builder)
{
var part = builder.GetValue <DateParts>("part");
var date = builder.GetExpression("date");
var number = builder.GetExpression("number", true);
string expStr;
switch (part)
{
case DateParts.Year: expStr = "{0} * INTERVAL '1' YEAR"; break;
case DateParts.Quarter: expStr = "{0} * INTERVAL '3' MONTH"; break;
case DateParts.Month: expStr = "{0} * INTERVAL '1' MONTH"; break;
case DateParts.DayOfYear:
case DateParts.WeekDay:
case DateParts.Day: expStr = "{0} * INTERVAL '1' DAY"; break;
case DateParts.Week: expStr = "{0} * INTERVAL '7' DAY"; break;
case DateParts.Hour: expStr = "{0} * INTERVAL '1' HOUR"; break;
case DateParts.Minute: expStr = "{0} * INTERVAL '1' MINUTE"; break;
case DateParts.Second: expStr = "{0} * INTERVAL '1' SECOND"; break;
case DateParts.Millisecond: expStr = "{0} * INTERVAL '0.001' SECOND"; break;
default:
throw new InvalidOperationException($"Unexpected datepart: {part}");
}
builder.ResultExpression = builder.Add(
date,
new SqlExpression(typeof(TimeSpan?), expStr, Precedence.Multiplicative, number),
typeof(DateTime?));
}
public void Build(ISqExtensionBuilder builder)
{
ISqlExpression data;
if (builder.Arguments.Length == 2)
{
data = builder.GetExpression("source");
}
else
{
data = builder.GetExpression("selector");
}
// https://github.com/linq2db/linq2db/issues/1765
if (data is SqlField field && field.Type !.Value.DataType != DataType.Undefined)
{
var separator = builder.GetExpression("separator");
if (separator is SqlValue value && value.ValueType.DataType == DataType.Undefined)
{
value.ValueType = value.ValueType.WithDataType(field.Type !.Value.DataType);
}
public void Build(ISqExtensionBuilder builder)
{
var part = builder.GetValue <DateParts>("part");
var date = builder.GetExpression("date");
var number = builder.GetExpression("number", true);
string expStr;
switch (part)
{
case DateParts.Year: expStr = "{0} * Interval '1 Year'"; break;
case DateParts.Quarter: expStr = "{0} * Interval '1 Month' * 3"; break;
case DateParts.Month: expStr = "{0} * Interval '1 Month'"; break;
case DateParts.DayOfYear:
case DateParts.WeekDay:
case DateParts.Day: expStr = "{0} * Interval '1 Day'"; break;
case DateParts.Week: expStr = "{0} * Interval '1 Day' * 7"; break;
case DateParts.Hour: expStr = "{0} * Interval '1 Hour'"; break;
case DateParts.Minute: expStr = "{0} * Interval '1 Minute'"; break;
case DateParts.Second: expStr = "{0} * Interval '1 Second'"; break;
case DateParts.Millisecond: expStr = "{0} * Interval '1 Millisecond'"; break;
default:
throw new InvalidOperationException($"Unexpected datepart: {part}");
}
builder.ResultExpression = builder.Add(
date,
new SqlExpression(typeof(TimeSpan?), expStr, Precedence.Multiplicative, number),
typeof(DateTime?));
}
public void Build(ISqExtensionBuilder builder)
{
var part = builder.GetValue <DateParts>("part");
var date = builder.GetExpression("date");
var number = builder.GetExpression("number", true);
string expStr;
switch (part)
{
case DateParts.Year: expStr = "{0} Year"; break;
case DateParts.Quarter: expStr = "({0} * 3) Month"; break;
case DateParts.Month: expStr = "{0} Month"; break;
case DateParts.DayOfYear:
case DateParts.WeekDay:
case DateParts.Day: expStr = "{0} Day"; break;
case DateParts.Week: expStr = "({0} * 7) Day"; break;
case DateParts.Hour: expStr = "{0} Hour"; break;
case DateParts.Minute: expStr = "{0} Minute"; break;
case DateParts.Second: expStr = "{0} Second"; break;
case DateParts.Millisecond: expStr = "({0} / 1000.0) Second"; break;
default:
throw new InvalidOperationException($"Unexpected datepart: {part}");
}
builder.ResultExpression = builder.Add(
date,
new SqlExpression(typeof(TimeSpan?), expStr, Precedence.Primary, number),
typeof(DateTime?));
}
public void Build(ISqExtensionBuilder builder)
{
string exprStr;
var part = builder.GetValue <DateParts>("part");
switch (part)
{
case DateParts.Year: exprStr = "Year({date})"; break;
case DateParts.Quarter:
builder.Expression = "Floor((Month({date})-1) / 3)";
builder.ResultExpression = builder.Inc(builder.ConvertToSqlExpression());
return;
case DateParts.Month: exprStr = "Month({date})"; break;
case DateParts.DayOfYear: exprStr = "DayOfYear({date})"; break;
case DateParts.Day: exprStr = "DayOfMonth({date})"; break;
case DateParts.Week: exprStr = "Week({date})"; break;
case DateParts.WeekDay:
builder.Expression = "MOD(Weekday({date}) + 1, 7)";
builder.ResultExpression = builder.Inc(builder.ConvertToSqlExpression());
return;
case DateParts.Hour: exprStr = "Hour({date})"; break;
case DateParts.Minute: exprStr = "Minute({date})"; break;
case DateParts.Second: exprStr = "Second({date})"; break;
default:
throw new InvalidOperationException($"Unexpected datepart: {part}");
}
builder.Expression = exprStr;
}
public void Build(ISqExtensionBuilder builder)
{
var part = builder.GetValue <DateParts>("part");
var date = builder.GetExpression("date");
var number = builder.GetExpression("number", true);
var partStr = part switch
{
DateParts.Year => "yyyy",
DateParts.Quarter => "q",
DateParts.Month => "m",
DateParts.DayOfYear => "y",
DateParts.Day => "d",
DateParts.Week => "ww",
DateParts.WeekDay => "w",
DateParts.Hour => "h",
DateParts.Minute => "n",
DateParts.Second => "s",
_ => throw new InvalidOperationException($"Unexpected datepart: {part}"),
};
builder.ResultExpression = new SqlFunction(typeof(DateTime?), "DateAdd",
new SqlValue(partStr), number, date);
}
public void Build(ISqExtensionBuilder builder)
{
var part = builder.GetValue <DateParts>("part");
var date = builder.GetExpression("date");
var number = builder.GetExpression("number", true);
string expStr = "strftime('%Y-%m-%d %H:%M:%f', {0},";
switch (part)
{
case DateParts.Year: expStr += "{1} || ' Year')"; break;
case DateParts.Quarter: expStr += "({1}*3) || ' Month')"; break;
case DateParts.Month: expStr += "{1} || ' Month')"; break;
case DateParts.DayOfYear:
case DateParts.WeekDay:
case DateParts.Day: expStr += "{1} || ' Day')"; break;
case DateParts.Week: expStr += "({1}*7) || ' Day')"; break;
case DateParts.Hour: expStr += "{1} || ' Hour')"; break;
case DateParts.Minute: expStr += "{1} || ' Minute')"; break;
case DateParts.Second: expStr += "{1} || ' Second')"; break;
case DateParts.Millisecond: expStr += "({1}/1000.0) || ' Second')"; break;
default:
throw new InvalidOperationException($"Unexpected datepart: {part}");
}
builder.ResultExpression = new SqlExpression(typeof(DateTime?), expStr, Precedence.Concatenate, date, number);
}
public void Build(ISqExtensionBuilder builder)
{
var part = builder.GetValue <DateParts>("part");
var date = builder.GetExpression("date");
var number = builder.GetExpression("number", true);
string expStr;
switch (part)
{
case DateParts.Year: expStr = "{0} + Interval({1}) Year to Year"; break;
case DateParts.Quarter: expStr = "{0} + Interval({1}) Month to Month * 3"; break;
case DateParts.Month: expStr = "{0} + Interval({1}) Month to Month"; break;
case DateParts.DayOfYear:
case DateParts.WeekDay:
case DateParts.Day: expStr = "{0} + Interval({1}) Day to Day"; break;
case DateParts.Week: expStr = "{0} + Interval({1}) Day to Day * 7"; break;
case DateParts.Hour: expStr = "{0} + Interval({1}) Hour to Hour"; break;
case DateParts.Minute: expStr = "{0} + Interval({1}) Minute to Minute"; break;
case DateParts.Second: expStr = "{0} + Interval({1}) Second to Second"; break;
case DateParts.Millisecond: expStr = "{0} + Interval({1}) Second to Fraction * 1000"; break;
default:
throw new InvalidOperationException($"Unexpected datepart: {part}");
}
builder.ResultExpression = new SqlExpression(typeof(DateTime?), expStr, Precedence.Additive, date, number);
}
public void Build(ISqExtensionBuilder builder)
{
builder.ResultExpression = new SqlAliasPlaceholder();
}
public void Build(ISqExtensionBuilder builder)
{
string exprStr;
var part = builder.GetValue <DateParts>("part");
switch (part)
{
case DateParts.Year: exprStr = "Year({date})"; break;
case DateParts.Quarter:
{
builder.Expression = "Month({date})";
builder.ResultExpression =
builder.Inc(builder.Div(builder.Dec(builder.ConvertToSqlExpression(Precedence.Primary)), 3));
return;
}
case DateParts.Month: exprStr = "Month({date})"; break;
case DateParts.DayOfYear:
{
var param = builder.GetExpression("date");
builder.ResultExpression = builder.Inc(
builder.Sub <int>(
new SqlFunction(typeof(DateTime?), "Mdy",
new SqlFunction(typeof(int?), "Month", param),
new SqlFunction(typeof(int?), "Day", param),
new SqlFunction(typeof(int?), "Year", param)),
new SqlFunction(typeof(DateTime?), "Mdy",
new SqlValue(1),
new SqlValue(1),
new SqlFunction(typeof(int?), "Year", param)))
);
return;
}
case DateParts.Day: exprStr = "Day({date})"; break;
case DateParts.Week: exprStr = "((Extend({date}, year to day) - (Mdy(12, 31 - WeekDay(Mdy(1, 1, year({date}))), Year({date}) - 1) + Interval(1) day to day)) / 7 + Interval(1) day to day)::char(10)::int"; break;
case DateParts.WeekDay:
{
builder.Expression = "weekDay({date})";
builder.ResultExpression = builder.Inc(builder.ConvertToSqlExpression(Precedence.Primary));
return;
}
case DateParts.Hour: exprStr = "({date}::datetime Hour to Hour)::char(3)::int"; break;
case DateParts.Minute: exprStr = "({date}::datetime Minute to Minute)::char(3)::int"; break;
case DateParts.Second: exprStr = "({date}::datetime Second to Second)::char(3)::int"; break;
case DateParts.Millisecond: exprStr = "Millisecond({date})"; break;
default:
throw new InvalidOperationException($"Unexpected datepart: {part}");
}
builder.Expression = exprStr;
}
public void Build(ISqExtensionBuilder builder)
{
var args = Array.ConvertAll(builder.Arguments, x => builder.ConvertExpressionToSql(x));
builder.ResultExpression = new SqlRow(args);
}