Optimizing Nested Loop When Looking for Matching Properties

Question

I have an Entity Framework query that is doing some joins between tables to give me some data which is then mapped to a list of objects. The objects in the list have a structure like so:

public ListObject
{
    public Panel Panel
    public Code Code
}

Panel and Code just have simple properties such as name, Id, etc.

I am looping over the list of objects and doing a little processing before adding them into a Dataset. That code looks like this:

foreach (var l in listObject)
{
  var codeString = string.Empty;

  if (l.Code != null)
  {
    listObject.Where(x => string.Equals(x.Code.Id, l.Panel.Id))
       .DistinctBy(x => x.Code.Name)
       .ForEach(x => codeString += $"{x.Code.Name},")
  }

  ...add to DataSet
}

The idea is that if I have data like

Panels:

Id

1
1
1
2

And Codes like:

Id | Name

1  | hi
1  | hi
1  | hello
3  | bye

that when adding to my dataSet, I have for every inserted row, the complete comma separated string of distinct names. So the rows for Id 1 would look like

Id | Names
1  | hi, hello
1  | hi, hello
1  | hi, hello
3  | bye

Most of the time, the Code object is null and processing on this list is very fast. In the rare case that I have 100,000 Panels and 100,000 Codes, this is taking a long time to process so I am looking at how I can optimize this.

I have looked into breaking apart my query so that I have just a list of Panels and then my Codes go into a dictionary and can do faster processing there, but splitting this into two db queries every time also doesn't seem ideal. Is there a better way to get the distinct names of Codes associated with Panels?

Answer 1

You can try using Linq for objects on listObject, e.g.

var dict = listObject
  .Where(x => x.Code != null)
  .GroupBy(x => x.Code.Id, 
           x => x.Code.Name)
  .ToDictionary(group => group.Key, 
                group => string.Join(", ", group.Distinct()));

Here, I've materialized the result as a dictionary, Key id Code.Id and Value is comma separated distinct Code.Names:

{ 1, "hi, hello" }
{ 3, "bye" }

then you can loop and add to DataSet:

foreach (var l in listObject) {
  if (l.Code != null) {
    var id = l.Code.Id;
    var name = dict[l.Code.Id];

    ... Add to DataSet
  }
}