Select parents with specific children in LINQ

Question

I have a database structure with 2 types, that looks like this:

Project ProjectID ProjectName Task TaskID TaskTitle Status ProjectID

Because of all the relations declared on the sql server - the dbml automatically knows about them, and I can get all project's tasks simply by:

myProject.Tasks

So far so good.

I want to display all the projects and their tasks, so the user can filter them by Status. Which means that I want to display only those tasks (and their project, of course), that meets the selected criteria.

For that I have a Repeater, that look somewhat like this:

<asp:Repeater id="rptProjects" runat="server"> <ItemTemplate> <%# Eval("ProjectName") %> (<%# Eval("ProjectID") %>): <asp:Repeater id="rptTasks" DataSource="<%#(Container.DataItem as Project).Tasks %>" runat="server"> <ItemTemplate> <%# Eval("TaskTitle") %> </ItemTemplate> </asp:Repeater> </ItemTemplate> </asp:Repeater>

But I can't figure out the LINQ query needed.

In each of the queries I tried, I get the projects alright (meaning that if none of the project's task meets the selected criteria it's not on the list), but every time Project.Tasks hold all the tasks for the selected project, and never a filtered tasks list.

Servy · Accepted Answer · 2013-06-26 18:58:38Z

Not only do you need to filter the projects queryable itself, you need to project the Tasks relationship to a new one with the given condition:

var query = context.Projects.Where(project => project.Tasks.Any(task => task.Status == someStatus) .Select(project => new { project.ProjectName, Tasks = project.Tasks.Where(task => task.Status == someStatus), });

Note that, because of this change, you'll need to change the data source of the inner repeater to Eval("Tasks") rather than what you have.

Well, that works better then ever before :) Is there a way to covert to a list of something other then anonymous type? Other way then to write a new class that holds PtojectName and Tasks, that is.
@yanivca Sure, just create new items of some other type than an anonymous type...but in this particular context you probably don't need a named type.

kaushik · Accepted Answer · 2013-07-16 09:51:25Z

var TasksForProject = from p in Project join t in Tasks on p.ProjectID equals t.ProjectID where t.Satus !=null and t.status==SomeStatus select new { p,t }

After that you can take this into memory using tolist() and then you can groupby on the basis of properties.

Collectives™ on Stack Overflow

Select parents with specific children in LINQ

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related