CODEWITHSUNDEEP
c#linq
Tom
Tom

Reputation: 71

LINQ query to get count of joined record

Can somebody help me out writing a LINQ query to get record that are joined? I have two models below. I want to get requirements count that belong to given project and has one or more ProjectTest joined to it. 

public class ProjectTest
{
    public int ProjectTestID { get; set; }
    public int ProjectID { get; set; }
    public String Objective { get; set; }
    public String Category { get; set; }
    public String SubCategory { get; set; }
    public String Tags { get; set; }
    public virtual ICollection<ProjectRequirement> ProjectRequirements { get; set; }
    public virtual ICollection<ProjectTestStep> ProjectTestSteps { get; set; }
}

public class ProjectRequirement
{
    public int ProjectRequirementID { get; set; }
    public int ProjectID { get; set; }

    [Display(Name = "Req No.")]
    public String ProjectRequirementIDStr { get; set; }

    [Display(Name = "Module")]
    public String ModuleName { get; set; }

    [Display(Name = "Description")]
    public String Description { get; set; }

    public virtual ICollection<ProjectTest> ProjectTests { get; set; }
}

I just tried to write it as follows but does not seem working. 

db.ProjectRequirements
  .Where(e => e.ProjectID == activeProjectID && 
              e.ProjectTests
                 .Select(ept => ept.ProjectTestID)
                 .Count() > 0)
  .Select(e => e.ProjectRequirementID)
  .Count();

Upvotes: 1

Views: 65

Answers (1)

Gilad Green
Gilad Green

Reputation: 37299

Currently you are counting the number of ProjectRequirement objects that have a given id and that have at least 1 ProjectTest.

If you want to count the amount of ProjectTests you have for a given ProjectId:

var number = db.ProjectRequirements.Where(e => e.ProjectID == activeProjectID)
                                   .Sum(e => e.ProjectTests.Count());

Upvotes: 1

Related Questions