CODEWITHSUNDEEP
c#.netentity-frameworkiqueryable
MrBliz
MrBliz

Reputation: 5898

Entity Framework and Iqueryable issue

Given the Following basic Structure

public class Project
{
    public int ProjectId {get;set;}
    ICollection<SubProject> SubProjects {get;set;}

}

public class SubProject
{
    public int SubProjectId {get;set;}
    public int SubProjectId {get;set;}
    public int PrinterId {get;set;}
    public int AgencyId {get;set;}
    public int Status
    //Oodles more  properties
}

public class ProjectSearchResult
{
   public int ProjectId {get;set;}
   public int NoOfSubProjects {get;set;}
   //Loads more properties
}

I need to get a list of projects and a count of how many sub-projects they have, sometimes by Agency, Sometimes by Printer, and Sometimes by Status,

Instead of doing something like the below

public IQueryable<ProjectSearchResult> GetProjects(int? status, int? agencyId, int?     printerId)
{

    var projects = db.Projects.Include(x=>x.SubProjects);
    if(status.HasValue)
         return projects.Select(proj=> new ProjectSearchResult
                               {
                                  ProjectId = proj.ProjectId,
                                  NoOfSubProjects = proj.SubProjects.Count(s=>s.Status = status)

};

if(agency.HasValue)
         return projects.Select(proj=> new ProjectSearchResult
                               {
                                  ProjectId = proj.ProjectId,
                                  NoOfSubProjects = proj.SubProjects.Count(s=>s.AgencyId= agency)

};
if(printer.HasValue)
         return projects.Select(proj=> new ProjectSearchResult
                               {
                                  ProjectId = proj.ProjectId,
                                  NoOfSubProjects = proj.SubProjects.Count(s=>s.PrinterId= printer)

};

Is there any way I can make it more generic and keep it as an IQueryable, without returning all the sub projects and doing the count in memory?

Upvotes: 2

Views: 305

Answers (1)

Daniel Pratt
Daniel Pratt

Reputation: 12077

You could do it like this:

public IQueryable<ProjectSearchResult> GetProjects(Expression<Func<SubProject, bool>> predicate)
{
    return
        from p in db.Projects
        select new ProjectSearchResult {
            ProjectId = p.ProjectId,
            NoOfSubProjects = p.SubProjects.Count(predicate),
        };
}

In case it's not obvious, you can then do something like this:

var projects = GetProjects(_ => _.Status == projectStatus);

Upvotes: 2

Related Questions