Dynamic LINQ Sub Query

Question

I have LINQ query that I want to generate dynamically:

var groupData =
    from l in data
    group l by l.Field1 into field1Group
    select new MenuItem()
    {
        Key = field1Group.Key,
        Count = field1Group.Count(),
        Items = (from k in field1Group
                 group k by k.Field2 into field2Group
                 select new MenuItem()
                 {
                     Key = field2Group.Key,
                     Count = field2Group.Count()
                 }).ToList()
    };

The ultimate goal is to be able to dynamically group the data by any combination of fields with no limit on the nested queries.

I can get as far as the first level but I'm struggling with the nested sub queries:

string field1 = "Field1";
string field2 = "Field2";

var groupDataD =
    data.
    GroupBy(field1, "it").
    Select("new ( it.Key, it.Count() as Count )");

Is this possible with chained dynamic LINQ? Or is there a better way to achieve this?

You can build complex queries dynamically using expression trees. Here's a very short sample of what can be achieved: https://learn.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/expression-trees/how-to-use-expression-trees-to-build-dynamic-queries and the `Expression` class, which is what you'll be using to build your dynamic queries: https://learn.microsoft.com/en-us/dotnet/api/system.linq.expressions.expression?view=netframework-4.7.2 — Marius Bughiu, Jan 28 '19 at 21:46
@DaveW, did my answer help you solve your issue? If so, please mark it as accepted. If not, let me know and I'll try to help further. — OfirD, Dec 07 '19 at 20:04

score 1 · Answer 1 · answered Aug 20 '19 at 15:37

The following should work (though personally I would rather avoid using such code):

Follow this answer to add the following in ParseAggregate, :

Expression ParseAggregate(Expression instance, Type elementType, string methodName, int errorPos)
{
   // Change starts here
   var originalIt = it;
   var originalOuterIt = outerIt;
   // Change ends here

   outerIt = it;
   ParameterExpression innerIt = Expression.Parameter(elementType, elementType.Name);
   it = innerIt;
   Expression[] args = ParseArgumentList();

   // Change starts here
   it = originalIt;
   outerIt = originalOuterIt;
   // Change ends here

   ...
}

Add Select, GroupBy, ToList into IEnumerableSignatures, and respective conditions in ParseAggregate, as explained in this answer:

interface IEnumerableSignatures
{
  ...
  void GroupBy(object selector);
  void Select(object selector);
  void ToList();
  ...
}

Expression ParseAggregate(Expression instance, Type elementType, string methodName, int errorPos)
{
   ...
   if (signature.Name == "Min" || 
       signature.Name == "Max" || 
       signature.Name == "GroupBy" || 
       signature.Name == "Select")
   ...
}

Finally, Your query would be:

string field1 = "Field1";
string field2 = "Field2";

var result =
    data
    .GroupBy(field1, "it")
    .Select($@"new ( 
       it.Key,
       it.Count() as Count,
       it.GroupBy({field2})
         .Select(new (it.Key, it.Count() as Count))
         .ToList() as Items
    )");

Note that "it" holds a different instance when used in the parent query vs. the subquery. I tried to take advantage of "outerIt" to overcome this conflation, but unfortunately without success (but maybe you'd succeed? maybe 1, 2 would help)

A simple example for future reference:

public class Person
{
    public string State { get; set; }
    public int Age { get; set; }
}
public static Main()
{
    var persons = new List<Person>
    {
       new Person { State = "CA", Age = 20 },
       new Person { State = "CA", Age = 20 },
       new Person { State = "CA", Age = 30 },
       new Person { State = "WA", Age = 60 },
       new Person { State = "WA", Age = 70 },
    };
    var result = persons
        .GroupBy("State", "it")
        .Select(@"new ( 
           it.Key,
           it.Count() as Count,
           it.GroupBy(Age)
             .Select(new (it.Key, it.Count() as Count))
             .ToList() as Items
        )");
     foreach (dynamic group in result)
     {
         Console.WriteLine($"Group.Key: {group.Key}");
         foreach (dynamic subGroup in group.Items)
         {
             Console.WriteLine($"SubGroup.Key: {subGroup.Key}");
             Console.WriteLine($"SubGroup.Count: {subGroup.Count}");
         }
     }
}

Dynamic LINQ Sub Query

1 Answers1

Linked