Sqlite metadata query fails with Microsoft.Data.Sqlite, works with System.Data.SQLite

I have a simple query taken directly from Microsoft's documentation (see https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite/metadata) that is failing. According to the documentation, queries against sqlite_master should work as intended, but they throw exceptions, even after I create the database and create a table.

SELECT t.name AS tbl_name, c.name, c.type, c.[notnull], c.dflt_value, c.pk FROM sqlite_master AS t, pragma_table_info(t.name) AS c WHERE t.type = 'table';

Here is the isolated code highlighting the issue:

using System;
using System.Data;
using Microsoft.Data.Sqlite;

namespace Test.Library
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string connStr = "Data Source=test.db";

                string tableQuery = "CREATE TABLE IF NOT EXISTS 'company' (id Integer PRIMARY KEY AUTOINCREMENT NOT NULL , name Text COLLATE NOCASE , postal Integer);";
                string metadataQuery = "SELECT t.name AS tbl_name, c.name, c.type, c.[notnull], c.dflt_value, c.pk FROM sqlite_master AS t, pragma_table_info(t.name) AS c WHERE t.type = 'table';";

                using (SqliteConnection conn = new SqliteConnection(connStr))
                {
                    conn.Open();
                    DataTable result = Query(tableQuery, conn);
                    Console.WriteLine(result.Rows.Count);

                    result = Query(metadataQuery, conn);
                    Console.WriteLine(result.Rows.Count);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }

            Console.ReadLine();
        }

        private static DataTable Query(string query, SqliteConnection conn)
        {
            using (SqliteCommand cmd = new SqliteCommand(query, conn))
            {
                using (SqliteDataReader rdr = cmd.ExecuteReader())
                {
                    DataTable result = new DataTable();
                    result.Load(rdr);
                    return result;
                }
            }
        }
    }
}

And the console output including exception (note the first line is the Console.WriteLine of the number of rows returned from execution of the first query, i.e. 0)...

0
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'no such table column: pragma_table_info.name'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteDataReader.GetSchemaTable()
   at System.Data.ProviderBase.SchemaMapping..ctor(DataAdapter adapter, DataSet dataset, DataTable datatable, DataReaderContainer dataReader, Boolean keyInfo, SchemaType schemaType, String sourceTableName, Boolean gettingData, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.FillMappingInternal(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.FillMapping(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
   at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)
   at System.Data.DataTable.Load(IDataReader reader)
   at Test.Library.Program.Query(String query, SqliteConnection conn) in C:\Code\Misc\Program.cs:line 43
   at Test.Library.Program.Main(String[] args) in C:\Code\Misc\Program.cs:line 24

Note that the metadataQuery is directly from Microsoft's site (link above).

When I execute this same query using DB Browser for Sqlite (or similar) it works fine, and, when using the same code with System.Data.SQLite it works fine (obviously class names have slightly different capitalization, etc).

Thanks!

It looks like an issue on how the Microsoft.Data.Sqlite handles querying the data directly from the table-valued functions like pragma_table_info.

On the other hand to achieve exactly to get all the metadata information of all the tables the below query works(Tested with nuget version 3.1.4)

string metadataQuery = "DROP TABLE IF EXISTS info;" +
                       "CREATE TEMPORARY TABLE info AS SELECT t.name AS tbl_name, c.name, c.type, c.[notnull], c.dflt_value, c.pk FROM sqlite_master AS t, pragma_table_info(t.name) AS c WHERE t.type = 'table';" +
                       "SELECT * FROM  info";

For similar issues see here.

Moreover Microsoft.Data.Sqlite has limitations on getting schema information.