How to dynamically build MongoDB queries and object to return based on user input in C#?

I have a specific requirement in my .NET application, utilizing the ABP Framework, where I need to dynamically build MongoDB queries and objects based on user input. To illustrate, I've included a snippet of code below, which focuses on retrieving records grouped by a specified period. I want to ensure that the code is well-optimized and adheres to best practices.

To understand what is a Record, consider there is a Document where each row has self-contained information and they have some hierarchical relationships with others rows.

public class RecordRepository : MongoDbRepository<MyRecordsMongoDbContext, Record, Guid>, IRecordRepository
{
    public async Task<List<RecordValuesGroupByPeriod>> GetRecordsGroupByPeriod(string recordName, string? period)
    {
        var groupByPeriod = from record in (await GetMongoQueryableAsync())
                            where record.Name == recordName && 
                            (period.IsNullOrEmpty() || record.Ancestor.TextualFields["PERIOD"] == period)
                            group record by record.Ancestor.TextualField["PERIOD"] into group
                            select new RecordValuesGroupByPeriod()
                            {
                                GroupName= group.Key,
                                Record = group.ToList()
                            };

        return await groupByPeriod.ToListAsync();
    }

    public class RecordValuesGroupByPeriod
    {
        public string GroupName { get; set; }
        public List<Record> Records { get; set; }
    }

    public class Record
    {
        public Guid Id { get; protected set; } = Guid.NewGuid();
        public Guid DocumentId { get; set; }
        public string Name { get; set; }
        public Dictionary<string, string?> TextualFields { get; set; } = [];
        public Dictionary<string, DateTime?> DateFields { get; set; } = [];
        public Dictionary<string, decimal?> DecimalFields { get; set; } = [];
        public Record? Ancestor { get; set; } = null;
        ...
    }
}
  

In this way, I find myself writing a dedicated function for each conceivable user query and explicitly declaring the return object. This approach leads to a proliferation of functions, making the codebase less adaptable and more difficult to maintain.

I'm seeking advice on how to enhance the dynamic nature of this code, particularly in the context of constructing MongoDB queries and objects dynamically based on user input. Furthermore, insights into optimizing this code are eagerly welcomed.

To illustrate the challenge, consider the following scenarios:

• User A wishes to retrieve record A1 where A1.Date is equal to 2024.
• User B desires to retrieve record B1 where B1.Date is equal to 2023.

These scenarios are manageable. However, when the complexity increases:

• User A seeks to retrieve the record A1 joined to B1 based on A1.Category matching B1.Category.
• User B wants to retrieve the attribute B1.Category for the record identified by B1.Id.

In such cases, every minor modification to a query necessitates the creation of a new function. Similarly, adapting the function return type becomes cumbersome, as a custom output may not align with (in fact, it often won't) the pre-defined entities.

Your insights and suggestions on improving this structure or highlighting potential pitfalls in the existing implementation would be immensely valuable.

Thank you in advance for your assistance!

I have already tried:

In navigating this issue, and considering both my research findings and performance constraints, the following key realizations emerged:

• Reflection Constraint: I am bound by the constraint of not utilizing Reflection, limiting my ability to dynamically inspect and manipulate types during runtime.

• Limitation of LINQ with Dynamic Types: LINQ poses limitations when it comes to dynamically typed queries. This restriction hampers the seamless integration of dynamic typing within the LINQ queries, limiting the flexibility I require.

• Anonymous Type Conversion: A notable constraint arises from the inability to directly return a new object derived from the result of a query.

  public async Task<List<object>> GetRecordsWithAncestors(string recordName)
  {
      var recordsWithAncestors = from record in (await GetMongoQueryableAsync())
                               where record.Name == recordName
                               select new 
                               {
                                   Name = Record.Name,
                                   AncestorName = Record.Ancestor.Name
                               };
  
      return await recordsWithAncestors.ToListAsync();
  }