public void Date()
{
ForEachProvider(db => AreEqual(
from t in Types select Sql.AsSql(t.DateTimeValue.Date),
from t in db.Types select Sql.AsSql(t.DateTimeValue.Date)));
}
public Expression BuildExpression(IBuildContext context, Expression expression)
{
var newExpr = expression.Transform(expr =>
{
if (_skippedExpressions.Contains(expr))
{
return(new TransformInfo(expr, true));
}
if (expr.Find(IsNoneSqlMember) != null)
{
return(new TransformInfo(expr));
}
switch (expr.NodeType)
{
case ExpressionType.MemberAccess:
{
if (IsServerSideOnly(expr) || PreferServerSide(expr))
{
return(new TransformInfo(BuildSql(context, expr)));
}
var ma = (MemberExpression)expr;
if (Expressions.ConvertMember(MappingSchema, ma.Expression == null ? null : ma.Expression.Type, ma.Member) != null)
{
break;
}
if (ma.Member.IsNullableValueMember())
{
break;
}
if (ma.Member.IsNullableHasValueMember())
{
Expression e = Expression.NotEqual(
ma.Expression, Expression.Constant(null, ma.Expression.Type));
return(new TransformInfo(
BuildExpression(
context,
ma.Expression.Type.IsPrimitiveEx() ?
Expression.Call(
MemberHelper.MethodOf(() => Sql.AsSql(true)),
e) :
e),
true));
}
var ctx = GetContext(context, ma);
if (ctx != null)
{
if (ma.Type.IsGenericTypeEx() && typeof(IEnumerable <>).IsSameOrParentOf(ma.Type))
{
var res = ctx.IsExpression(ma, 0, RequestFor.Association);
if (res.Result)
{
var table = (TableBuilder.AssociatedTableContext)res.Context;
if (table.IsList)
{
var mexpr = GetMultipleQueryExpression(context, ma, new HashSet <ParameterExpression>());
return(new TransformInfo(BuildExpression(context, mexpr)));
}
}
}
return(new TransformInfo(ctx.BuildExpression(ma, 0)));
}
var ex = ma.Expression;
while (ex is MemberExpression)
{
ex = ((MemberExpression)ex).Expression;
}
if (ex is MethodCallExpression)
{
var ce = (MethodCallExpression)ex;
if (IsSubQuery(context, ce))
{
if (!IsMultipleQuery(ce))
{
var info = GetSubQueryContext(context, ce);
var par = Expression.Parameter(ex.Type);
var bex = info.Context.BuildExpression(ma.Transform(e => e == ex ? par : e), 0);
if (bex != null)
{
return(new TransformInfo(bex));
}
}
}
}
ex = ma.Expression;
if (ex != null && ex.NodeType == ExpressionType.Constant)
{
// field = localVariable
//
var c = _expressionAccessors[ex];
return(new TransformInfo(Expression.MakeMemberAccess(Expression.Convert(c, ex.Type), ma.Member)));
}
break;
}
case ExpressionType.Parameter:
{
if (expr == ParametersParam)
{
break;
}
var ctx = GetContext(context, expr);
if (ctx != null)
{
return(new TransformInfo(ctx.BuildExpression(expr, 0)));
}
break;
}
case ExpressionType.Constant:
{
if (expr.Type.IsConstantable())
{
break;
}
if (_expressionAccessors.ContainsKey(expr))
{
return(new TransformInfo(Expression.Convert(_expressionAccessors[expr], expr.Type)));
}
break;
}
case ExpressionType.Coalesce:
if (expr.Type == typeof(string) && MappingSchema.GetDefaultValue(typeof(string)) != null)
{
return(new TransformInfo(BuildSql(context, expr)));
}
if (CanBeTranslatedToSql(context, ConvertExpression(expr), true))
{
return(new TransformInfo(BuildSql(context, expr)));
}
break;
case ExpressionType.Conditional:
if (CanBeTranslatedToSql(context, ConvertExpression(expr), true))
{
return(new TransformInfo(BuildSql(context, expr)));
}
break;
case ExpressionType.Call:
{
var ce = (MethodCallExpression)expr;
if (IsGroupJoinSource(context, ce))
{
foreach (var arg in ce.Arguments.Skip(1))
{
if (!_skippedExpressions.Contains(arg))
{
_skippedExpressions.Add(arg);
}
}
if (IsSubQuery(context, ce))
{
if (ce.IsQueryable())
//if (!typeof(IEnumerable).IsSameOrParentOf(expr.Type) || expr.Type == typeof(string) || expr.Type.IsArray)
{
var ctx = GetContext(context, expr);
if (ctx != null)
{
return(new TransformInfo(ctx.BuildExpression(expr, 0)));
}
}
}
break;
}
if (IsSubQuery(context, ce))
{
if (IsMultipleQuery(ce))
{
return(new TransformInfo(BuildMultipleQuery(context, expr)));
}
return(new TransformInfo(GetSubQueryExpression(context, ce)));
}
if (IsServerSideOnly(expr) || PreferServerSide(expr))
{
return(new TransformInfo(BuildSql(context, expr)));
}
}
break;
}
if (EnforceServerSide(context))
{
switch (expr.NodeType)
{
case ExpressionType.MemberInit:
case ExpressionType.New:
case ExpressionType.Convert:
break;
default:
if (CanBeCompiled(expr))
{
break;
}
return(new TransformInfo(BuildSql(context, expr)));
}
}
return(new TransformInfo(expr));
});
return(newExpr);
}
public void ToUInt2()
{
ForEachProvider(new[] { ProviderName.MySql }, db => AreEqual(
from t in Types select Sql.Convert <uint, decimal>(t.MoneyValue),
from t in db.Types select Sql.AsSql(Sql.Convert <uint, decimal>(t.MoneyValue))));
}
static Expression <Func <Issue1189Customer, DateTime> > DefaultDateTime()
{
return(p => Sql.AsSql(DateTime.Now));
}
static Expression <Func <ITestDataContext, Parent, int, int> > Count5Expression()
{
return((db, p, n) => Sql.AsSql(db.Child.Where(c => c.ParentID == p.ParentID).Count() + n));
}
public void AddMilliseconds([DataSources(ProviderName.Informix, ProviderName.Access, ProviderName.SapHana, TestProvName.AllMySql)]
string context)
{
using (var db = GetDataContext(context))
(from t in db.Types select Sql.AsSql(t.DateTimeValue.AddMilliseconds(221))).ToList();
}
public void SqlDatesGetExecutedProperly()
{
var tr = _servicesFixture.InsertRequirement(months: 12);
var actualDate = _servicesFixture.DbConnection.TrainingRequirements.Select(requirement =>
Sql.AsSql(new DateTime(2018, 3, 1).AddMonths(requirement.RenewMonthsCount / -2))
).First();
actualDate.ShouldBe(new DateTime(2018, 3, 1).AddMonths(tr.RenewMonthsCount / -2));
}
public void AddYears()
{
ForEachProvider(db => AreEqual(
from t in Types select t.DateTimeValue.AddYears(1).Date,
from t in db.Types select Sql.AsSql(t.DateTimeValue.AddYears(1)).Date));
}
public void AddHours()
{
ForEachProvider(db => AreEqual(
from t in Types select t.DateTimeValue.AddHours(22).Hour,
from t in db.Types select Sql.AsSql(t.DateTimeValue.AddHours(22)).Hour));
}
public void DateAddSecond()
{
ForEachProvider(db => AreEqual(
from t in Types select Sql.DateAdd(Sql.DateParts.Second, 41, t.DateTimeValue).Value.Second,
from t in db.Types select Sql.AsSql(Sql.DateAdd(Sql.DateParts.Second, 41, t.DateTimeValue)).Value.Second));
}
public void DateAddMillisecond()
{
ForEachProvider(new[] { ProviderName.Informix, ProviderName.MySql, ProviderName.Access },
db => (from t in db.Types select Sql.AsSql(Sql.DateAdd(Sql.DateParts.Millisecond, 41, t.DateTimeValue))).ToList());
}
public void DateAddMinute()
{
ForEachProvider(db => AreEqual(
from t in Types select Sql.DateAdd(Sql.DateParts.Minute, 5, t.DateTimeValue).Value.Minute,
from t in db.Types select Sql.AsSql(Sql.DateAdd(Sql.DateParts.Minute, 5, t.DateTimeValue)).Value.Minute));
}
public void DateAddHour()
{
ForEachProvider(db => AreEqual(
from t in Types select Sql.DateAdd(Sql.DateParts.Hour, 1, t.DateTimeValue).Value.Hour,
from t in db.Types select Sql.AsSql(Sql.DateAdd(Sql.DateParts.Hour, 1, t.DateTimeValue)).Value.Hour));
}
public void DateAddWeekDay()
{
ForEachProvider(db => AreEqual(
from t in Types select Sql.DateAdd(Sql.DateParts.WeekDay, 1, t.DateTimeValue).Value.Date,
from t in db.Types select Sql.AsSql(Sql.DateAdd(Sql.DateParts.WeekDay, 1, t.DateTimeValue)).Value.Date));
}
public void AddMilliseconds(string context)
{
using (var db = GetDataContext(context))
(from t in db.Types select Sql.AsSql(t.DateTimeValue.AddMilliseconds(221))).ToList();
}
public void AddMinutes()
{
ForEachProvider(db => AreEqual(
from t in Types select t.DateTimeValue.AddMinutes(-8).Minute,
from t in db.Types select Sql.AsSql(t.DateTimeValue.AddMinutes(-8)).Minute));
}
TransformInfo TransformExpression(IBuildContext context, Expression expr, bool enforceServerSide, string alias)
{
if (_skippedExpressions.Contains(expr))
{
return(new TransformInfo(expr, true));
}
if (expr.Find(IsNoneSqlMember) != null)
{
return(new TransformInfo(expr));
}
switch (expr.NodeType)
{
case ExpressionType.Convert:
case ExpressionType.ConvertChecked:
{
if (expr.Type == typeof(object))
{
break;
}
var cex = (UnaryExpression)expr;
_convertedExpressions.Add(cex.Operand, cex);
var nex = BuildExpression(context, cex.Operand, enforceServerSide);
if (nex.Type != cex.Type)
{
nex = cex.Update(nex);
}
var ret = new TransformInfo(nex, true);
RemoveConvertedExpression(cex.Operand);
return(ret);
}
case ExpressionType.MemberAccess:
{
if (IsServerSideOnly(expr) || PreferServerSide(expr, enforceServerSide))
{
return(new TransformInfo(BuildSql(context, expr, alias)));
}
var ma = (MemberExpression)expr;
var l = Expressions.ConvertMember(MappingSchema, ma.Expression?.Type, ma.Member);
if (l != null)
{
// In Grouping KeyContext we have to perform calculation on server side
if (Contexts.Any(c => c is GroupByBuilder.KeyContext))
{
return(new TransformInfo(BuildSql(context, expr, alias)));
}
break;
}
if (ma.Member.IsNullableValueMember())
{
break;
}
if (ma.Member.IsNullableHasValueMember())
{
Expression e = Expression.NotEqual(
ma.Expression, Expression.Constant(null, ma.Expression.Type));
return(new TransformInfo(
BuildExpression(
context,
ma.Expression.Type.IsPrimitiveEx() ?
Expression.Call(
MemberHelper.MethodOf(() => Sql.AsSql(true)),
e) :
e, enforceServerSide),
true));
}
var ctx = GetContext(context, ma);
if (ctx != null)
{
if (ma.Type.IsGenericTypeEx() && typeof(IEnumerable <>).IsSameOrParentOf(ma.Type))
{
var res = ctx.IsExpression(ma, 0, RequestFor.Association);
if (res.Result)
{
var table = (TableBuilder.AssociatedTableContext)res.Context;
if (table.IsList)
{
var mexpr = GetMultipleQueryExpression(context, MappingSchema, ma, new HashSet <ParameterExpression>());
return(new TransformInfo(BuildExpression(context, mexpr, enforceServerSide)));
}
}
}
var prevCount = ctx.SelectQuery.Select.Columns.Count;
var expression = ctx.BuildExpression(ma, 0, enforceServerSide);
if (!alias.IsNullOrEmpty() && (ctx.SelectQuery.Select.Columns.Count - prevCount) == 1)
{
ctx.SelectQuery.Select.Columns[ctx.SelectQuery.Select.Columns.Count - 1].Alias = alias;
}
return(new TransformInfo(expression));
}
var ex = ma.Expression;
while (ex is MemberExpression)
{
ex = ((MemberExpression)ex).Expression;
}
if (ex is MethodCallExpression ce)
{
if (IsSubQuery(context, ce))
{
if (!IsMultipleQuery(ce))
{
var info = GetSubQueryContext(context, ce);
if (alias != null)
{
info.Context.SetAlias(alias);
}
var par = Expression.Parameter(ex.Type);
var bex = info.Context.BuildExpression(ma.Transform(e => e == ex ? par : e), 0, enforceServerSide);
if (bex != null)
{
return(new TransformInfo(bex));
}
}
}
}
ex = ma.Expression;
if (ex != null && ex.NodeType == ExpressionType.Constant)
{
// field = localVariable
//
var c = _expressionAccessors[ex];
return(new TransformInfo(Expression.MakeMemberAccess(Expression.Convert(c, ex.Type), ma.Member)));
}
break;
}
case ExpressionType.Parameter:
{
if (expr == ParametersParam)
{
break;
}
var ctx = GetContext(context, expr);
if (ctx != null)
{
var buildExpr = ctx.BuildExpression(expr, 0, enforceServerSide);
if (buildExpr.Type != expr.Type)
{
buildExpr = Expression.Convert(buildExpr, expr.Type);
}
return(new TransformInfo(buildExpr));
}
break;
}
case ExpressionType.Constant:
{
if (expr.Type.IsConstantable())
{
break;
}
if (_expressionAccessors.TryGetValue(expr, out var accessor))
{
return(new TransformInfo(Expression.Convert(accessor, expr.Type)));
}
break;
}
case ExpressionType.Coalesce:
if (expr.Type == typeof(string) && MappingSchema.GetDefaultValue(typeof(string)) != null)
{
return(new TransformInfo(BuildSql(context, expr, alias)));
}
if (CanBeTranslatedToSql(context, ConvertExpression(expr), true))
{
return(new TransformInfo(BuildSql(context, expr, alias)));
}
break;
case ExpressionType.Conditional:
if (CanBeTranslatedToSql(context, ConvertExpression(expr), true))
{
return(new TransformInfo(BuildSql(context, expr, alias)));
}
break;
case ExpressionType.Call:
{
var ce = (MethodCallExpression)expr;
if (IsGroupJoinSource(context, ce))
{
foreach (var arg in ce.Arguments.Skip(1))
{
if (!_skippedExpressions.Contains(arg))
{
_skippedExpressions.Add(arg);
}
}
if (IsSubQuery(context, ce))
{
if (ce.IsQueryable())
//if (!typeof(IEnumerable).IsSameOrParentOf(expr.Type) || expr.Type == typeof(string) || expr.Type.IsArray)
{
var ctx = GetContext(context, expr);
if (ctx != null)
{
return(new TransformInfo(ctx.BuildExpression(expr, 0, enforceServerSide)));
}
}
}
break;
}
if (ce.IsAssociation(MappingSchema))
{
var ctx = GetContext(context, ce);
if (ctx == null)
{
throw new InvalidOperationException();
}
return(new TransformInfo(ctx.BuildExpression(ce, 0, enforceServerSide)));
}
if ((_buildMultipleQueryExpressions == null || !_buildMultipleQueryExpressions.Contains(ce)) && IsSubQuery(context, ce))
{
if (IsMultipleQuery(ce))
{
return(new TransformInfo(BuildMultipleQuery(context, ce, enforceServerSide)));
}
return(new TransformInfo(GetSubQueryExpression(context, ce, enforceServerSide, alias)));
}
if (IsServerSideOnly(expr) || PreferServerSide(expr, enforceServerSide) || ce.Method.IsSqlPropertyMethodEx())
{
return(new TransformInfo(BuildSql(context, expr, alias)));
}
}
break;
case ExpressionType.New:
{
var ne = (NewExpression)expr;
List <Expression> arguments = new List <Expression>();
for (var i = 0; i < ne.Arguments.Count; i++)
{
var a = ne.Arguments[i];
var memberAlias = ne.Members?[i].Name;
var newArgument =
a.Transform(ae => TransformExpression(context, ae, enforceServerSide, memberAlias));
a = newArgument;
arguments.Add(a);
}
if (arguments.Count > 0)
{
ne = ne.Update(arguments);
}
return(new TransformInfo(ne, true));
}
}
if (EnforceServerSide(context))
{
switch (expr.NodeType)
{
case ExpressionType.MemberInit:
case ExpressionType.Convert:
break;
default:
if (CanBeCompiled(expr))
{
break;
}
return(new TransformInfo(BuildSql(context, expr, alias)));
}
}
return(new TransformInfo(expr));
}
public void AddSeconds()
{
ForEachProvider(db => AreEqual(
from t in Types select t.DateTimeValue.AddSeconds(-35).Second,
from t in db.Types select Sql.AsSql(t.DateTimeValue.AddSeconds(-35)).Second));
}
public static Expression <Func <PersonCustom, int> > AgeExpr()
{
return(p => Sql.AsSql(5));
}
public void AddMilliseconds()
{
ForEachProvider(new[] { ProviderName.Informix, ProviderName.MySql, ProviderName.Access },
db => (from t in db.Types select Sql.AsSql(t.DateTimeValue.AddMilliseconds(221))).ToList());
}
static Expression <Func <PersonCalculated, string> > GetAsSqlFullNameExpr()
{
return(p => Sql.AsSql(p.LastName + ", " + p.FirstName));
}
public void Parse2()
{
ForEachProvider(db => AreEqual(
from d in from t in Types select DateTime.Parse(t.DateTimeValue.Year + "-02-24 00:00:00") where d.Day > 0 select d,
from d in from t in db.Types select Sql.AsSql(DateTime.Parse(t.DateTimeValue.Year + "-02-24 00:00:00")) where d.Day > 0 select d));
}
public void DatePartWeekNumberingType([DataSources(false)] string context)
{
using (var db = new TestDataConnection(context))
{
var dates = new[]
{
new DateTime(2018, 12, 28),
new DateTime(2018, 12, 29),
new DateTime(2018, 12, 30),
new DateTime(2018, 12, 31),
new DateTime(2019, 1, 1),
new DateTime(2019, 1, 2),
new DateTime(2019, 1, 3),
new DateTime(2019, 1, 4),
new DateTime(2019, 1, 5),
new DateTime(2019, 1, 6),
new DateTime(2019, 1, 7),
new DateTime(2019, 1, 8)
};
// actually 53 should be 1st week of 2019, but..
var isoWeeks = new[] { 52, 52, 52, 53, 1, 1, 1, 1, 1, 1, 2, 2 };
var sqliteParodyNumbering = new[] { 52, 52, 52, 53, 0, 0, 0, 0, 0, 0, 1, 1 };
var isoProperWeeks = new[] { 52, 52, 52, 1, 1, 1, 1, 1, 1, 1, 2, 2 };
var usWeeks = new[] { 52, 52, 53, 53, 1, 1, 1, 1, 1, 2, 2, 2 };
var usWeeksZeroBased = new[] { 51, 51, 52, 52, 0, 0, 0, 0, 0, 1, 1, 1 };
var muslimWeeks = new[] { 52, 53, 53, 53, 1, 1, 1, 1, 2, 2, 2, 2 };
var primitive = new[] { 52, 52, 52, 53, 1, 1, 1, 1, 1, 1, 1, 2 };
var results = dates
.Select(date => db.Select(() => Sql.AsSql(Sql.DatePart(Sql.DateParts.Week, Sql.ToSql(date)))))
.AsEnumerable()
.Select(_ => _.Value)
.ToArray();
if (isoWeeks.SequenceEqual(results))
{
Assert.Pass($"Context {db.DataProvider.Name} uses ISO week numbering schema");
}
else if (isoProperWeeks.SequenceEqual(results))
{
Assert.Pass($"Context {db.DataProvider.Name} uses PROPER ISO week numbering schema");
}
else if (usWeeks.SequenceEqual(results))
{
Assert.Pass($"Context {db.DataProvider.Name} uses US week numbering schema");
}
else if (muslimWeeks.SequenceEqual(results))
{
Assert.Pass($"Context {db.DataProvider.Name} uses Islamic week numbering schema");
}
else if (primitive.SequenceEqual(results))
{
Assert.Pass($"Context {db.DataProvider.Name} uses PRIMITIVE week numbering schema");
}
else if (sqliteParodyNumbering.SequenceEqual(results))
{
Assert.Pass($"Context {db.DataProvider.Name} uses SQLite inhuman numbering logic");
}
else if (usWeeksZeroBased.SequenceEqual(results))
{
Assert.Pass($"Context {db.DataProvider.Name} uses US 0-based week numbering schema");
}
else
{
Assert.Fail($"Context {db.DataProvider.Name} uses unknown week numbering schema");
}
}
}
public void DatePartDay()
{
ForEachProvider(db => AreEqual(
from t in Types select Sql.DatePart(Sql.DateParts.Day, t.DateTimeValue),
from t in db.Types select Sql.AsSql(Sql.DatePart(Sql.DateParts.Day, t.DateTimeValue))));
}
static Expression <Func <ITable <Child>, Parent, int, int> > Count7Expression()
{
return((ch, p, n) => Sql.AsSql(ch.Where(c => c.ParentID == p.ParentID).Count() + n));
}
public void ToInt2()
{
ForEachProvider(db => AreEqual(
from t in Types select Sql.Convert <int, decimal>(t.MoneyValue),
from t in db.Types select Sql.AsSql(Sql.Convert <int, decimal>(t.MoneyValue))));
}
public void DatePartWeek([DataSources] string context)
{
using (var db = GetDataContext(context))
(from t in db.Types select Sql.AsSql(Sql.DatePart(Sql.DateParts.Week, t.DateTimeValue))).ToList();
}
public void DateAddMillisecond(string context)
{
using (var db = GetDataContext(context))
(from t in db.Types select Sql.AsSql(Sql.DateAdd(Sql.DateParts.Millisecond, 41, t.DateTimeValue))).ToList();
}
public void ToInt1()
{
ForEachProvider(db => AreEqual(
from t in Types select Sql.ConvertTo <int> .From(t.MoneyValue),
from t in db.Types select Sql.AsSql(Sql.ConvertTo <int> .From(t.MoneyValue))));
}
public void Millisecond()
{
ForEachProvider(new[] { ProviderName.Informix, ProviderName.MySql, ProviderName.Access }, db => AreEqual(
from t in Types select t.DateTimeValue.Millisecond,
from t in db.Types select Sql.AsSql(t.DateTimeValue.Millisecond)));
}