Rebuild Parant->Child->GrandChild hierarchy with LINQ

Vote count:

0

The problem

I have a hierarchy that I am pulling from the database and trying to restore using LINQ. When I run my LINQ query against the collections, it does not appear to be hitting my grand-child objects.

Object setup

The hierarchy of my objects is as follows

One Project -> Many Sections
One Section -> Many BidItems
One BidItem -> Many SubItems

They are all related through foreign keys in the database and mapped to my model objects. The following are simplified versions of the models.

Models

public class Section
{
    public int SectionId { get; set; }

    public int ProjectId { get; set; }
}

public class BidItem
{
    public int BidItemId { get; set; }

    public int SectionId { get; set; }
}

public class SubItem
{
    public int SubItemId { get; set; }

    public int BidItemId { get; set; }
}

View Models

public class SectionViewModel : BaseChangeNotify
{
    private readonly Section section;
    private readonly List<BidItemViewModel> bidItems;

    public SectionViewModel(Project project, Section section)
    {
        var repository = new ProjectRepository();

        this.section = section;

        this.bidItems = new List<BidItemViewModel>(
            (from item in repository.GetBidItemsBySectionId(section.SectionId)
             select new BidItemViewModel(project, item)).ToList());
    }

    public SectionViewModel(Project project, Section section, List<BidItemViewModel> bidItemsForSection)
    {
        this.section = section;
        this.bidItems = bidItemsForSection;
    }
}

public class BidItemViewModel : BaseChangeNotify
{
    private BidItem bidItem;

    private List<SubItem> subItems;

    public BidItemViewModel(Project project, BidItem bidItem, List<SubItem> subItems = null)
    {
        var repository = new ProjectRepository();

        this.bidItem = bidItem;

        if (subItems == null)
        {
            subItems = repository.GetSubItemsByBidItemId(bidItem.BidItemId);
        }

        this.subItems = subItems;
    }
}

You can see in one constructor in each view model, I was hitting a repository to fetch the children objects. I wanted to re-write it because it's not performing well. There could be a dozen sections, each with 100+ BidItems. Each BidItem can have 100+ SubItems. So for a project having 5 sections, I hit the database 50,000 times during the apps start up (takes about 2.9 seconds).

Troublesome source code

I have refactored it so that I just make 3 calls, one to fetch all sections for a project, one for all BidItems in a project and one for all SubItems in a project. Now I need to reconstruct the hierarchy.

I tried using Lambda initially:

List<Section> projectSections =
    repository.GetSectionsByProjectId(ProjectId).Where(section => section.SectionId != 0).ToList();
List<BidItem> bidItemCollection = repository.GetBidItemsByProjectId(ProjectId);
List<SubItem> subItemCollection = repository.GetSubItemsByBidItemId(ProjectId);

foreach (var sectionViewModel in projectSections.Select(section => new SectionViewModel(project, section)))
{
    bidItemCollection.ForEach(bidItem =>
    {
        var bidItemViewModel = new BidItemViewModel(project, bidItem,
            subItemCollection.Where(subItem => subItem.BidItemId == bidItem.BidItemId).ToList());
    });

    sectionViewModels.Add(sectionViewModel);
}

and this worked alright, but was slow. My original approach would take 2.9 seconds during start up to return all Sections, BidItems and SubItems. The Lambda took 2.3 seconds. I then tried a LINQ query.

List<Section> projectSections =
    repository.GetSectionsByProjectId(ProjectId).Where(section => section.SectionId != 0).ToList();
List<BidItem> bidItemCollection = repository.GetBidItemsByProjectId(ProjectId);
List<SubItem> subItemCollection = repository.GetSubItemsByBidItemId(ProjectId);

sectionViewModels = new List<SectionViewModel>(
    from section in projectSections
    select new SectionViewModel(
        project,
        section,
        bidItemCollection.Where(c => c.SectionId == section.SectionId)
            .Select(
                bidItem =>
                    new BidItemViewModel(
                        project,
                        bidItem,
                        subItemCollection.Where(subItem => subItem.BidItemId == bidItem.BidItemId)
                            .ToList())).ToList())).ToList();

This returned the fastest, at 0.3 seconds, but each of the BidItems contained an empty SubItem collection. For some reason, my SubItems were not populating the BidItem view model constructor like they should. I set a breakpoint within the subItemCollection.Where() lambda and it never gets hit.

I would really appreciate some guidance on what I am doing wrong with my LINQ. I'm a bit new to LINQ so I know I'm doing something wrong that is a simple fix.

Thanks in advance!

Johnathon.

asked 28 secs ago

Johnathon Sullinger

331