Entity Framework Core - Use Extension Methods Inside Queryable

Question

Entity Framework Core - Use Extension Methods Inside Queryable

c# entity-framework entity-framework-core

Question

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.

Tal Humy

Popular Answer

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

Ramesh

View more on Stack Overflow

Ankush Madankar · Accepted Answer · 2/6/2019 8:50:21 AM

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()
}

Entity Framework Core - Use Extension Methods Inside Queryable

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related