CODEWITHSUNDEEP
c#entity-framework-4.1
Fred Wilson
Fred Wilson

Reputation: 2207

Entity Framework - Dynamic filter within .Select()

I am using projection to fill ViewModels with data.

In this case, I am working with Projects and Tasks.

I have a form that allows the user to select a project and OPTIONALLY select the person to whom tasks may be assigned within the project.

In my Project ViewModel, I have this ViewModel for tasks:

public IEnumerable<TaskVM> TaskVM { get; set; }

I am filtering the query like so:

var query = db.Projects.AsQueryable();

if (filterProjectId)
{
    query = query.Where(p => p.ProjectId == ProjectId); 
}

if (filterAssignedTo)
{
    query = query.Where(p => p.Tasks.Any(t => t.AssignedTo == "John")); 
}

Question: The above will return all projects that have any task assigned to John, but it will also return tasks assigned to Fred. My question is this: how can I get all of the projects, but ONLY the tasks assigned to John?

I know I can do this:

var resultList = query.Select(p => new ProjectVM
{
    ProjectId = p.ProjectId,
    ProjectName = p.ProjectName,
    TaskVM = p.Tasks.Where(t => t.AssignedTo == "John").OrderByDescending(t => t.TaskName).Select(t => new TaskVM
    {
        TaskId = t.TaskId,
        TaskName = t.TaskName,
        AssignedTo = t.AssignedTo
    })
});

But, I don't know if I need to filter John. The user may want ALL projects with the projectId.

So again, how can I get all of the projects, but ONLY the tasks assigned to John?

Thank you!

Upvotes: 0

Views: 1594

Answers (2)

ken2k
ken2k

Reputation: 48975

From what I understand, you have a sort of dynamic filtering depending on both filterProjectId and filterAssignedTo booleans.

Here's what you could do:

var resultList = db.Projects.Where(z => !filterProjectId || z.ProjectId == MyProjectId).Select(p => new ProjectVM
{
    ProjectId = p.ProjectId,
    ProjectName = p.ProjectName,
    TaskVM = p.Tasks.Where(t => !filterAssignedTo || t.AssignedTo == "John").OrderByDescending(t => t.TaskName).Select(t => new TaskVM
    {
        TaskId = t.TaskId,
        TaskName = t.TaskName,
        AssignedTo = t.AssignedTo
    })
});

Upvotes: 1

Steve Wilkes
Steve Wilkes

Reputation: 7135

You could define the 'assigned to' filter like this:

Expression<Func<Project, bool>> assignedToFilter;

if (filterAssignedTo)
{
    assignedToFilter = p => p.Tasks.Any(t => t.AssignedTo == "John");
}
else
{
    assignedToFilter = p => true;
}

...and then use it like this:

var resultList = query.Where(assignedToFilter).Select(p => new ProjectVM
{
    ProjectId = p.ProjectId,
    ProjectName = p.ProjectName,
    TaskVM = p.Tasks
        .Where(assignedToFilter)
        .OrderByDescending(t => t.TaskName)
        .Select(t => new TaskVM
        {
            TaskId = t.TaskId,
            TaskName = t.TaskName,
            AssignedTo = t.AssignedTo
        })
});

Upvotes: 1

Related Questions