Help me construct this Linq statement

Question

There should be a simple Linq query for what I'm trying to accomplish, but I'm producing some ugly code.

I have two tables, one of issues and another of issue status. There is a one-to-many relationship between issue and issue status. When an issue is created an IssueStatus is also created with the status field set to "Open" when it is closed, another IssueStatus is created with the status field set to "Closed" ... but issues can be re-opened. It seems like I should be able to write something like this:

    public static List<Issue> FindOpenIssues(this IList<Issue> issues) {
        return (
            from issue in issues
            from issueStatus in issue.issueStatus.OrderByDescending(x=>x.CreatedOn).Single() 
            where issueStatus.Status == "Open"
            select issue
            ).ToList();
    }

This obviously fails, but there must be a clean way to do this? Thanks!

Edit: As Reed Copsey points out, my intention is to find current open issues. An issue might contain an element "Open" and at a later date, an element "Closed" ... which is why a simple Where == "Open" won't work, it needs to find the element with the most recent date in the list to determine the status of the issue.

Answer 1

Assuming you are keeping a history of issue statuses for every issue, you can select all issues where the latest issue status (i.e. the first issue status descendingly sorted by creation date) is "Open" using this:

return issues.Where(issue => issue.issueStatus
                                  .OrderByDescending(x => x.CreatedOn)
                                  .First()
                                  .Status == "Open")
             .ToList();

Answer 2

It looks like you're trying to find issues with a status marked "Open". If so, this should work:

public static List<Issue> FindOpenIssues(this IList<Issue> issues) {
    return issues
              .Where(i => i.issueStatus.Any(stat => stat.Status == "Open")
              .ToList();
}

This is different from your original, but based on the method name, I believe may actually provide the intended results (any issues where there is a status of Open).

You can do what would be returned by your original like so:

public static List<Issue> FindOpenIssues(this IList<Issue> issues) {
    return issues
            .Where(i => i.issueStatus
                             // This potentially should be (I left your original logic, though):
                             // .OrderByDescending(stat => stat.CreatedOn)
                             .OrderBy(stat => stat.CreatedOn)
                             .First()
                             .Status == "Open"
                  )
            .ToList();
}

Answer 3

issues.Where(issue=>issue.issueStatus
 .Where(issueStat=>issueStat.Status==Open)
 .OrderBy(x=>x.createdOn).Single()).ToList()