public static Sql Where <T>(this Sql sql, Expression <Func <T, bool> > predicate, ISqlSyntaxProvider sqlSyntax)
{
var expresionist = new PocoToSqlExpressionVisitor <T>(sqlSyntax);
var whereExpression = expresionist.Visit(predicate);
return(sql.Where(whereExpression, expresionist.GetSqlParameters()));
}
public void StartsWith()
{
Expression <Func <UserDto, object> > predicate = user => user.Login.StartsWith("aaaaa");
var modelToSqlExpressionHelper = new PocoToSqlExpressionVisitor <UserDto>(SqlContext, null);
var result = modelToSqlExpressionHelper.Visit(predicate);
Console.WriteLine(result);
Assert.AreEqual("upper([umbracoUser].[userLogin]) LIKE upper(@0)", result);
predicate = user => user.Login.StartsWith(GetSomeValue("aaaaa"));
modelToSqlExpressionHelper = new PocoToSqlExpressionVisitor <UserDto>(SqlContext, null);
result = modelToSqlExpressionHelper.Visit(predicate);
Console.WriteLine(result);
Assert.AreEqual("upper([umbracoUser].[userLogin]) LIKE upper(@0)", result);
predicate = user => user.Login.StartsWith(GetSomeValue("aaaaa"));
modelToSqlExpressionHelper = new PocoToSqlExpressionVisitor <UserDto>(SqlContext, null);
result = modelToSqlExpressionHelper.Visit(predicate);
Console.WriteLine(result);
Assert.AreEqual("upper([umbracoUser].[userLogin]) LIKE upper(@0)", result);
var foo = new Foo {
Value = "aaaaa"
};
predicate = user => user.Login.StartsWith(foo.Value);
modelToSqlExpressionHelper = new PocoToSqlExpressionVisitor <UserDto>(SqlContext, null);
result = modelToSqlExpressionHelper.Visit(predicate);
Console.WriteLine(result);
Assert.AreEqual("upper([umbracoUser].[userLogin]) LIKE upper(@0)", result);
// below does not work, we want to output
// LIKE concat([group].[name], ',%')
// and how would we get the comma there? we'd have to parse .StartsWith(group.Name + ',')
// which is going to be quite complicated => no
// how can we do user.Login.StartsWith(other.Value) ?? to use in WHERE or JOIN.ON clauses ??
//// Expression<Func<UserDto, UserGroupDto, object>> predicate2 = (user, group) => user.Login.StartsWith(group.Name);
//// var modelToSqlExpressionHelper2 = new PocoToSqlExpressionVisitor<UserDto, UserGroupDto>(SqlContext, null, null);
//// var result2 = modelToSqlExpressionHelper2.Visit(predicate2); // fails, for now
//// Console.WriteLine(result2);
Expression <Func <UserDto, UserGroupDto, object> > predicate3 = (user, group) =>
SqlExtensionsStatics.SqlText <bool>(user.Login, group.Name, (n1, n2) => $"({n1} LIKE concat({n2}, ',%'))");
var modelToSqlExpressionHelper3 = new PocoToSqlExpressionVisitor <UserDto, UserGroupDto>(SqlContext, null, null);
var result3 = modelToSqlExpressionHelper3.Visit(predicate3);
Console.WriteLine(result3);
}
public void Poco_Expression_Value_Does_Not_Get_Double_Escaped()
{
//mysql escapes backslashes, so we'll test with that
SqlSyntaxContext.SqlSyntaxProvider = new MySqlSyntaxProvider(Mock.Of <ILogger>());
Expression <Func <UserDto, bool> > predicate = user => user.Login.StartsWith("mydomain\\myuser");
var modelToSqlExpressionHelper = new PocoToSqlExpressionVisitor <UserDto>();
var result = modelToSqlExpressionHelper.Visit(predicate);
Debug.Print("Poco to Sql ExpressionHelper: \n" + result);
Assert.AreEqual("upper(`umbracoUser`.`userLogin`) LIKE upper(@0)", result);
Assert.AreEqual("mydomain\\myuser%", modelToSqlExpressionHelper.GetSqlParameters()[0]);
}
