Entity Framework Core - Use Extension Methods Inside Queryable

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.

Yes. Actually this is just an example. I can not post the real code. I will be glad to supply you with anything you need :-) — Tal Humy, Feb 04 '19 at 08:10
@TalHumy Better explain clearly what you are expecting from these query? — TanvirArjel, Feb 04 '19 at 08:14
Custom methods for obvious reasons cannot be translated to SQL. One possible solution is to use 3rd party library, like [NeinLinq.EntityFrameworkCore](https://learn.microsoft.com/en-us/ef/core/extensions/#neinlinqentityframeworkcore): *"NeinLinq extends LINQ providers such as Entity Framework to enable reusing functions, rewriting queries, and building dynamic queries using translatable predicates and selectors."* — Ivan Stoev, Feb 04 '19 at 09:00
I Updated the question, hopefully it will shed some light on my problem — Tal Humy, Feb 04 '19 at 09:05
You may have to explore how EF translates your C# code to SQL queries. You may solve this by making the extension method return `Expression>` — Ramesh, Feb 04 '19 at 09:08
Check this, it could be helpful. https://stackoverflow.com/questions/19052507/how-to-use-a-func-in-an-expression-with-linq-to-entity-framework — Karan, Feb 04 '19 at 09:29
@Ramesh Now if you can tell how to syntactically use that method inside query expression tree. — Ivan Stoev, Feb 04 '19 at 09:29
@Ramesh I didn't understand how to use it. I understand the general idea but I will really appreciate an example — Tal Humy, Feb 04 '19 at 09:47

Ankush Madankar · Accepted Answer · 2019-02-06T08:50:21.923

5

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

edited Feb 06 '19 at 08:50

answered Feb 05 '19 at 06:59

Ankush Madankar

3,689
4
40
74

So I tried to do the following: db.Users.AsQueryable() .Where(u => u.Id = userResolver.LoggedUserId() && p.Packages.AsQueryable().Where(IsShippedOrInProgress()) but I received the following error: Could not parse expression 'u.Package.AsQueryable().Where(IsShippedOrInProgress(p.OwnerId))': The given arguments did not match the expected arguments: Object of type 'System.Linq.Expressions.MethodCallExpression1' cannot be converted to type 'System.Linq.Expressions.LambdaExpression'. – Tal Humy Feb 05 '19 at 07:55
Much simpler option +1 – Ramesh Feb 05 '19 at 09:45
@TalHumy Not sure why `IsShippedOrInProgress` method receiving `p.OwnerId` argument. It should be of `Package` type. This `u.Packages.Where(p => IsShippedOrInProgress())` should work with instanced method `IsShippedOrInProgress`. – Ankush Madankar Feb 06 '19 at 07:10
@AnkushMadankar Creating expression is trivial. The problem is how to **use** it inside another expression tree. If you think carefully, you'll see that `Where(p => IsShippedOrInProgress())` is not a valid C# code. The problem is old as hell - from the introduction of the `IQueryable`. Cannot be solved w/o expression tree manipulation. Addressed by LINQKit, NeinLINQ, AutoMapper and other packages. – Ivan Stoev Feb 06 '19 at 08:14
@AnkushMadankar p.OwnerId was put there by me by mistake. The example I wrote is not the real scenario and when I translated the error I put the p.OwnerId by mistake. But it did work for me in the end. I use it like this Where(IsShippedOrInProgress()) and not .Where(p => IsShippedOrInProgress()). The problem that does occur is what will happen if you want to do something like that Where(p => p.IsConditionOne() || p. IsContionTwo()). Will you need to merge them to a new method? – Tal Humy Feb 06 '19 at 08:20
1

@TalHumy Please check this post for detail about combining multiple expression inside lamba: https://stackoverflow.com/questions/457316/combining-two-expressions-expressionfunct-bool – Ankush Madankar Feb 06 '19 at 08:51

Ramesh · Answer 2 · 2019-02-05T17:36:29.577

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

Hi Ramesh, Thank you for your help. I hope for a simpler solution but this one worked — Tal Humy, Feb 05 '19 at 08:11
@TalHumy - Ankush answer would be simpler. Replace the method content with what he has given. — Ramesh, Feb 05 '19 at 09:45

Entity Framework Core - Use Extension Methods Inside Queryable

2 Answers2

Linked