I am trying to create a dynamic lambda expression to pass in the Where clause of Entity Framework in .NET. Specifically, I need to construct a condition like:
u => (u.FirstName + " " + u.LastName).Contains("anyval")
However, I am facing issues in explicitly creating the (u.FirstName + " " + u.LastName) part as an expression.
What I Tried
1️⃣ Using Expression.Call with string.Concat
- This results in a SQL query with the
CONCAT()function, which Entity Framework does not support inWhereclauses.
2️⃣ Using Expression.Add to chain string concatenation
- This produces
((u.FirstName + " ") + u.LastName), which also does not work in Entity Framework.
var parameter = Expression.Parameter(typeof(ApplicationUser), "u");
var firstNameProperty = Expression.PropertyOrField(parameter, "FirstName");
var lastNameProperty = Expression.PropertyOrField(parameter, "LastName");
var spaceConstant = Expression.Constant(" ");
var plusConstant = Expression.Constant("+");
// Constructing (u.FirstName + " " + u.LastName)
var firstNamePlusSpace = Expression.Call(
typeof(string).GetMethod("Concat", new\[\] { typeof(string), typeof(string) }),
Expression.Call(
typeof(string).GetMethod("Concat", new\[\] { typeof(string), typeof(string) }),
firstNameProperty, plusConstant
),
Expression.Call(
typeof(string).GetMethod("Concat", new\[\] { typeof(string), typeof(string) }),
spaceConstant, lastNameProperty
)
);
var fullNameExpression = Expression.Call(
typeof(string).GetMethod("Concat", new\[\] { typeof(string), typeof(string) }),
firstNamePlusSpace, lastNameProperty
);
// Creating the lambda expression
var lambda = Expression.Lambda\<Func\<ApplicationUser, string\>\>(fullNameExpression, parameter);
// Passing the expression to GetCondition method
return GetCondition\<ApplicationUser\>(null, condition, value, lambda);
Helper Methods
private static MemberExpression GetMemberExpression(Expression param, string propertyName)
{
if (propertyName.Contains("."))
{
int index = propertyName.IndexOf(".");
var subParam = Expression.Property(param, propertyName.Substring(0, index));
return GetMemberExpression(subParam, propertyName.Substring(index + 1));
}
return Expression.Property(param, propertyName);
}
private static Expression\<Func\<T, bool\>\> GetCondition\<T\>(string propertyName, string condition, string value, Expression\<Func\<ApplicationUser, string\>\> lambdaExp = null)
{
var parameter = Expression.Parameter(typeof(T), "u");
var property = (lambdaExp == null) ? GetMemberExpression(parameter, propertyName) : lambdaExp.Body;
var constant = Expression.Constant(value);
switch (condition.ToLower())
{
case "contains":
return Expression.Lambda<Func<T, bool>>(
Expression.Call(property, nameof(string.Contains), null, constant),
parameter);
case "doesnotcontain":
return Expression.Lambda<Func<T, bool>>(
Expression.Not(Expression.Call(property, nameof(string.Contains), null, constant)),
parameter);
case "startswith":
return Expression.Lambda<Func<T, bool>>(
Expression.Call(property, nameof(string.StartsWith), null, constant),
parameter);
case "endswith":
return Expression.Lambda<Func<T, bool>>(
Expression.Call(property, nameof(string.EndsWith), null, constant),
parameter);
case "=":
return Expression.Lambda<Func<T, bool>>(
Expression.Equal(property, constant),
parameter);
default:
return null;
}
}
Issue & Expected Behavior Problem: When I use Expression.Call with string.Concat, Entity Framework does not recognize the generated query in the Where clause.
Expected Behavior: The generated lambda should translate to SQL in a way that allows filtering on concatenated FirstName and LastName. Possible Solutions & Workarounds I am open to solutions, whether they involve:
A proper way to construct (u.FirstName + " " + u.LastName) in an Expression Tree
Any help or suggestions would be greatly appreciated!
I am trying to create a dynamic lambda expression to pass in the Where clause of Entity Framework in .NET. Specifically, I need to construct a condition like:
u => (u.FirstName + " " + u.LastName).Contains("anyval")
However, I am facing issues in explicitly creating the (u.FirstName + " " + u.LastName) part as an expression.
What I Tried
1️⃣ Using Expression.Call with string.Concat
- This results in a SQL query with the
CONCAT()function, which Entity Framework does not support inWhereclauses.
2️⃣ Using Expression.Add to chain string concatenation
- This produces
((u.FirstName + " ") + u.LastName), which also does not work in Entity Framework.
var parameter = Expression.Parameter(typeof(ApplicationUser), "u");
var firstNameProperty = Expression.PropertyOrField(parameter, "FirstName");
var lastNameProperty = Expression.PropertyOrField(parameter, "LastName");
var spaceConstant = Expression.Constant(" ");
var plusConstant = Expression.Constant("+");
// Constructing (u.FirstName + " " + u.LastName)
var firstNamePlusSpace = Expression.Call(
typeof(string).GetMethod("Concat", new\[\] { typeof(string), typeof(string) }),
Expression.Call(
typeof(string).GetMethod("Concat", new\[\] { typeof(string), typeof(string) }),
firstNameProperty, plusConstant
),
Expression.Call(
typeof(string).GetMethod("Concat", new\[\] { typeof(string), typeof(string) }),
spaceConstant, lastNameProperty
)
);
var fullNameExpression = Expression.Call(
typeof(string).GetMethod("Concat", new\[\] { typeof(string), typeof(string) }),
firstNamePlusSpace, lastNameProperty
);
// Creating the lambda expression
var lambda = Expression.Lambda\<Func\<ApplicationUser, string\>\>(fullNameExpression, parameter);
// Passing the expression to GetCondition method
return GetCondition\<ApplicationUser\>(null, condition, value, lambda);
Helper Methods
private static MemberExpression GetMemberExpression(Expression param, string propertyName)
{
if (propertyName.Contains("."))
{
int index = propertyName.IndexOf(".");
var subParam = Expression.Property(param, propertyName.Substring(0, index));
return GetMemberExpression(subParam, propertyName.Substring(index + 1));
}
return Expression.Property(param, propertyName);
}
private static Expression\<Func\<T, bool\>\> GetCondition\<T\>(string propertyName, string condition, string value, Expression\<Func\<ApplicationUser, string\>\> lambdaExp = null)
{
var parameter = Expression.Parameter(typeof(T), "u");
var property = (lambdaExp == null) ? GetMemberExpression(parameter, propertyName) : lambdaExp.Body;
var constant = Expression.Constant(value);
switch (condition.ToLower())
{
case "contains":
return Expression.Lambda<Func<T, bool>>(
Expression.Call(property, nameof(string.Contains), null, constant),
parameter);
case "doesnotcontain":
return Expression.Lambda<Func<T, bool>>(
Expression.Not(Expression.Call(property, nameof(string.Contains), null, constant)),
parameter);
case "startswith":
return Expression.Lambda<Func<T, bool>>(
Expression.Call(property, nameof(string.StartsWith), null, constant),
parameter);
case "endswith":
return Expression.Lambda<Func<T, bool>>(
Expression.Call(property, nameof(string.EndsWith), null, constant),
parameter);
case "=":
return Expression.Lambda<Func<T, bool>>(
Expression.Equal(property, constant),
parameter);
default:
return null;
}
}
Issue & Expected Behavior Problem: When I use Expression.Call with string.Concat, Entity Framework does not recognize the generated query in the Where clause.
Expected Behavior: The generated lambda should translate to SQL in a way that allows filtering on concatenated FirstName and LastName. Possible Solutions & Workarounds I am open to solutions, whether they involve:
A proper way to construct (u.FirstName + " " + u.LastName) in an Expression Tree
Any help or suggestions would be greatly appreciated!
Share Improve this question edited Jan 29 at 16:28 Panagiotis Kanavos 132k16 gold badges203 silver badges265 bronze badges asked Jan 29 at 16:23 Saket AgrawalSaket Agrawal 91 silver badge1 bronze badge 6 | Show 1 more comment3 Answers
Reset to default 1I don't think you need Expressions for this. Try something like:
var results = dbContext.Users
.Where(u => EF.Functions.Like(string.Concat(u.FirstName, " ", u.LastName), "%anyval%"))
.ToList();
Documented here and here.
First of all, you're going to want to change GetCondition to use the parameter value from the Lambda when you're providing a lambda to it:
private static Expression<Func<T, bool>> GetCondition<T>(string propertyName, string condition, string value)
{
var parameter = Expression.Parameter(typeof(T), "u");
var property = GetMemberExpression(parameter, propertyName);
return GetCondition<T>(parameter, property, condition, value);
}
private static Expression<Func<T, bool>> GetCondition<T>(Expression<Func<T, string>> lambdaExp, string condition, string value)
{
var parameter = lambdaExp.Parameters.Single();
var property = lambdaExp.Body;
return GetCondition<T>(parameter, property, condition, value);
}
private static Expression<Func<T, bool>> GetCondition<T>(ParameterExpression parameter, Expression property, string condition, string value)
{
var constant = Expression.Constant(value);
switch (condition.ToLower())
...
Then, if you actually know (at code time) that u => u.FirstName + " " + u.Lastname is the expression you're going for, you can do this:
var lambda = (Expression<Func<ApplicationUser, string>>)(u => u.FirstName + " " + u.LastName);
return GetCondition<ApplicationUser>(lambda, condition, value);
If the firstname and lastname property names need to be provided dynamically, you can build an expression like this:
// Constructing (u.FirstName + " " + u.LastName)
var firstNamePlusSpace = Expression.Call(
typeof(string).GetMethod("Concat", new[] { typeof(string), typeof(string) }),
firstNameProperty,
spaceConstant);
var fullNameExpression = Expression.Call(
typeof(string).GetMethod("Concat", new[] { typeof(string), typeof(string) }),
firstNamePlusSpace, lastNameProperty
);
This works when I test it with Entity Framework 7. But if you want to more closely match what gets emitted by the compiler in the previous example, you can make a slight adjustment:
// Constructing (u.FirstName + " " + u.LastName)
var firstNamePlusSpace = Expression.Add(
firstNameProperty,
spaceConstant,
typeof(string).GetMethod("Concat", new[] { typeof(string), typeof(string) })
);
var fullNameExpression = Expression.Add(
firstNamePlusSpace,
lastNameProperty,
typeof(string).GetMethod("Concat", new[] { typeof(string), typeof(string) })
);
If you really do want to use expressions, the easiest way is to just use an Expression<Func>
Expression<Func<User, bool>> predicate =
u => EF.Functions.Like(u.FirstName + " " + u.LastName, "%anyval%"));
var results = dbContext.Users
.Where(predicate)
.ToList();
Note that this usually doesn't work within another query expression, it has to be actually executed code.
发布者：admin，转转请注明出处：http://www.yc00.com/questions/1745290509a4620829.html
WHERE FirstName LIKE '%x%' or LastName LIKE '%x%'. That's easier but still very slow as it also can't use any indexes. That's why all web sites only useStartsWithwhich translates toLIKE 'x%. Those that don't use full text search indexes – Panagiotis Kanavos Commented Jan 29 at 16:32pg_trgm. Anyway if you have answer - do it. – Svyatoslav Danyliv Commented Jan 29 at 17:03Add. As a suggestion just create the expression in a variable with known propety names. And then inspect in a debugger. You can also pass it toWhereto see if it works. – Ivan Petrov Commented Jan 30 at 0:58