时间戳

首页 > 解决方案 > SQL 插入因 SQLite 中的 Entity Framework Core 而失败

c# - SQL 插入因 SQLite 中的 Entity Framework Core 而失败

问题描述

我有以下插入脚本：

INSERT INTO Sections (ID, Name, Type, Notes, CodeI, CodeII, DataID, CodeIII, CodeIV, ShopID) 
VALUES ("787c4d0b-8b6e-4bed-8fb7-03932d0346cd", "Test shop", "R", NULL, "123456789", "1234", NULL, "11", "1234", NULL);

我使用带有 Entity Framework Core 的移动 SQLite 数据库。

这是我的执行器方法：

public async Task ExecuteCommand(FormattableString command)
{
  if (command == null)
    throw new ArgumentNullException(nameof(command));
  try
  {
    await dbContext.Database.ExecuteSqlCommandAsync(command);
  }
  catch (Exception ex)
  {
    throw;
  }
}

这是调用代码：

var command = $"INSERT INTO Sections (ID, Name, Type, Notes, CodeI, CodeII, DataID, CodeIII, CodeIV, ShopID) 
VALUES ('787c4d0b-8b6e-4bed-8fb7-03932d0346cd', 'Test shop', 'R', NULL, '123456789', '1234', NULL, '11', '1234', NULL);"
await ExecuteCommand(command);

当我执行以下操作时：

 await dbContext.Database.ExecuteSqlCommandAsync(command);

我得到这个例外：

Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'near "@p0": syntax error'.
  at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC (System.Int32 rc, SQLitePCL.sqlite3 db) [0x00067] in <56cfa09aae23467e945f1a64a1f893bb>:0 
  at Microsoft.Data.Sqlite.SqliteCommand+<PrepareAndEnumerateStatements>d__62.MoveNext () [0x0008a] in <56cfa09aae23467e945f1a64a1f893bb>:0 
  at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader (System.Data.CommandBehavior behavior) [0x0025d] in <56cfa09aae23467e945f1a64a1f893bb>:0 
  at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader () [0x00000] in <56cfa09aae23467e945f1a64a1f893bb>:0 
  at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery () [0x00042] in <56cfa09aae23467e945f1a64a1f893bb>:0 
  at System.Data.Common.DbCommand.ExecuteNonQueryAsync (System.Threading.CancellationToken cancellationToken) [0x00049] in <6409c8a079bb4f4c8dff9761b9062573>:0 
--- End of stack trace from previous location where exception was thrown ---
  at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Threading.Tasks.Task task) [0x0003e] in <43dbbdc147f2482093d8409abb04c233>:0 
  at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Threading.Tasks.Task task) [0x00028] in <43dbbdc147f2482093d8409abb04c233>:0 
  at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd (System.Threading.Tasks.Task task) [0x00008] in <43dbbdc147f2482093d8409abb04c233>:0 
  at System.Runtime.CompilerServices.TaskAwaiter`1[TResult].GetResult () [0x00000] in <43dbbdc147f2482093d8409abb04c233>:0 
  at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand+<ExecuteAsync>d__17.MoveNext () [0x00358] in <e12f0cc891a249419803faaf433b12e6>:0 
--- End of stack trace from previous location where exception was thrown ---
  at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Threading.Tasks.Task task) [0x0003e] in <43dbbdc147f2482093d8409abb04c233>:0 
  at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Threading.Tasks.Task task) [0x00028] in <43dbbdc147f2482093d8409abb04c233>:0 
  at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd (System.Threading.Tasks.Task task) [0x00008] in <43dbbdc147f2482093d8409abb04c233>:0 
  at System.Runtime.CompilerServices.TaskAwaiter`1[TResult].GetResult () [0x00000] in <43dbbdc147f2482093d8409abb04c233>:0 
  at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions+<ExecuteSqlCommandAsync>d__13.MoveNext () [0x00154] in <e12f0cc891a249419803faaf433b12e6>:0 
--- End of stack trace from previous location where exception was thrown ---
  at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Threading.Tasks.Task task) [0x0003e] in <43dbbdc147f2482093d8409abb04c233>:0 
  at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Threading.Tasks.Task task) [0x00028] in <43dbbdc147f2482093d8409abb04c233>:0 
  at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd (System.Threading.Tasks.Task task) [0x00008] in <43dbbdc147f2482093d8409abb04c233>:0 
  at System.Runtime.CompilerServices.TaskAwaiter`1[TResult].GetResult () [0x00000] in <43dbbdc147f2482093d8409abb04c233>:0 
  at MyXamarinTest.ExecuteCommand(FormattableString command)

当我尝试使用 Entity Framework Core 在我的数据库中插入数据时，我做错了什么？

标签： c#sqliteentity-framework-core

解决方案

问题是FormattableString参数。

看来，当使用这种类型的字符串时，Entity Framework Core 会尝试替换内插字符串的变量，但它无法使用无参数的SQL 字符串执行此操作，因此失败了。

我将参数类型更改为RawSqlString，现在插入完成。

推荐阅读