Issue Details (XML | Word | Printable)

Key: DNET-811
Type: Bug Bug
Status: Closed Closed
Resolution: Won't Fix
Priority: Major Major
Assignee: Jiri Cincura
Reporter: Andreas Patock
Votes: 1
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
.NET Data provider

Can't use new created columns in SQL - Statement in Up() Migration

Created: 14/Mar/18 10:31 AM   Updated: 08/Oct/18 09:50 AM
Component/s: Entity Framework
Affects Version/s: 5.9.1.0
Fix Version/s: None


 Description  « Hide
If I have the following migration:

   public partial class AddDeviceType : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "dbo.DeviceType",
                c => new
                    {
                        Id = c.Int(nullable: false, identity: true),
                        Name = c.String(nullable: false, maxLength: 128),
                        ManufacturerId = c.Int(nullable: false),
                    })
                .PrimaryKey(t => t.Id)
                .ForeignKey("dbo.Manufacturer", t => t.ManufacturerId, cascadeDelete: true)
                .Index(t => t.ManufacturerId);
            
            AddColumn("dbo.Device", "TempDeviceTypeId", c => c.Int());
            AddColumn("dbo.Device", "DeviceTypeId", c => c.Int(nullable: false));
            CreateIndex("dbo.Device", "DeviceTypeId");
            AddForeignKey("dbo.Device", "DeviceTypeId", "dbo.DeviceType", "Id", cascadeDelete: true);

            Sql("UPDATE \"Device\" d SET d.\"DeviceTypeId\" = (SELECT t.\"Id\" from \"DeviceType\" t where t.\"ManufacturerId\" = d.\"ManufacturerId\" ORDER BY t.\"Id\" ROWS 1)");
        }

I can a Column unknown "DeviceTypeId" exception when running the migration. This can found on many examples in the internet and is working with mssql.

I had the idea to do this in two migrations, but the "DeviceTypeId" is a not null field and so the first migration throws an exception also.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Jiri Cincura added a comment - 14/Mar/18 05:31 PM
It's probably because of transactions.

Can you script this migration and paste it here?

Andreas Patock added a comment - 15/Mar/18 11:42 AM - edited
Do you mean this?

ALTER TABLE "Device" DROP CONSTRAINT "FK_ManDevManId"
DROP INDEX "IX_DevManId"
CREATE TABLE "DeviceType" (
"Id" INT NOT NULL,
"Name" VARCHAR(128) NOT NULL,
"ManufacturerId" INT NOT NULL
)
ALTER TABLE "DeviceType" ADD CONSTRAINT "PK_DeviceType" PRIMARY KEY ("Id")
EXECUTE BLOCK
AS
BEGIN
if (not exists(select 1 from rdb$generators where rdb$generator_name = 'GEN_IDENTITY')) then
begin
execute statement 'create sequence GEN_IDENTITY';
end
END
CREATE OR ALTER TRIGGER "DeviceType_TRIG" ACTIVE BEFORE INSERT ON "DeviceType"
AS
BEGIN
if (new."Id" is null) then
begin
new."Id" = next value for GEN_IDENTITY;
end
END
CREATE INDEX "IX_DevTypManId" ON "DeviceType"("ManufacturerId")
ALTER TABLE "Device" ADD "DeviceTypeId" INT DEFAULT 0 NOT NULL
CREATE INDEX "IX_DevDevTypId" ON "Device"("DeviceTypeId")
ALTER TABLE "Device" ADD CONSTRAINT "FK_DevTypDevDevTypId" FOREIGN KEY ("DeviceTypeId") REFERENCES "DeviceType" ("Id") ON DELETE CASCADE
ALTER TABLE "Device" DROP "ManufacturerId"
UPDATE or INSERT INTO "Manufacturer"("Id", "Name") VALUES((SELECT d."Id" from "Manufacturer" d where d."Name" = 'SwitchSimulation' ROWS 1), 'SwitchSimulation')
UPDATE or INSERT INTO "Manufacturer"("Id", "Name") VALUES((SELECT d."Id" from "Manufacturer" d where d."Name" = 'Arista' ROWS 1), 'Arista')
UPDATE or INSERT INTO "Manufacturer"("Id", "Name") VALUES((SELECT d."Id" from "Manufacturer" d where d."Name" = 'Cisco' ROWS 1), 'Cisco')
UPDATE or INSERT INTO "Manufacturer"("Id", "Name") VALUES((SELECT d."Id" from "Manufacturer" d where d."Name" = 'Simulation' ROWS 1), 'Simulation')
UPDATE or INSERT INTO "Manufacturer"("Id", "Name") VALUES((SELECT d."Id" from "Manufacturer" d where d."Name" = 'MulticastStreamer' ROWS 1), 'MulticastStreamer')
UPDATE or INSERT INTO "Manufacturer"("Id", "Name") VALUES((SELECT d."Id" from "Manufacturer" d where d."Name" = 'Embrionix' ROWS 1), 'Embrionix')
UPDATE or INSERT INTO "Manufacturer"("Id", "Name") VALUES((SELECT d."Id" from "Manufacturer" d where d."Name" = 'Imagine' ROWS 1), 'Imagine')
UPDATE or INSERT INTO "Manufacturer"("Id", "Name") VALUES((SELECT d."Id" from "Manufacturer" d where d."Name" = 'S-A-M' ROWS 1), 'S-A-M')
UPDATE or INSERT INTO "Manufacturer"("Id", "Name") VALUES((SELECT d."Id" from "Manufacturer" d where d."Name" = 'Lawo' ROWS 1), 'Lawo')
UPDATE or INSERT INTO "DeviceType"("Id", "Name", "ManufacturerId") VALUES((SELECT d."Id" from "DeviceType" d where d."Name" = 'SwitchSimulation' ROWS 1), 'SwitchSimulation', (SELECT m."Id" from "Manufacturer" m where m."Name" = 'SwitchSimulation' ROWS 1))


The Error is:

Dynamic SQL Error
SQL error code = -204
Table unknown
DeviceType
At line 1, column 108

Jiri Cincura added a comment - 08/Oct/18 09:48 AM - edited
For not null columns the default value is generated (even if you haven't specified it), see "ALTER TABLE "Device" ADD "DeviceTypeId" INT DEFAULT 0 NOT NULL ". The whole migration is executed in a transaction, the update doesn't see yet the table, because of how DDL and DML inside same transaction work in Firebird.

Not sure provider can do much here. I think the best bet is to split this to two migrations.