CODEWITHSUNDEEP
c#.netentity-frameworklinqlist
KungFuMaster
KungFuMaster

Reputation: 101

Populating a list within a list in C# not using foreach loop. better way?

I have a list of objects within a list of objects (List-ParentClass) that has as one of its objects a nested list (List-ChildClass). To populate List-ChildClass I have used a foreach loop as shown below. I have also nested a linq query as show below.

At this point I am having some performance issues and I feel like there is a better way to do this that I am just not finding.

Question: How could I do this better/faster?

Note - This is a Web based .net MVC application written in C#. I use EF back to a SQL database.

public class ParentClass
{
    public int pcid { get; set; }
    public List<ChildClass> ChildClassList { get; set; }
}

public class ChildClass
{
    public int pcid { get; set; }
    public int ccid { get; set; }
}

public class DoWork 
{
    public void ExampleMethodForEach()
    {
        List<ParentClass> ParentClassList = new List<ParentClass>();

        foreach(ParentClass a in ParentClassList)
        {
            a.ChildClassList = EFDatabase2.where(b => b.pcid == a.pcid).select(b => b.ccid).ToList();
        }
    }

    public void ExampleMethodLinq()
    {
        var ParentClassList = (from a in EFDatabase
                               select new ParentClass
                               {
                                   ccid = a.ccid,
                                   pcid = (from b in EFDatabase2
                                           where b.pcid == a.pcid
                                           select b.ccid).ToList()
                                   //something like this were I nest a query
                               }).ToList();
    }
}

Upvotes: 6

Views: 2049

Answers (2)

Ivan Stoev
Ivan Stoev

Reputation: 205539

The best way when working with relational databases and LINQ is to use joins to correlate data. In your case, the most appropriate is group join: 

var ParentClassList =
    (from p in EFDatabase
     join c in EFDatabase2 on p.pcid equals c.pcid into children
     select new ParentClass
     {
         pcid = p.pcid,
         ChildClassList =
             (from c in children
              select new ChildClass
              {
                  pcid = c.pcid,
                  ccid = c.ccid
              }).ToList()
     }).ToList();

which should give you a nice fast single database query.

P.S. Hope your EFDatabase and EFDatabase2 variables refer to two tables inside one and the same database.

Upvotes: 2

Pierre-Alain Vigeant
Pierre-Alain Vigeant

Reputation: 23083

You are hitting your database multiple times. You have a N+1 issue.

What I suggest is to query all parents first, but excluding the children data. Then get the ID of all parents that you retrieved and put it inside an array. We will use that array to create a IN clause in SQL.

After loading all the children using the array of parent IDs, map them to a Lookup using ToLookup using the parent ID as the key and use a foreach to assign the list of children to the parent.

var parents = EFDatabase2.Parents.Where(...).Select(p => new ParentClass { pcid = p.pcid }).ToList();
var ids = parents.Select(p => p.pcid).ToArray();
var children = EFDatabase2.Children.Where(c => ids.Contains(c.ccid)).Select(c => new ChildClass { pcid = c.pcid, ccid = c.ccid }).ToLookup(c => c.pcid);

foreach (var parent in parents)
{
    parent.Children = children[parent.pcid];
}

In this case, you will only do two queries to your database.

Upvotes: 0

Related Questions