entity-framework - Npgsql 的 EntityFramework6 创建无效的更新语句

Question

我正在使用 Npgsql EF6 提供程序 (Npgsql v3.0.5.0)。我从现有数据库生成模型。我可以访问数据库并读取记录，但更新现有记录失败。

这是我的代码的简化版本:

int jobId = 123; // some unique id
Entities entities = new Entities();
Job job = entities.Jobs.First(j => j.Id = jobId);
job.Status = 4;
entities.SaveChanges(); // this line throws an exception

这是我从这段代码中得到的错误信息:

42601: syntax error at or near "("

[NpgsqlException (0x80004005): 42601: syntax error at or near "("]
   Npgsql.NpgsqlConnector.DoReadSingleMessage(DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage, Boolean isPrependedMessage) +445
   Npgsql.NpgsqlConnector.ReadSingleMessage(DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage) +282
   Npgsql.NpgsqlCommand.Execute(CommandBehavior behavior) +270
   Npgsql.NpgsqlCommand.ExecuteNonQueryInternal() +177
   Npgsql.NpgsqlCommand.ExecuteNonQuery() +21
   System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<NonQuery>b__0(DbCommand t, DbCommandInterceptionContext`1 c) +26
   System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch(TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed) +129
   System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext) +602
   System.Data.Entity.Internal.InterceptableDbCommand.ExecuteNonQuery() +151
   System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.Execute(Dictionary`2 identifierValues, List`1 generatedValues) +1045
   System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update() +221

这是针对数据库运行的查询:

UPDATE (
    SELECT 
        "Jobs"."Id", 
        "Jobs"."Name", 
        "Jobs"."Created", 
        "Jobs"."Status"
    FROM "Jobs"."Jobs" 
    AS "Jobs"
) SET "Status"=$1 WHERE "Id" = $2

我知道这个查询显然不适用于嵌套的 SELECT 而不是表名。

有人遇到过同样的错误吗？有没有我可能会错过的设置？

Answer 1

此错误是由受影响表上的缺少主键引起的。

.edmx 文件实际上包含以下消息:

warning 6002: The table/view '[database.schema.TableName]' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view.

当从数据库更新模型时，Visual Studio 还在输出选项卡中显示以下消息(我一定是在第一次生成模型时错过了):

The model was generated with warnings or errors.ServiceModel.edmxPlease see the Error List for more details. These issues must be fixed before running your application.