Efcore: Generating migration SQL scripts generates invalid SQL

Created on 4 Nov 2018 · 3Comments · Source: dotnet/efcore

Describe what is not working as expected.

I generated SQL scripts for my EF Core 2.1 model (ASP.Net MVC Core 2.1 App with ASP.Net Identity core 2.1 and EF Core 2.1). I generated it on my development workstation running .Net Core 2.1 on a Mac with a SQL-Server in a Docker container. This works well also for UPDATE DATABASE.

But when I generate the SQL script for all migrations I get a SQL script that does not run on a SQL-Server (not on my local and not on Azure SQL Database). Here is the script I got:

CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" (
    "MigrationId" TEXT NOT NULL CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY,
    "ProductVersion" TEXT NOT NULL
);

CREATE TABLE "AspNetRoles" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_AspNetRoles" PRIMARY KEY,
    "Name" TEXT NULL,
    "NormalizedName" TEXT NULL,
    "ConcurrencyStamp" TEXT NULL
);

CREATE TABLE "AspNetUsers" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_AspNetUsers" PRIMARY KEY,
    "UserName" TEXT NULL,
    "NormalizedUserName" TEXT NULL,
    "Email" TEXT NULL,
    "NormalizedEmail" TEXT NULL,
    "EmailConfirmed" INTEGER NOT NULL,
    "PasswordHash" TEXT NULL,
    "SecurityStamp" TEXT NULL,
    "ConcurrencyStamp" TEXT NULL,
    "PhoneNumber" TEXT NULL,
    "PhoneNumberConfirmed" INTEGER NOT NULL,
    "TwoFactorEnabled" INTEGER NOT NULL,
    "LockoutEnd" TEXT NULL,
    "LockoutEnabled" INTEGER NOT NULL,
    "AccessFailedCount" INTEGER NOT NULL
);

CREATE TABLE "AspNetRoleClaims" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_AspNetRoleClaims" PRIMARY KEY AUTOINCREMENT,
    "RoleId" TEXT NOT NULL,
    "ClaimType" TEXT NULL,
    "ClaimValue" TEXT NULL,
    CONSTRAINT "FK_AspNetRoleClaims_AspNetRoles_RoleId" FOREIGN KEY ("RoleId") REFERENCES "AspNetRoles" ("Id") ON DELETE CASCADE
);

CREATE TABLE "AspNetUserClaims" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_AspNetUserClaims" PRIMARY KEY AUTOINCREMENT,
    "UserId" TEXT NOT NULL,
    "ClaimType" TEXT NULL,
    "ClaimValue" TEXT NULL,
    CONSTRAINT "FK_AspNetUserClaims_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
);

CREATE TABLE "AspNetUserLogins" (
    "LoginProvider" TEXT NOT NULL,
    "ProviderKey" TEXT NOT NULL,
    "ProviderDisplayName" TEXT NULL,
    "UserId" TEXT NOT NULL,
    CONSTRAINT "PK_AspNetUserLogins" PRIMARY KEY ("LoginProvider", "ProviderKey"),
    CONSTRAINT "FK_AspNetUserLogins_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
);

CREATE TABLE "AspNetUserRoles" (
    "UserId" TEXT NOT NULL,
    "RoleId" TEXT NOT NULL,
    CONSTRAINT "PK_AspNetUserRoles" PRIMARY KEY ("UserId", "RoleId"),
    CONSTRAINT "FK_AspNetUserRoles_AspNetRoles_RoleId" FOREIGN KEY ("RoleId") REFERENCES "AspNetRoles" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_AspNetUserRoles_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
);

CREATE TABLE "AspNetUserTokens" (
    "UserId" TEXT NOT NULL,
    "LoginProvider" TEXT NOT NULL,
    "Name" TEXT NOT NULL,
    "Value" TEXT NULL,
    CONSTRAINT "PK_AspNetUserTokens" PRIMARY KEY ("UserId", "LoginProvider", "Name"),
    CONSTRAINT "FK_AspNetUserTokens_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
);

CREATE TABLE "Diver" (
    "Id" BLOB NOT NULL CONSTRAINT "PK_Diver" PRIMARY KEY,
    "Fullname" TEXT NOT NULL,
    "Firstname" TEXT NOT NULL,
    "Lastname" TEXT NOT NULL,
    "MemberSince" TEXT NULL,
    "MemberUntil" TEXT NULL,
    "WebsiteUrl" TEXT NULL,
    "TwitterHandle" TEXT NULL,
    "SkypeId" TEXT NULL,
    "Slogan" TEXT NULL,
    "Education" TEXT NULL,
    "Experience" TEXT NULL,
    "MobilePhone" TEXT NULL,
    "NotificationIntervalInHours" INTEGER NOT NULL DEFAULT 1,
    "LastNotificationCheckAt" TEXT NULL,
    "UserId" TEXT NULL,
    CONSTRAINT "FK_Diver_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE RESTRICT
);

CREATE TABLE "Events" (
    "Id" BLOB NOT NULL CONSTRAINT "PK_Events" PRIMARY KEY,
    "Name" TEXT NOT NULL,
    "OrganisatorId" BLOB NOT NULL,
    "Location" TEXT NULL,
    "MeetingPoint" TEXT NULL,
    "Description" TEXT NULL,
    "StartTime" TEXT NOT NULL,
    "EndTime" TEXT NULL,
    "Canceled" INTEGER NOT NULL DEFAULT 0,
    "Deleted" INTEGER NOT NULL DEFAULT 0,
    CONSTRAINT "FK_Events_Diver_OrganisatorId" FOREIGN KEY ("OrganisatorId") REFERENCES "Diver" ("Id") ON DELETE RESTRICT
);

CREATE TABLE "Posts" (
    "Id" BLOB NOT NULL CONSTRAINT "PK_Posts" PRIMARY KEY,
    "Category" INTEGER NOT NULL,
    "CreatedDate" TEXT NOT NULL,
    "PublishDate" TEXT NOT NULL,
    "ModificationDate" TEXT NOT NULL,
    "AuthorId" BLOB NOT NULL,
    "Title" TEXT NOT NULL,
    "Text" TEXT NULL,
    "IntroImageId" BLOB NOT NULL,
    CONSTRAINT "FK_Posts_Diver_AuthorId" FOREIGN KEY ("AuthorId") REFERENCES "Diver" ("Id") ON DELETE RESTRICT
);

CREATE TABLE "Comments" (
    "Id" BLOB NOT NULL CONSTRAINT "PK_Comments" PRIMARY KEY,
    "EventId" BLOB NOT NULL,
    "AuthorId" BLOB NOT NULL,
    "CreateDate" TEXT NOT NULL,
    "ModifiedDate" TEXT NULL,
    "Text" TEXT NOT NULL,
    CONSTRAINT "FK_Comments_Diver_AuthorId" FOREIGN KEY ("AuthorId") REFERENCES "Diver" ("Id") ON DELETE RESTRICT,
    CONSTRAINT "FK_Comments_Events_EventId" FOREIGN KEY ("EventId") REFERENCES "Events" ("Id") ON DELETE RESTRICT
);

CREATE TABLE "Notifications" (
    "Id" BLOB NOT NULL CONSTRAINT "PK_Notifications" PRIMARY KEY,
    "RecipientId" BLOB NOT NULL,
    "OccuredAt" TEXT NOT NULL,
    "AlreadySent" INTEGER NOT NULL DEFAULT 0,
    "CountOfTries" INTEGER NOT NULL DEFAULT 0,
    "Message" TEXT NOT NULL,
    "Type" INTEGER NOT NULL,
    "EventId" BLOB NOT NULL,
    CONSTRAINT "FK_Notifications_Events_EventId" FOREIGN KEY ("EventId") REFERENCES "Events" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_Notifications_Diver_RecipientId" FOREIGN KEY ("RecipientId") REFERENCES "Diver" ("Id") ON DELETE RESTRICT
);

CREATE TABLE "Participants" (
    "Id" BLOB NOT NULL CONSTRAINT "PK_Participants" PRIMARY KEY,
    "EventId" BLOB NOT NULL,
    "ParticipatingDiverId" BLOB NOT NULL,
    "CountPeople" INTEGER NOT NULL DEFAULT 1,
    "Note" TEXT NULL,
    "Status" INTEGER NOT NULL,
    "BuddyTeamName" TEXT NULL,
    CONSTRAINT "FK_Participants_Events_EventId" FOREIGN KEY ("EventId") REFERENCES "Events" ("Id") ON DELETE RESTRICT,
    CONSTRAINT "FK_Participants_Diver_ParticipatingDiverId" FOREIGN KEY ("ParticipatingDiverId") REFERENCES "Diver" ("Id") ON DELETE CASCADE
);

CREATE TABLE "PostImages" (
    "Id" BLOB NOT NULL CONSTRAINT "PK_PostImages" PRIMARY KEY,
    "PostId" BLOB NOT NULL,
    "Caption" TEXT NULL,
    "ImageUrlThumbnail" TEXT NULL,
    "ImageUrlLarge" TEXT NOT NULL,
    CONSTRAINT "FK_PostImages_Posts_PostId" FOREIGN KEY ("PostId") REFERENCES "Posts" ("Id") ON DELETE CASCADE
);

CREATE INDEX "IX_AspNetRoleClaims_RoleId" ON "AspNetRoleClaims" ("RoleId");

CREATE UNIQUE INDEX "RoleNameIndex" ON "AspNetRoles" ("NormalizedName");

CREATE INDEX "IX_AspNetUserClaims_UserId" ON "AspNetUserClaims" ("UserId");

CREATE INDEX "IX_AspNetUserLogins_UserId" ON "AspNetUserLogins" ("UserId");

CREATE INDEX "IX_AspNetUserRoles_RoleId" ON "AspNetUserRoles" ("RoleId");

CREATE INDEX "EmailIndex" ON "AspNetUsers" ("NormalizedEmail");

CREATE UNIQUE INDEX "UserNameIndex" ON "AspNetUsers" ("NormalizedUserName");

CREATE INDEX "IX_Comments_AuthorId" ON "Comments" ("AuthorId");

CREATE INDEX "IX_Comments_EventId" ON "Comments" ("EventId");

CREATE INDEX "IX_Diver_UserId" ON "Diver" ("UserId");

CREATE INDEX "IX_Events_OrganisatorId" ON "Events" ("OrganisatorId");

CREATE INDEX "IX_Events_StartTime_Deleted" ON "Events" ("StartTime", "Deleted");

CREATE INDEX "IX_Notifications_EventId" ON "Notifications" ("EventId");

CREATE INDEX "IX_Notifications_RecipientId" ON "Notifications" ("RecipientId");

CREATE INDEX "IX_Participants_EventId" ON "Participants" ("EventId");

CREATE INDEX "IX_Participants_ParticipatingDiverId" ON "Participants" ("ParticipatingDiverId");

CREATE INDEX "IX_PostImages_PostId" ON "PostImages" ("PostId");

CREATE INDEX "IX_Posts_AuthorId" ON "Posts" ("AuthorId");

CREATE INDEX "IX_Posts_Category_PublishDate" ON "Posts" ("Category", "PublishDate");

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20181002051420_initial', '2.1.3-rtm-32065');

DROP TABLE "PostImages";

DROP TABLE "Posts";

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20181015191233_RemovePosts', '2.1.3-rtm-32065');

ALTER TABLE "Diver" ADD "FacebookId" TEXT NULL;

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20181015192404_AddFacebookId', '2.1.3-rtm-32065');

ALTER TABLE "Diver" ADD "AvatarId" TEXT NULL;

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20181016143224_AddAvatarId', '2.1.3-rtm-32065');

I get the following error message from SQL-Server:

Msg 102, Level 15, State 1, Line 34 Incorrect syntax near 'AUTOINCREMENT'.
Msg 102, Level 15, State 1, Line 42 Incorrect syntax near 'AUTOINCREMENT'.
Msg 156, Level 15, State 1, Line 92 Incorrect syntax near the keyword 'RESTRICT'.
Msg 156, Level 15, State 1, Line 106 Incorrect syntax near the keyword 'RESTRICT'.
Msg 156, Level 15, State 1, Line 119 Incorrect syntax near the keyword 'RESTRICT'.
Msg 156, Level 15, State 1, Line 129 Incorrect syntax near the keyword 'RESTRICT'.
Msg 156, Level 15, State 1, Line 143 Incorrect syntax near the keyword 'RESTRICT'.
Msg 156, Level 15, State 1, Line 154 Incorrect syntax near the keyword 'RESTRICT'.

I read that for example CREATE TABLE IF NOT EXISTS is not valid for SQL-Server. So I'm asking myself (and actually you) if there is a general problem so the wrong flavour of SQL is generated? Should this work for SQL-Server?

Steps to reproduce

Execute the above, generated SQL Script agains a SQL-Server.

The project can be found in this GitHub repo: https://github.com/mduu/tauchbolde (migrations are in Tauchbolde.Common, Startup project is Tauchbolde.Web.

Further technical details

EF Core version: 2.1
Database Provider: System.Data.SqlClient
Operating system: macOS Mojave (Dev-Workstation), Azure SQL Database
IDE: DotNet Core CLI, Visual Studio Mac

closed-question customer-reported

Source

mduu

Most helpful comment

This is a SQLite script, not SQL Server

ErikEJ on 4 Nov 2018

👍2

All 3 comments

This is a SQLite script, not SQL Server

ErikEJ on 4 Nov 2018

👍2

I once was on SQLite but switched to SQL Server a while ago by setting .UseSqlServer() in the startup.cs. Where / how does the dotnet ef generate command takes the database engine / flavour from? Where do I have to look for changing to SQL Server?

mduu on 4 Nov 2018

@mduu https://github.com/mduu/tauchbolde/blob/master/src/Tauchbolde.Web/ApplicationDbContextFactory.cs sets the provider to SQLite.