При подтверждении данных произошла ошибка no such table main

I have created a Linq and SQLite app in C#/winform with DBLinq using these two commands:

DbMetal /provider:Sqlite /conn "Data Source=pathtodatabase.s3db" /dbml:pathtoDatabase.dbml

and:

DbMetal /code:pathtoDatabaseContext.cs pathtoDatabase.dbml

To get a connection to database:

string dataSource = @"Data Source=" + AppDomain.CurrentDomain.BaseDirectory + "database.s3db";
var connection = new SQLiteConnection(dataSource);
Main db = new Main(connection, new SqliteVendor());

And to query database:

var user = db.User.SingleOrDefault(u => u.UserName == username);

Running this line of code, I get No shuch table Exception:

no such table: main.USER
System.Data.SQLite.SQLiteException was unhandled HResult=-2147467259 Message=SQLite error
no such table: main.USER
Source=System.Data.SQLite 
ErrorCode=-2147467259
StackTrace:
    at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
    at System.Data.SQLite.SQLiteCommand.BuildNextCommand()        
    at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
    at System.Data.SQLite.SQLiteDataReader.NextResult()
    at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
    at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
    at System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader(CommandBehavior behavior)
    at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
    at DbLinq.Data.Linq.Sugar.Implementation.QueryRunner.Select[T](SelectQuery selectQuery)
    at DbLinq.Data.Linq.Sugar.Implementation.QueryRunner.SelectSingle[S](SelectQuery selectQuery, Boolean allowDefault)
    at DbLinq.Data.Linq.Sugar.Implementation.QueryRunner.SelectScalar[S](SelectQuery selectQuery)
    at DbLinq.Data.Linq.Implementation.QueryProvider`1.Execute[TResult](Expression expression)
    at System.Linq.Queryable.SingleOrDefault[TSource](IQueryable`1 source, Expression`1 predicate)
...

Database file exists and I have the table.
Any Idea?

Возможно, Вы создали таблицу в БД на SQL-сервере. Но в контексте она не появится автоматически — ее надо туда добавить, выбрав опцию «обновить модель из БД» в конструкторе EDM и поставить на ней галочку.

Модель в общем случае может состоять из двух частей.
1 — та, которая добавляется средствами EF и состоит из содержимого папки .edmx (классы модели + контекст).
Все модели этой части привязаны в контексту и отслеживаются EF. Если были внесены любые изменения в эти модели и дан один SaveChanges, то все эти изменения из буфера будут переноситься в БД «скопом», — EF сам создаст для них нужные запросы Update/Insert/Delete и сам же запакует их в одну транзакцию.
2 — добавленные руками классы, которые нужны для алгоритма приложения, но которых нет в самой БД. Например, запросы, возвращающие выборку из нескольких связанных таблиц. Эти классы лежат обычно в папке Models, но не в папке .edmx, а методы для работы с ними — в специальном классе-интерфейсе, называемом репозиторием. Ни об этих классах, ни о репозитории, EF не имеет никакого понятия, поэтому работать с ними можно и нужно без EF (для чего собственно и служит репозиторий)

• Laravel Version: 5.8.12
• PHP Version: 7.1.23
• Database Driver & Version: SQLite 3

Description:

When I launch the command php artisan migrate:fresh --seed I have this error:

Dropped all tables successfully.
Migration table created successfully.
Migrating: 2019_04_14_162607_create_users_table
Migrated:  2019_04_14_162607_create_users_table
Migrating: 2019_04_14_163732_create_companies_table
Migrated:  2019_04_14_163732_create_companies_table
Migrating: 2019_04_15_194613_create_tickets_table
Migrated:  2019_04_15_194613_create_tickets_table
Migrating: 2019_04_15_194639_create_statuses_table
Migrated:  2019_04_15_194639_create_statuses_table
Migrating: 2019_04_15_194747_create_categories_table
Migrated:  2019_04_15_194747_create_categories_table
Migrating: 2019_04_15_195429_create_severities_table
Migrated:  2019_04_15_195429_create_severities_table
Migrating: 2019_04_15_202912_create_messages_table
Migrated:  2019_04_15_202912_create_messages_table
Seeding: UsersTableSeeder
Seeding: SeveritiesTableSeeder
Seeding: StatusesTableSeeder
Seeding: CategoriesTableSeeder
Seeding: TicketsTableSeeder

   IlluminateDatabaseQueryException  : SQLSTATE[HY000]: General error: 1 no such table: main. (SQL: insert into "tickets" ("title", "body", "attachment", "user_id", "severity_id", "status_id", "category_id", "created_at", "updated_at") values (Ea et qui quia quis dolore quo incidunt ullam., Recusandae quasi voluptatem omnis quia ullam dolore exercitationem harum., 77d4b4ef-2668-3b6e-8914-47b87419b98a.img, 73, 4, 2, 1, 2019-04-16 15:44:30, 2019-04-16 15:44:30))

  at /Users/adrienpsn/Dev/Laravel/support-ce/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664
    660|         // If an exception occurs when attempting to run a query, we'll format the error
    661|         // message to include the bindings with SQL, which will make this exception a
    662|         // lot more helpful to the developer instead of just the database's errors.
    663|         catch (Exception $e) {
  > 664|             throw new QueryException(
    665|                 $query, $this->prepareBindings($bindings), $e
    666|             );
    667|         }
    668| 

  Exception trace:

  1   PDOException::("SQLSTATE[HY000]: General error: 1 no such table: main.")
      /Users/adrienpsn/Dev/Laravel/support-ce/vendor/laravel/framework/src/Illuminate/Database/Connection.php:452

  2   PDO::prepare("insert into "tickets" ("title", "body", "attachment", "user_id", "severity_id", "status_id", "category_id", "created_at", "updated_at") values (?, ?, ?, ?, ?, ?, ?, ?, ?)")
      /Users/adrienpsn/Dev/Laravel/support-ce/vendor/laravel/framework/src/Illuminate/Database/Connection.php:452

  Please use the argument -v to see more details.

The error no such table main. comes from my TicketsTableSeederfile because when I don’t call it, I have no error.

Here is my TicketsTableSeeder file:

public function run()
    {
        $faker = FakerFactory::create();

        for ($i = 1; $i < 20; $i++) {
            $title = $faker->sentence(6, true);
            DB::table('tickets')->insert([
                'title' => $title,
                'body' => $faker->text($faker->numberBetween(50, 200)),
                'attachment' => $faker->uuid . '.img',
                'user_id' => $faker->numberBetween(1, 100),
                'severity_id' => $faker->numberBetween(1, 4),
                'status_id' => $faker->numberBetween(1, 7),
                'category_id' => $faker->numberBetween(1, 4),
                'created_at' => Carbon::now()->format('Y-m-d H:i:s'),
                'updated_at' => Carbon::now()->format('Y-m-d H:i:s')
            ]);
        }
    }

Thank’s for any help!

I have also met sqlite «no such table» error on Android 9.0, I have found there is a similar issue on SO. (Android P — ‘SQLite: No Such Table Error’ after copying database from assets).

Base on above link, I tried below solutions 1 and 2.
1) Close connection after read

private void createDataBase() throws IOException {
    this.getReadableDatabase();
    this.close(); 
    try {           
        copyDataBase();            
    } catch (IOException e) {           
       throw new RuntimeException(e);
    }
}

2) Disable WAL.

@Override
public void onConfigure(SQLiteDatabase db) {
    Logger.i(TAG, "onConfigure()");
    super.onConfigure(db);
    db.disableWriteAheadLogging();
}

private boolean checkIfDBExists() {
    File dbFile = new File(DB_PATH + DB_NAME);
    return dbFile.exists();
}

public void openDataBase() throws SQLException {
    String myPath = DB_PATH + DB_NAME;
    myDataBase = SQLiteDatabase.openDatabase(myPath, null,SQLiteDatabase.OPEN_READWRITE);

}

After that, I found a problem, These solutions are only fine when I remove installed App, and fresh install App.

When I have installed a buggy App with "no such table error" on Android 9.0 device, Then I add above fixed code, and install it directly with Android Studio which looks like an App upgrade scenario.

In this case, The db file will be over there, checkIfDBExists() will return true, then we will call openDataBase() instead of createDataBase() The sqlite "no such table" error will still happen, So How can I totally fix this issue no matter fresh install or upgrade? Thanks in advance.

I have also met sqlite «no such table» error on Android 9.0, I have found there is a similar issue on SO. (Android P — ‘SQLite: No Such Table Error’ after copying database from assets).

Base on above link, I tried below solutions 1 and 2.
1) Close connection after read

private void createDataBase() throws IOException {
    this.getReadableDatabase();
    this.close(); 
    try {           
        copyDataBase();            
    } catch (IOException e) {           
       throw new RuntimeException(e);
    }
}

2) Disable WAL.

@Override
public void onConfigure(SQLiteDatabase db) {
    Logger.i(TAG, "onConfigure()");
    super.onConfigure(db);
    db.disableWriteAheadLogging();
}

private boolean checkIfDBExists() {
    File dbFile = new File(DB_PATH + DB_NAME);
    return dbFile.exists();
}

public void openDataBase() throws SQLException {
    String myPath = DB_PATH + DB_NAME;
    myDataBase = SQLiteDatabase.openDatabase(myPath, null,SQLiteDatabase.OPEN_READWRITE);

}

After that, I found a problem, These solutions are only fine when I remove installed App, and fresh install App.

When I have installed a buggy App with "no such table error" on Android 9.0 device, Then I add above fixed code, and install it directly with Android Studio which looks like an App upgrade scenario.

In this case, The db file will be over there, checkIfDBExists() will return true, then we will call openDataBase() instead of createDataBase() The sqlite "no such table" error will still happen, So How can I totally fix this issue no matter fresh install or upgrade? Thanks in advance.