CODEWITHSUNDEEP
.netentity-framework
JK36
JK36

Reputation: 853

Entity Framework manual Database Change

I'm following a a course on Udemy building a .net and mvc5 project. This project involves using the code first design philosophy for databases. I got a couple of things mixed up whilst following the exercises, so I went to the tutors github code repository and downloaded some of the classes. In one of the classes he had a column name called Genre_Id in a Movie class whilst I named my version to GenreId. So I stupidly went into the movie class and called the column Genre_Id and went to the database and renamed the column manually to Genre_Id. This manual change immediately started throwing exceptions in my application.

When I try and build my app, I get thrown system exceptions. I did some research, found a post that suggested reinitialising migrations may resolve the exceptions. So I deleted my migrations folder, disconnected the database connection and deleted the database so that I can recreate the migrations and database schema from scratch. What I don't seem to get is why Visual Studio is still creating migrations with Genre_Id1 which is a foreign key when my class for a movie doesn't contain that column name.

Can anyone help me out please before I rebuild the entire solution from scratch. The Exception I have is Inner Exception: Invalid column name 'Genre_Id1'. I've searched for Genre_Id1 in the entire solution, checked my classes, made sure that the add-migration InitialMigration that contained Genre_Id1 was removed and replaced with Genre_Id before I updated the database, but I'm not quite sure where it is defining or picking up this column name up as the build process seems to look for it.

Anything else I can try out before I trash the project and restart.

Thanks

Upvotes: 1

Views: 113

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205609

The problem you are experiencing originates from poorly chosen default convention by EF (not you) prior EF Core.

When you have a class with navigation property and no explcit FK property:

public class Movie
{
    // ...
    public Genre Genre { get; set; }
}

the implied shadow FK property and the column name is Genre_Id.

But the default convention for the explicit FK property name is GenreId (no underscore). Which when supplied also changes the implied column name:

public class Movie
{
    // ...
    public int GenreId { get; set; }
    public Genre Genre { get; set; }
}

When you name the property Genre_Id

public class Movie
{
    // ...
    public int Genre_Id { get; set; }
    public Genre Genre { get; set; }
}

EF by convention will not recognize it as FK property and will try to create a hidden (shadow) property with the default name Genre_Id. Since the name is preserved by your property, EF appends a suffix in order to make it unique, hence the Genre_Id1 you see in the generated SQL.

There are several ways you can fix it. I personally prefer fluent configuration because it's cleaner IMO, but here is how you can resolve it with data annotations:

If you want FK property named Genre_Id, then use [ForeignKey] attribute to map it as FK:

public class Movie
{
    // ...
    [ForeignKey("Genre")]
    public int Genre_Id { get; set; }
    public Genre Genre { get; set; }
}

or

public class Movie
{
    // ...
    public int Genre_Id { get; set; }
    [ForeignKey("Genre_Id")]
    public Genre Genre { get; set; }
}

If you want property called GenreId, then use [Column] attribute to specify the column name:

public class Movie
{
    // ...
    [Column("Genre_Id")]
    public int GenreId { get; set; }
    public Genre Genre { get; set; }
}

I would suggest you to always create an explicit migration and see what it contains. If you see columns not specified by you, that's indication of some improper configuration causing conflict with EF default conventions.

Upvotes: 2

Related Questions