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

Question

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?

Im using Microsoft.EntityFrameworkCore 5.0.15 .NETStandard, Version =v2.1 — Demaru, Mar 09 '22 at 17:49
You may need to update to a newer version of Entity Framework. See [this documentation](https://learn.microsoft.com/en-us/ef/ef6/querying/raw-sql). — mason, Mar 09 '22 at 17:50

Mohammad Aghazadeh · Accepted Answer · 2022-03-10T03:40:02.260

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);
    }

Thank you this helped! I still wonder why SqlQuery method doesn't show, maybe I need to update E — Demaru, Mar 09 '22 at 19:26

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

1 Answers1