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

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

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

Ramesh

Related Questions

Entity Framework 6 - Extension Methods for DbFunctions

Entity Framework 6 is stupid in this case. It just try to get associated ...[DbFunction]... attribute from method, and replaces the method with ...DbExpression... using ...System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.Translator...private... class. And so, there is no way to register custom translator from outside code. Also, .NET does ...
c# entity-framework entity-framework-6
asked by hazzik

Entity framework IQueryable extension methods do not work as a sub query

You can make a partial class for your ...User... model with a static class inside:...partial class User { public static class Q { public static Expression<Func<User,bool>> IsStudent { return x => x.Type == (int)UserTypes.Student; } } } ...Then your query would look like this:...var vm = from o in db.O...
c# entity-framework entity-framework-6 linq linq-to-sql
asked by Alexander Derck

Entity Framework extension method ICollection / IQueryable

I need to check the same specific condition (...where... clause) many times: ...return _ctx.Projects.Where(p => p.CompanyId == companyId && (p.Type == Enums.ProjectType.Open || p.Invites.Any(i => i.InviteeId == userId))).ToList() ...The part after the '&&' will cause that the user isn't able to ...
.net c# entity-framework entity-framework-core linq
asked by ErazerBrecht

Entity Framework core : DbContextOptionsBuilder' does not contain a definition for 'usesqlserver' and no extension method 'usesqlserver'

We install Microsoft.EntityFrameworkCore.SqlServer NuGet Package. ...Microsoft.EntityFrameworkCore.SqlServer...PM > Install-Package Microsoft.EntityFrameworkCore.SqlServer ...Then, ... services.AddDbContext<AspDbContext>(options => options.UseSqlServer(config.GetConnectionString("optimumDB"))); ...Update... do not forget to add ...using Mi...
asp.net-core entity-framework-core
asked by Win

How to save changes twice in one method using Entity Framework Core

Using Entity Framework Core i am trying to save changes twice for the same entity collection.... public async Task Save(IEnumerable<Request> request) { var entities = request.Select(x => new MyEntity() { FileName = x.FileName, Status = Status.Downloading ...
entity-framework entity-framework-6 entity-framework-core
asked by LP13

Entity framework core Output parameter not working in store procedure using FromSql method (Linq)

I am trying to get output value from the ...dbcontext... using the method ...FromSql(...).......If i execute it on SSMS, it works but not working in EF core....My SP param:... @totalItems VARCHAR(MAX) OUTPUT ...I've removed other params for readable and point out the issue. The records are coming but the ...OUTPUT... parameter is always ...null....
c# entity-framework-core linq sql sql-server
asked by Robin

Entity Framework Core - Call A Populate Extension Method Inside Include

I will explain my issue using an example. Lets say I have this following classes and methods (I created them only for this example)... public class Student { public string Name { get; set; } public string Id { get; set; } public Subject Expertise { get; set; } } public class Subject { public string...
c# entity-framework-6 entity-framework-core
asked by Tal Humy

Using methods inside EF Core queries

I've been working with EF and EF Core for a long time, but there's something that has always given me problems, one way or the other, that is using methods inside queries, specially while mapping....For example, right now I have this code... public IEnumerable<SelectObjDTO> SelectSearch() { return Context.Usuarios.IncludePerson().ToS...
.net-core asp.net c# ef-core-2.2 entity-framework-core
asked by Javier Goldman

Entity Framework Core 3.0 - Lambda expression used inside Include is not valid

Here below I am using lambda expression in LINQ Includes for 1 level, to fetch a list of customers using three entities, the main one is for all Customers and has only 2 properties Id and bool: IsCompany, the second one has only those that are companies, and the third one has only those that are individuals. The below code was working using .Net Co...
asp.net-core c# entity-framework-core
asked by Sami-L

Entity Framework Core 3.1 is throwing "Include has been used on non entity queryable" exception

I have an ASP.NET Core 3.1 based project where I am using Entity Framework Core 3.1 as an ORM....I have the following two entity models...public class PropertyToList { public int Id { get; set; } public int ListId { get; set; } public int UserId { get; set; } public int PropertyId { get; set; } // ... Other properties removed f...
.net-core .net-core-3.1 asp.net-core entity-framework entity-framework-core
asked by user12310517

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

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