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.

1
3
2/4/2019 11:34:55 AM

Accepted Answer

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()
}
3
2/6/2019 8:50:21 AM

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



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow