How to create query builder from dynamic multiple tables by ASP. Net Core?

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 theses inputs, to generate dynamic report, I searched how can create SQL query, but most ways were with one table,, but in my project, there is must multiple tables (inner join),,

My question is how can create SQL query with multiple tables from DB? I mean how to write inner join SQL query after receiving the selected table and columns, (example: [table1{column1, .....}, table2{columns2,....},....]) ,,

Thank you for your attention. I am waiting for your guide

Answer 1

To create a dynamic SQL query with multiple tables, you can follow these steps:

1. Create a DbContext class that will be used to access the database. This class should inherit from the DbContext class provided by Entity Framework.
2. Define the models for the tables you will be querying. Each model should correspond to a table in the database and should have properties that match the columns in that table.
3. Define a method that will build the SQL query based on the user input. This method should accept a list of tables and columns as parameters.
4. Use the LINQ Join method to join the tables together. You can use the Select method to select the columns you want to include in the result set.
5. Build the SQL query by converting the LINQ expression to a string using the ToString() method.

public class MyDbContext : DbContext
{
    public DbSet<Table1> Table1 { get; set; }
    public DbSet<Table2> Table2 { get; set; }

    // other tables

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // configure the database connection
    }
}

public class Table1
{
    public int Id { get; set; }
    public string Column1 { get; set; }

    // other columns
}

public class Table2
{
    public int Id { get; set; }
    public string Column2 { get; set; }

    // other columns
}

public class QueryBuilder
{
    private readonly MyDbContext _dbContext;

    public QueryBuilder(MyDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    public string BuildQuery(List<string> tables, List<string> columns)
    {
        var query = _dbContext.Table1.AsQueryable();

        foreach (var table in tables.Skip(1))
        {
            var joinType = "inner";
            // determine join type based on user input

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

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

        return query.ToString();
    }
}

In this example, the QueryBuilder class takes an instance of the MyDbContext class in its constructor. The BuildQuery method accepts a list of tables and columns and uses the LINQ Join method to join the tables together. The result is then projected to a new anonymous type containing only the selected columns. Finally, the LINQ expression is converted to a string using the ToString() method.

Hope it helps!