Entity Framework - Dynamic filter within .Select()

Question

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!

ken2k · Accepted Answer · 2012-01-17 21:36:39Z

1

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

answered Jan 17, 2012 at 21:36

ken2k

49.2k12 gold badges119 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Steve Wilkes · Accepted Answer · 2012-01-17 21:32:48Z

1

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

answered Jan 17, 2012 at 21:32

Steve Wilkes

7,1433 gold badges31 silver badges33 bronze badges

Collectives™ on Stack Overflow

Entity Framework - Dynamic filter within .Select()

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related