CODEWITHSUNDEEP
mysqlasp.netasp.net-mvcentity-framework
Yuri Zolotarev
Yuri Zolotarev

Reputation: 731

Entity Framework in MY.SQL ASP.NET Core Angular project fails with MySqlException error in syntax near CONSTRAINT when trying to DropForeignKey

I am running a project using code first migrations. I have a big model and everything ran smoothly untill this happened. So part of the big picture was like looking this. Initial state model number one:

public class WebClient
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public IList<Trade> Trades { get; set; }
    public IList<Portfolio> Portfolios { get; set; }
    public IList<Strategy> Strategies { get; set; }

    [Required]
    public string EMail { get; set; }
}

And the second model:

public class Strategy
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string ShortDesc { get; set; }
    public string EntryRules { get; set; }
    public string ExitRules { get; set; }
    public IList<Trade> Trades { get; set; }

}

When I ran this migration to MYSQL database Entity Framework created a webClientId column in the strategies table and set it to be the foreign key for WebCLients.Id (for the id in the webclients table) and also created an index for that which is pretty cool.

After this I realized I forgot to input a relation to the webclient inside the strategy model. So I put two lines in and got this.

public class Strategy
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string ShortDesc { get; set; }
    public string EntryRules { get; set; }
    public string ExitRules { get; set; }
    public IList<Trade> Trades { get; set; }

    public virtual WebClient WebClient { get; set; } //new stuff
    public int WebClientId { get; set; } //new stuff

}

For this Entity Framework suggested a following migrations which is a bit weird to start with.

migrationBuilder.DropForeignKey(
            name: "FK_Strategies_WebClients_WebClientId",
            table: "Strategies");

        migrationBuilder.AlterColumn<int>(
            name: "WebClientId",
            table: "Strategies",
            nullable: false,
            oldClrType: typeof(int),
            oldNullable: true);

        migrationBuilder.AddForeignKey(
            name: "FK_Strategies_WebClients_WebClientId",
            table: "Strategies",
            column: "WebClientId",
            principalTable: "WebClients",
            principalColumn: "Id",
            onDelete: ReferentialAction.Cascade);

So it drops the old key to create a new one which is exactly the same apart from that it is not nullable. Ok well let's do it. However when I run a database update on that I get an error and I have no idea on how to deal with it.

MySql.Data.MySqlClient.MySqlException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT FK_Strateg ies_WebClients_WebClientId' at line 1

If I run the command with a --verbose flag I can catch the bit of SQL and see where it crashes...

ALTER TABLE Strategies DROP CONSTRAINT FK_Strategies_WebClients_WebClientId;

Has anyone come across this issue ? Will be real glad to hear any hints, Thanks!

Upvotes: 1

Views: 391

Answers (2)

Yuri Zolotarev
Yuri Zolotarev

Reputation: 731

Ok so the work around is like this. Seems to me MySQL doesn't understand:

ALTER TABLE Strategies DROP CONSTRAINT FK_Strategies_WebClients_WebClientId;

That is for SQL Server / Oracle / MS Access. More on this here

I just changed that line in the migrations to 

migrationBuilder.Sql("ALTER TABLE Strategies DROP FOREIGN KEY FK_Strategies_WebClients_WebClientId;");

Everything updated fine after that. Result - use DROP FOREIGN KEY in MySql instead of DROP CONSTRAINT. No idea why that is not fized in the MySql Adapter for Entity Framework.

Upvotes: 2

Ashiquzzaman
Ashiquzzaman

Reputation: 5284

This is happening because Entity Framework doesn't understand that WebClientId is a foreign key so it is adding a new foreign key field for you. The Entity Framework convention for naming automatically inserted foreign key fields is TableName_Id,

Entity Framework Code First provides a set of data annotation attributes that can be applied on domain classes or the properties of domain classes.The ForeignKey attribute is used to specify which property is the foreign key in a relationship (ForeignKey Attribute specifies the foreign key for the Navigation property in Entity Framework).

Example:

public class Strategy
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string ShortDesc { get; set; }
    public string EntryRules { get; set; }
    public string ExitRules { get; set; }
    public IList<Trade> Trades { get; set; }
    public int WebClientId { get; set; } //Foreign Key property
    [ForeignKey("WebClientId")]
    public WebClient WebClient { get; set; } //Respective Entity


}

public class WebClient
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    [Required]
    public string EMail { get; set; }
    public IList<Trade> Trades { get; set; }
    public IList<Portfolio> Portfolios { get; set; }
    public IList<Strategy> Strategies { get; set; }

}

You can get more information from this link.

Upvotes: 0

Related Questions