How to get a list of all Table names through asp.net API controller

So I want to get a list of all the table names from the database through a controller as an ASP.net API project. I tried to do it through raw sql query without entity and it looked something like this.

public async Task<ActionResult<IList>> GetAllTableNames()
{
using (var context = new DbContext())


{

List<string> results = context.Database.SqlQuery<string>("SELECT name FROM sys.tables").ToListAsync();
}
}

But when I try to use the SqlQuery method I get the error ” ‘DatabaseFacade’ does not contain a definition for ‘SqlQuery’ and no accessible extension method ‘SqlQuery’ “. Anybody that has any idea how to solve this?

Answer

First create an Helper method in your controller as shown below

using System.Data.SqlClient;

  public async IAsyncEnumerable<string> GetTableNamesAsync()
    {
        using var connection = new SqlConnection(_dbContext.Database.GetConnectionString());
        var command = new SqlCommand("SELECT name FROM sys.tables",connection);
        await connection.OpenAsync();
        var reader = await command.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            yield return reader.GetString(0);
        }
    }

Then call in your action Like this

   public async Task<IActionResult> Index()
    {
        var list=new List<string>();    
        await foreach (var item in GetTableNamesAsync())
        {          
            list.Add(item);
        }
        return Ok(list);
    }