How to group a list of entities by a custom condition?

Question

I am using Entity Framework Core in an ASP.NET Core 6 application.

I have the following class:

public class Ledgercode
{
   public Guid Id { get; set; }
   public string Code { get; set; }
}

The 'Code' property is a number in string between 1 and 10 characters, e.g. 1, 10, 100, 1000, 2, 200, 3, 3000000, etc.

I am using the following Dto:

public class LedgercodeDto
{
   public Guid Id { get; set; }
   public string Code { get; set; }
   public List<LedgercodeDto> Children { get; set; }

   public LedgercodeDto(Ledgercode ledgercode)
   {
      Id = ledgercode.Id;
      Code = ledgercode.Code;     
   }
}

I am trying to group the Ledger codes by the 'Code' property where a ledger code with a code of 10 is a child of a ledger code with a code of 1. 100 is a child of 10, 1000 is a child of 100, etc. Furthermore, it is not dependent of the length, since a code of 3000 falls into the group of 3 if there is not any preceding code that matches, (e.g. 300 or 30).

I tried using recursion to determine the children of each ledger code.

public async Task<IEnumerable<LedgercodeDto>> GetLedgercodesAsync()
{
   var ledgercodes = await _context.Ledgercodes
      .AsNoTracking()
      .Where(l => l.Code == "1" || l.Code == "3")
      .Select(l => new LedgercodeDto(l))
      .ToListAsync();

   ledgercodes.ForEach(l => 
   {
      l.Children = GetChildren(_context.Ledgercodes.AsNoTracking().ToList(), l);
   });
}

public List<LedgercodeDto> GetChildren(List<LedgercodeDto> items, LedgercodeDto parent)
{
    var children = items.Where(x => x.Code.StartsWith(parent.Code)).ToList();
    foreach (var child in children)
    {
        child.Children = GetChildren(items, child);
    }
    return children;
}

The problem is that all subsequent codes that start with the parent's code are included. For example if the parent's code is '1', and there are ledger codes with 10 and 100, both are included, instead of just 10, and 10's children should include 100.

Thus, I require a solution where the following ledger codes:

1,10,100,1000,10000000,10000001,10000100,3,30,3095,30951809

gets grouped into this:

-1
 -10
  -100
   -1000
    -10000000
    -10000001
    -10000100
-3
 -30
  -3095
   -30951809

Thanks in advance

Answer 1

What you need is not a group by, but a tree. You can easily find implementations online. I've created a simple one here https://dotnetfiddle.net/Ziomy1, which does not depend on EntityFramework.

Stitching it all together should be quite simple. For simplicity I would assign a clean List to all new LedgercodeDto instances. Then I would fetch all Ledgercode entries that make sense to your business case, including all nesting levels in a single EF call. With all the data in memory it is just a matter of inserting all objects into a tree structure and return the dummy root node children collection.

public class LedgercodeDto
{
   public Guid Id { get; set; }
   public string Code { get; set; }
   public List<LedgercodeDto> Children { get; set; }

   public LedgercodeDto(Ledgercode ledgercode)
   {
      Id = ledgercode.Id;
      Code = ledgercode.Code;     
      Children = new List<LedgercodeDto>();
   }
}

public static void Insert(LedgercodeDto root, LedgercodeDto node) 
{
    var match = root.Children.FirstOrDefault(x => x.Code.StartsWith(node.Code) || node.Code.StartsWith(x.Code));
    if (match != null)
    {
        if (node.Code.StartsWith(match.Code)) 
        {
            Insert(match, node);
        }
        else
        {   
            node.Children.Add(match);
            root.Children.Remove(match);
        }
    }
    else
    {
        root.Children.Add(node);
    }
}

public async Task<IEnumerable<LedgercodeDto>> GetLedgercodesAsync()
{
   var ledgercodes = await _context.Ledgercodes
      .AsNoTracking()
      .Where(l => l.Code.StartsWith("1") || l.Code.StartsWith("3"))
      .Select(l => new LedgercodeDto(l))
      .ToListAsync();

   var root = new LedgercodeDto(new Ledgercode());

   ledgercodes.ForEach(l => Insert(root, l));

   return root.Children;
}