none
Column Missing from Table after Converting C# to SQL RRS feed

  • Question

  • I am looking for some general guidance. My code produces the following exception:

    "SqlException: Invalid column name 'iouId'."

    This makes sense since the table I am trying to change is:

    CREATE TABLE [dbo].[Operation] (
        [Id]           INT           IDENTITY (1, 1) NOT NULL,
        [publicView]   BIT           NOT NULL,
        [registerById] INT           NOT NULL,
        [donorId]      INT           NOT NULL,
        [receiverId]   INT           NOT NULL,
        [dateTime]     DATETIME2 (7) DEFAULT ('0001-01-01T00:00:00.0000000') NOT NULL,
        CONSTRAINT [PK_Operation] PRIMARY KEY CLUSTERED ([Id] ASC),
        CONSTRAINT [FK_Operation_Trader_donorId] FOREIGN KEY ([donorId]) REFERENCES [dbo].[Trader] ([Id]) ON DELETE CASCADE,
        CONSTRAINT [FK_Operation_Trader_receiverId] FOREIGN KEY ([receiverId]) REFERENCES [dbo].[Trader] ([Id]),
        CONSTRAINT [FK_Operation_Trader_registerById] FOREIGN KEY ([registerById]) REFERENCES [dbo].[Trader] ([Id])
    );

    Clearly there is no column 'íouid'. However I am have tried updating the database various ways. I just want to ask about the basic principals rather than getting a full solution. This is my c# code:

        public class Operation : Registered
        {
            [ForeignKey("IOU")]
            public int iouId { get; set; }
            public IOU iou { get; set; }
            [ForeignKey("Trader")]
            public int donorId { get; set; }
            public Trader donor { get; set; }
            [ForeignKey("Trader")]
            public int receiverId { get; set; }
            public Trader receiver { get; set; }
            public DateTime dateTime { get; set; }

    The are extra columns in Registered which make the id and public columns appear in the table.

    My question is why is there no iouId column in my table? I have been updating my database using add-migration followed by update-database. I am using Entity Framework Core. How can I troubleshoot?


    DaronRyan

    Wednesday, February 5, 2020 8:42 AM

All replies


  • Hi DaronRyan,
    This error occurs because the updated model class is different than the schema of the table of the existing database. There's no iouId column in the database table.
    There are a few approaches to resolving the error:
    1.Have the Entity Framework automatically drop and re-create the database based on the new model class schema. 
    2.Explicitly modify the schema of the existing database so that it matches the model classes. 

    CREATE TABLE [dbo].[Operation] (
        [Id]           INT           IDENTITY (1, 1) NOT NULL,
        [publicView]   BIT           NOT NULL,
        [registerById] INT           NOT NULL,
        [iouId ]       INT           NOT NULL,
        .....
       );

    3.Use Code First Migrations to update the database schema.
    From the Tools menu, select NuGet Package Manager > Package Manager Console.

    In the PMC, enter the following commands:

    Add-Migration iouId 
    Update-Database
    More details you can refer to this document.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 6, 2020 2:58 AM
  • Hi DaronRyan,
    Has your problem been solved? If it is resolved, we suggest that you mark it as the answer. So it can help other people who have the same problem find a solution quickly.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 20, 2020 9:18 AM