Entity framework core and get table list from sqlite database

Question

I want a list of tables that exists on sqlite database, and well I've got a simple query that looks like:

SELECT name from sqlite_master WHERE type='table';

But now I need to execute this using entity framework core, so I am binding this to DataContext, and here is where I am stuck as data context contains all representation of tables as db set but not sqlite system tables, I tried using query on exposed database facade but it is not returning any pother values then number of rows it has affected, something like:

using (var ctx = new DataContext())
{
    var query = @"SELECT name from sqlite_master WHERE type='table';"
    string[] result = ctx.Database.ExecuteSqlCommand(query);
    return result;
}

Is there any other way i can that information.

Use getschema : https://github.com/aspnet/Microsoft.Data.Sqlite/issues/443 or https://stackoverflow.com/questions/4770716/reading-sqlite-table-information-in-c-net — jdweng, Mar 28 '19 at 10:12
Aye would be nice, but ef core throws me NotSupported exception — Wojciech Szabowicz, Mar 28 '19 at 10:40

score 6 · Accepted Answer · answered Mar 28 '19 at 10:57

You can directly use ADO.NET commands with EntityFrameworkCore

using (var ctx = new DataContext())
{
    using (var command = ctx.Database.GetDbConnection().CreateCommand())
    {
        command.CommandText = "SELECT name from sqlite_master WHERE type='table'";
        ctx.Database.OpenConnection();
        using (var result = command.ExecuteReader())
        {
            while (result.Read())
            {
                Console.WriteLine(result.GetString(0));
            }
        }
    }
}

score -2 · Answer 2 · answered Jun 03 '20 at 15:26

-2

In case you need a schema with a list of tables inside the database you can always do:

using (var ctx = new DataContext())
{
    DataTable dbSchemaWithTables = ctx.Database.GetDbConnection().GetSchema("Tables");
}

answered Jun 03 '20 at 15:26

Ivan B

84
8

1

Specified method is not supported. – Moumit Jul 27 '20 at 20:12

Entity framework core and get table list from sqlite database

2 Answers2