我正在關注docs.microsoft.com上的EF Core with MVC教程。
我有以下遷移：
using Microsoft.EntityFrameworkCore.Migrations;
using Npgsql.EntityFrameworkCore.PostgreSQL.Metadata;
#nullable disable
namespace ContosoUniversity.Migrations
{
public partial class ComplexDataModel : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.AlterColumn<DateTime>(
name: "EnrollmentDate",
table: "Student",
type: "timestamp with time zone",
nullable: true,
oldClrType: typeof(DateTime),
oldType: "timestamp with time zone"
);
migrationBuilder.AlterColumn<string>(
name: "Title",
table: "Course",
type: "character varying(50)",
maxLength: 50,
nullable: false,
oldClrType: typeof(string),
oldType: "text"
);
// migrationBuilder.AddColumn<int>(
// name: "DepartmentID",
// table: "Course",
// type: "integer",
// nullable: false,
// defaultValue: 0
// );
migrationBuilder.CreateTable(
name: "Instructor",
columns: table =>
new
{
ID = table
.Column<int>(type: "integer", nullable: false)
.Annotation(
"Npgsql:ValueGenerationStrategy",
NpgsqlValueGenerationStrategy.IdentityByDefaultColumn
),
LastName = table.Column<string>(
type: "character varying(50)",
maxLength: 50,
nullable: false
),
FirstName = table.Column<string>(
type: "character varying(50)",
maxLength: 50,
nullable: false
),
HireDate = table.Column<DateTime>(
type: "timestamp with time zone",
nullable: true
)
},
constraints: table => table.PrimaryKey("PK_Instructor", x => x.ID)
);
migrationBuilder.CreateTable(
name: "CourseAssignment",
columns: table =>
new
{
InstructorID = table.Column<int>(type: "integer", nullable: false),
CourseID = table.Column<int>(type: "integer", nullable: false)
},
constraints: table =>
{
table.PrimaryKey(
"PK_CourseAssignment",
x => new { x.CourseID, x.InstructorID }
);
table.ForeignKey(
name: "FK_CourseAssignment_Course_CourseID",
column: x => x.CourseID,
principalTable: "Course",
principalColumn: "CourseID",
onDelete: ReferentialAction.Cascade
);
table.ForeignKey(
name: "FK_CourseAssignment_Instructor_InstructorID",
column: x => x.InstructorID,
principalTable: "Instructor",
principalColumn: "ID",
onDelete: ReferentialAction.Cascade
);
}
);
migrationBuilder.CreateTable(
name: "Department",
columns: table =>
new
{
DepartmentID = table
.Column<int>(type: "integer", nullable: false)
.Annotation(
"Npgsql:ValueGenerationStrategy",
NpgsqlValueGenerationStrategy.IdentityByDefaultColumn
),
Name = table.Column<string>(
type: "character varying(50)",
maxLength: 50,
nullable: false
),
Budget = table.Column<decimal>(type: "money", nullable: false),
StartDate = table.Column<DateTime>(
type: "timestamp with time zone",
nullable: false
),
InstructorID = table.Column<int>(type: "integer", nullable: true)
},
constraints: table =>
{
table.PrimaryKey("PK_Department", x => x.DepartmentID);
table.ForeignKey(
name: "FK_Department_Instructor_InstructorID",
column: x => x.InstructorID,
principalTable: "Instructor",
principalColumn: "ID"
);
}
);
migrationBuilder.Sql(
"INSERT INTO Department (Name, Budget, StartDate) VALUES ('Temp', 0.00, GETDATE())"
);
// Default value for FK points to department created above, with
// defaultValue changed to 1 in following AddColumn statement.
migrationBuilder.AddColumn<int>(
name: "DepartmentID",
table: "Course",
nullable: false,
defaultValue: 1
);
migrationBuilder.CreateTable(
name: "OfficeAssignment",
columns: table =>
new
{
InstructorID = table.Column<int>(type: "integer", nullable: false),
Location = table.Column<string>(
type: "character varying(50)",
maxLength: 50,
nullable: false
)
},
constraints: table =>
{
table.PrimaryKey("PK_OfficeAssignment", x => x.InstructorID);
table.ForeignKey(
name: "FK_OfficeAssignment_Instructor_InstructorID",
column: x => x.InstructorID,
principalTable: "Instructor",
principalColumn: "ID",
onDelete: ReferentialAction.Cascade
);
}
);
migrationBuilder.CreateIndex(
name: "IX_Course_DepartmentID",
table: "Course",
column: "DepartmentID"
);
migrationBuilder.CreateIndex(
name: "IX_CourseAssignment_InstructorID",
table: "CourseAssignment",
column: "InstructorID"
);
migrationBuilder.CreateIndex(
name: "IX_Department_InstructorID",
table: "Department",
column: "InstructorID"
);
migrationBuilder.AddForeignKey(
name: "FK_Course_Department_DepartmentID",
table: "Course",
column: "DepartmentID",
principalTable: "Department",
principalColumn: "DepartmentID",
onDelete: ReferentialAction.Cascade
);
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropForeignKey(
name: "FK_Course_Department_DepartmentID",
table: "Course"
);
migrationBuilder.DropTable(name: "CourseAssignment");
migrationBuilder.DropTable(name: "Department");
migrationBuilder.DropTable(name: "OfficeAssignment");
migrationBuilder.DropTable(name: "Instructor");
migrationBuilder.DropIndex(name: "IX_Course_DepartmentID", table: "Course");
migrationBuilder.DropColumn(name: "DepartmentID", table: "Course");
migrationBuilder.AlterColumn<DateTime>(
name: "EnrollmentDate",
table: "Student",
type: "timestamp with time zone",
nullable: false,
defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified),
oldClrType: typeof(DateTime),
oldType: "timestamp with time zone",
oldNullable: true
);
migrationBuilder.AlterColumn<string>(
name: "Title",
table: "Course",
type: "text",
nullable: false,
oldClrType: typeof(string),
oldType: "character varying(50)",
oldMaxLength: 50
);
}
}
}
并且在嘗試執行它時（資料庫預先洗掉，因此它被重新創建）我得到以下 PostgreSQL 錯誤/.NET 堆疊跟蹤：
dotnet ef database update
Build started...
Build succeeded.
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
Entity Framework Core 6.0.2 initialized 'SchoolContext' using provider 'Npgsql.EntityFrameworkCore.PostgreSQL:6.0.2 854d2438884c0bf3a4ba8ccde2c47f7ba1ea3a4c' with options: None
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (26,524ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE DATABASE contoso_university;
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (188ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "__EFMigrationsHistory" (
"MigrationId" character varying(150) NOT NULL,
"ProductVersion" character varying(32) NOT NULL,
CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId")
);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (39ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT EXISTS (SELECT 1 FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace WHERE c.relname='__EFMigrationsHistory');
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "MigrationId", "ProductVersion"
FROM "__EFMigrationsHistory"
ORDER BY "MigrationId";
info: Microsoft.EntityFrameworkCore.Migrations[20402]
Applying migration '20220525123835_InitialCreate'.
Applying migration '20220525123835_InitialCreate'.
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (348ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "Course" (
"CourseID" integer NOT NULL,
"Title" text NOT NULL,
"Credits" integer NOT NULL,
CONSTRAINT "PK_Course" PRIMARY KEY ("CourseID")
);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (281ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "Student" (
"ID" integer GENERATED BY DEFAULT AS IDENTITY,
"LastName" text NOT NULL,
"FirstMidName" text NOT NULL,
"EnrollmentDate" timestamp with time zone NOT NULL,
CONSTRAINT "PK_Student" PRIMARY KEY ("ID")
);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (95ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "Enrollment" (
"EnrollmentID" integer GENERATED BY DEFAULT AS IDENTITY,
"CourseID" integer NOT NULL,
"StudentID" integer NOT NULL,
"Grade" integer NULL,
CONSTRAINT "PK_Enrollment" PRIMARY KEY ("EnrollmentID"),
CONSTRAINT "FK_Enrollment_Course_CourseID" FOREIGN KEY ("CourseID") REFERENCES "Course" ("CourseID") ON DELETE CASCADE,
CONSTRAINT "FK_Enrollment_Student_StudentID" FOREIGN KEY ("StudentID") REFERENCES "Student" ("ID") ON DELETE CASCADE
);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (104ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE INDEX "IX_Enrollment_CourseID" ON "Enrollment" ("CourseID");
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (187ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE INDEX "IX_Enrollment_StudentID" ON "Enrollment" ("StudentID");
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20220525123835_InitialCreate', '6.0.2');
info: Microsoft.EntityFrameworkCore.Migrations[20402]
Applying migration '20220525130657_MaxLengthOnNames'.
Applying migration '20220525130657_MaxLengthOnNames'.
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (525ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE "Student" ALTER COLUMN "LastName" TYPE character varying(50);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (175ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE "Student" ALTER COLUMN "FirstMidName" TYPE character varying(50);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20220525130657_MaxLengthOnNames', '6.0.2');
Applying migration '20220525140736_ColumnFirstName'.
info: Microsoft.EntityFrameworkCore.Migrations[20402]
Applying migration '20220525140736_ColumnFirstName'.
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE "Student" RENAME COLUMN "FirstMidName" TO "FirstName";
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20220525140736_ColumnFirstName', '6.0.2');
Applying migration '20220526192225_ComplexDataModel'.info: Microsoft.EntityFrameworkCore.Migrations[20402]
Applying migration '20220526192225_ComplexDataModel'.
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE "Student" ALTER COLUMN "EnrollmentDate" DROP NOT NULL;
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (145ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE "Course" ALTER COLUMN "Title" TYPE character varying(50);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (83ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "Instructor" (
"ID" integer GENERATED BY DEFAULT AS IDENTITY,
"LastName" character varying(50) NOT NULL,
"FirstName" character varying(50) NOT NULL,
"HireDate" timestamp with time zone NULL,
CONSTRAINT "PK_Instructor" PRIMARY KEY ("ID")
);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (70ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "CourseAssignment" (
"InstructorID" integer NOT NULL,
"CourseID" integer NOT NULL,
CONSTRAINT "PK_CourseAssignment" PRIMARY KEY ("CourseID", "InstructorID"),
CONSTRAINT "FK_CourseAssignment_Course_CourseID" FOREIGN KEY ("CourseID") REFERENCES "Course" ("CourseID") ON DELETE CASCADE,
CONSTRAINT "FK_CourseAssignment_Instructor_InstructorID" FOREIGN KEY ("InstructorID") REFERENCES "Instructor" ("ID") ON DELETE CASCADE
);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (93ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "Department" (
"DepartmentID" integer GENERATED BY DEFAULT AS IDENTITY,
"Name" character varying(50) NOT NULL,
"Budget" money NOT NULL,
"StartDate" timestamp with time zone NOT NULL,
"InstructorID" integer NULL,
CONSTRAINT "PK_Department" PRIMARY KEY ("DepartmentID"),
CONSTRAINT "FK_Department_Instructor_InstructorID" FOREIGN KEY ("InstructorID") REFERENCES "Instructor" ("ID")
);
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
Failed executing DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO Department (Name, Budget, StartDate) VALUES ('Temp', 0.00, GETDATE())
Failed executing DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO Department (Name, Budget, StartDate) VALUES ('Temp', 0.00, GETDATE())
Npgsql.PostgresException (0x80004005): 42P01: relation "department" does not exist
POSITION: 13
at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|213_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
at Npgsql.NpgsqlDataReader.NextResult()
at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteNonQuery()
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Exception data:
Severity: ERROR
SqlState: 42P01
MessageText: relation "department" does not exist
Position: 13
File: parse_relation.c
Line: 1381
Routine: parserOpenTable
42P01: relation "department" does not exist
POSITION: 13
我不知道這種關系應該存在于哪里以及為什么它不存在，你能幫我嗎？
編輯：正如 Belayer 的回答中正確指出的那樣，錯誤的原因是 EF Core 在其中生成了它的命令，它使用雙引號將列和表的名稱括起來。在撰寫純 SQL 陳述句時，也必須使用雙引號并將名稱括起來。我剛剛粘貼了 MS 教程中的那一行，該教程是在考慮 SQL Server 而不是 PostgreSQL 的情況下制作的。也就是說，像這樣的案例正是為什么普通 SQL 幾乎總是不是一件好事的原因。
uj5u.com熱心網友回復：
您創建了 table"Department"但隨后使用了 table Department。那些不一樣。Postgres 將所有非雙引號（“...”）識別符號折疊為小寫（與將它們折疊為大寫的 SQL 標準相反），但在識別符號被雙引號時保持精確大小寫。但是，當使用雙引號創建時，參考時必須始終使用雙引號。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/483040.html