How can get the data from dynamic columns by LINQ To Entities in ASP. Net core C#?

Question

I want to build a dynamic report by Asp.net core -EF-, that is received tables and columns names from user, and then create SQL query depended on these inputs and after that display report by user's inputs, I tried this solution, but I faced some issue :

public string GetReportDataAsync(int Report_Type, List<string> tables, List<string> columns, DateTime Start_Date, DateTime End_Date)
        {
           
            var query = db.Children.AsQueryable();

            foreach (var table in tables.Skip(1))
            {
                var joinType = "inner";
                

                query = query.Join(db.Set(table), t1 => t1.Child_ID, t2 => t2.Id, (t1, t2) => new { t1, t2 }, joinType);
            }

            query = query.Select($"new {{ {string.Join(", ", columns)} }}");

            return query.ToString();




        }
 

query = query.Join(db.Set(table), t1 => t1.Child_ID, t2 => t2.Id, (t1, t2) => new { t1, t2 }, joinType);

in this line Screenshot 2023-04-30 1.png ,,

query = query.Select($"new {{ {string.Join(", ", columns)} }}");

and in this line Screenshot 2023-04-30 2.png , this compiler issue appear { Compiler Error CS0411: The type arguments for method 'method' cannot be inferred from the usage. Try specifying the type arguments explicitly.}

I tried to solve it but it is still appear,, what I should do ?? ,, please anyone guide me ,, thank you for your support..

Answer 1

The point of EF is to return strongly typed results. To create a type at runtime rather than compile time, you would need to use system.reflection.emit.

better solution is to use a DataReader directly with ado.net.