CODEWITHSUNDEEP
c#linqlinq-to-entitiesentity-framework-5
Tomas
Tomas

Reputation: 18097

Refactor Linq code and "LINQ to Entities does not recognize the method"

I am trying to refactor Linq to Entity query to prevent writing duplicate code but I can't avoid a "LINQ to Entities does not recognize the method" exception. The reason is IsUserActive method.

I am providing the code below as example

public static bool IsUserActive(this User user)
{
 return user.Orders.Any(c => c.Active && (c.TransactionType == TransactionType.Order || c.TransactionType == TransactionType.Subscription));
}

public IQueryable<UserView> GetView()
{
 return context.users.Select(p => new UserView
            {
                Id = p.Id,
                Active =p.IsUserActive()

            });
}

Is it possible to refactor Linq code to prevent duplication in my situation?

Upvotes: 5

Views: 425

Answers (2)

Akash Kava
Akash Kava

Reputation: 39916

This is little tricky, but this is how we are doing it. You can combine multiple IQueryables into one as shown below.

public static IQueryable<Order> ActiveOrdersQuery(this MyDBEntities db)
{
    return db.orders.Where(
           o=> o.Active && 
               (o.TransactionType == TransactionType.Order ||
                o.TransactionType == TransactionType.Subscription )));

}

public IQueryable<UserView> GetView()
{
    var q = context.ActiveOrdersQuery();
    return context.users.Select( x=> new UserView{
         Id = x.Id,
         Active = q.Any( o=> o.UserId == x.Id)
    });
}

The only problem is you have to explicitly compare foreign keys. But this results in exactly same SQL and will perform same.

This way you can refactor common queries and use them inside your views.

Upvotes: 0

Rawling
Rawling

Reputation: 50104

If you want to use your method in a lot of places, I don't think you can do anything except store it as an expression and then referring to that expression wherever you were calling the original method:

public static Expression<Func<User, bool>> IsUserActive = user =>
    user.Orders.Any(c => c.Active &&
                         (c.TransactionType == TransactionType.Order ||
                          c.TransactionType == TransactionType.Subscription));

public IQueryable<UserView> GetView()
{
    return context.users.Select(p => new UserView
        {
            Id = p.Id,
            Active = IsUserActive(p)
        });
}

EDIT: OK, so this doesn't work. You can't just "call" an expression like this. The only alternative I can think of is to turn your whole Select parameter into an expression:

public static Expression<Func<User, UserView>> UserToUserView = user =>
    new UserView
        {
            Id = user.Id,
            Active =  user.Orders.Any(c => c.Active &&
                         (c.TransactionType == TransactionType.Order ||
                          c.TransactionType == TransactionType.Subscription)
        };

public IQueryable<UserView> GetView()
{
    return context.users.Select(UserToUserView);
}

Upvotes: 3

Related Questions