CODEWITHSUNDEEP
asp.net-mvcentity-frameworklinqlinq-to-sql
jwize
jwize

Reputation: 4165

How pass a LINQ expression to a function that returns a IQueryable

I have a few methods that act on a query one that is for a simple where filter an the other with an expression to be used in the where filter and the third also has an expression as a parameter.

public static IQueryable<Employee> FilterExpression(this IQueryable<Employee> employees,
        Expression<Func<Employee, Boolean>> expression)
{
    return employees.Where(expression);
}

public static IQueryable<Employee> Active(this IQueryable<Employee> employees)
{
    return employees.Where(e => e.IsActive);
}

The first two execute fine without any problems.

var active = db.Employees.Active().Take(5).ToList();
var activeExpression = db.Employees.Take(5).FilterExpression((e) => e.IsActive).ToList();

The next one is being called in another extension method that returns a delegate for a select on a tasks collection.

public static Expression<Func<SpwTask, TaskView>> Tasks(this TaskService service)
{
    return (x) => new TaskView()
        {
            NotifyList = db.TaskContacts.JoinedEmployees
                  (t => t.TaskId == x.Id).Select(Projections.SimpleEmployees)
        };
}

public static IQueryable<Employee> JoinedEmployees(this IQueryable<TaskContact> contacts,
        Expression<Func<TaskContact, Boolean>> expression)
{
    var id = ServicesRoot.Company.Id;
    return from c in contacts.Where(expression)
           join e in db.Employees on c.EmployeeId equals e.Id
           where e.CompanyId == id
           select e;
}

The calling code looks like this

// db is the DbContext and the Tasks is the extension method
...
return db.Tasks.Select(this.Tasks()); 
...

The error I get is this:

System.NotSupportedException: 'LINQ to Entities does not recognize the method 'System.Linq.IQueryable1[SafetyPlus.Data.Models.Employee] JoinedEmployees(System.Linq.IQueryable1[SafetyPlus.Data.TaskContact], System.Linq.Expressions.Expression1[System.Func2[SafetyPlus.Data.TaskContact,System.Boolean]])' method, and this method cannot be translated into a store expression.'

Are there any ways to work around this problem? It would be really nice to reused this an other similar queries. It seems like this should work.

Inline version:

public static Expression<Func<SpwTask, TaskView>> Tasks(this TaskService service)
{
    return (x) => new TaskView()
        {
            NotifyList = 
                (from c in service.db.TaskContacts.
                where c.TaskId == x.Id
                join e in db.Employees on c.EmployeeId equals e.Id
                where e.CompanyId == id
                select e).Select(Projections.SimpleEmployees)
        };
}

When I run the code inline version of the query I get a single SQL produced which is optimized and what I want but is not reusable.

I had thought the Compile() solution given below was the answer that I needed, at first, but upon looking at the SQL Profiler I realize that each row of the outer task query is running a single query for each task instead of the single query for the whole dataset. This pretty much defeats the purpose of reusing the query.

Upvotes: 0

Views: 184

Answers (1)

Krystian Sitek
Krystian Sitek

Reputation: 588

You should pass it as a delegate without invoking it in the Select and compile before use.

Try this: db.Tasks.Select(Tasks.Compile());

Upvotes: 1

Related Questions