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]); }