I'm following the EF Core with MVC tutorial on docs.microsoft.com.
I have the following migration:
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
);
}
}
}
And upon trying to execute it (database deleted beforehand so it is re-created) i get the following PostgreSQL error/.NET stacktrace:
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
I don't know where that relation should exist and why it does not exist, could you help me please?
EDIT: As correctly noted in Belayer's answer, the cause of the error was that EF Core generates its commands inside them it uses double-quotes to surround the names of columns and tables. When writing plain-SQL statements, one has to use and surround the names with double-quotes too. I've just pasted that line from the MS tutorial, and that tutorial was made wth SQL Server in mind and not PostgreSQL. That said, cases like this one are exactly why plain-old SQL is almost always not a good thing to do.
CodePudding user response：
You created table "Department" but then used table Department. Those are not the same. Postgres folds all non-doubled quoted ("...") identifiers to lower case (as opposed to the SQL Standard of folding them to uppercase) but keeps the exact case when identifiers are double quoted. However when created with double quotes you must always double quote when referencing.