CODEWITHSUNDEEP
c#asp.net.netlinqlinq-to-sql
yanivca
yanivca

Reputation: 23

Select parents with specific children in LINQ

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.

Upvotes: 1

Views: 1600

Answers (2)

kaushik
kaushik

Reputation: 70

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.

Upvotes: 0

Servy
Servy

Reputation: 203840

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.

Upvotes: 2

Related Questions