I have this following query:
db.Users.AsQueryable()
.Where(u => u.Id = userResolver.LoggedUserId() && u.Packages.Where(p =>
p.StatusId == (int)PackageStatus.InProgress ||
p.StatusId == (int)PackageStatus.Delivered ||
p.StatusId == (int)PackageStatus.Shipped ||
p.StatusId == (int)PackageStatus.Waiting)
.Sum(p => p.Price) > u.MaxCredit)
.ToList()
What I'm trying to achieve is to group all the package status checks to an extension methods. Something like that:
db.Users.AsQueryable()
.Where(u => u.Id = userResolver.LoggedUserId() &&
u.Packages.Where(p => p.IsShippedOrInProgress())
.Sum(p => p.Price) > u.MaxCredit)
.ToList()
//This is the extension method
public static bool IsShippedOrInProgress(this Package p) {
return p.StatusId == (int)PackageStatus.InProgress ||
p.StatusId == (int)PackageStatus.Delivered ||
p.StatusId == (int)PackageStatus.Shipped ||
p.StatusId == (int)PackageStatus.Waiting)
}
When I view the sql query generated in the first example, everything seems ok, but when I'm using the second approach the part of the query that checks the status doesn't exists.
Try this method which will create Expression
, Package
as input and bool
as output:
public static System.Linq.Expressions.Expression<Func<Package, bool>> IsShippedOrInProgress()
{
return p => p.StatusId == ( int )PackageStatus.InProgress ||
p.StatusId == ( int )PackageStatus.Delivered ||
p.StatusId == ( int )PackageStatus.Shipped ||
p.StatusId == ( int )PackageStatus.Waiting);
}
Call it like this:
private void SomeMethod()
{
db.Users.AsQueryable()
.Where(u => u.Id = userResolver.LoggedUserId() &&
u.Packages.Where(IsShippedOrInProgress())
.Sum(p => p.Price) > u.MaxCredit)
.ToList()
}
I tried to write this in notepad. So there may be errors. Let me know in comments
public static Expression<Func<Package,bool>> IsShippedOrInProgress() {
// Compose the expression tree that represents the parameter to the predicate.
ParameterExpression p = Expression.Parameter(typeof(Package), "p");
// Compose left side of the expression i.e `p.StatusId`
Expression left = Expression.Call(p, typeof(Package).GetProperty("StatusId"));
// Compose right side of the expression i.e `(int)PackageStatus.InProgress` etc.
Expression exprInProgress = Expression.Constant((int)PackageStatus.InProgress);
Expression exprDelivered = Expression.Constant((int)PackageStatus.Delivered);
Expression exprShipped = Expression.Constant((int)PackageStatus.Shipped);
Expression exprWaiting = Expression.Constant((int)PackageStatus.Waiting);
// Compose left equals right side
Expression e1 = Expression.Equal(left, exprInProgress);
Expression e2 = Expression.Equal(left, exprDelivered);
Expression e3 = Expression.Equal(left, exprShipped);
Expression e4 = Expression.Equal(left, exprWaiting);
//Compose `p.StatusId == (int)PackageStatus.InProgress ||
// p.StatusId == (int)PackageStatus.Delivered ||
// p.StatusId == (int)PackageStatus.Shipped ||
// p.StatusId == (int)PackageStatus.Waiting`
Expression orConditions = Expressions.OrElse(Expression.OrElse(Expression.OrElse(e1,e2),e3),e4);
//Compose `p =>
// p.StatusId == (int)PackageStatus.InProgress ||
// p.StatusId == (int)PackageStatus.Delivered ||
// p.StatusId == (int)PackageStatus.Shipped ||
// p.StatusId == (int)PackageStatus.Waiting`
return Expression.Lambda<Func<Package, bool>>(orConditions, new ParameterExpression[] { p }));
}
Update
The C# compiler can generate expression trees from expression lambdas (or single-line lambdas). Please check @Ankush Answer