Entity Framework looking for wrong column

Question

I'm brand new to the Entity Framework and trying to learn all it can offer. I'm currently working my way through the MVC Music Store tutorial which includes the following code:

public ActionResult Browse(string genre)
 {
    // Retrieve Genre and its Associated Albums from database
    var genreModel = storeDB.Genres.Include("Albums")
        .Single(g => g.Name == genre);

    return View(genreModel);
 }

as I'm working in VB I converted it like so:

Function Browse(ByVal genre As String) As ActionResult

            'Retrieve Genre and its Associated Albums from database
            Dim genreModel = storeDB.Genres.Include("Albums"). _
                    Single(Function(g) g.Name = genre)

            Return(View(genreModel))
        End Function

The problem is I'm getting the following exception:

Invalid column name 'GenreGenreId'.

Which I know is true, but I can't for the life of my work out where it's getting 'GenreGenreId' from. Probably a basic question but I'll appreciate any help in the right direction.

---

As per requested here is the source for my classes:

Album.vb

Public Class Album

    Private _title As String
    Private _genre As Genre
    Private _AlbumId As Int32
    Private _GenreId As Int32
    Private _ArtistId As Int32
    Private _Price As Decimal
    Private _AlbumArtUrl As String

    Public Property Title As String
        Get
            Return _title
        End Get
        Set(ByVal value As String)
            _title = value
        End Set
    End Property

    Public Property AlbumId As Int16
        Get
            Return _AlbumId
        End Get
        Set(ByVal value As Int16)
            _AlbumId = value
        End Set
    End Property

    Public Property GenreId As Int16
        Get
            Return _GenreId
        End Get
        Set(ByVal value As Int16)
            _GenreId = value
        End Set
    End Property

    Public Property ArtistId As Int16
        Get
            Return _ArtistId
        End Get
        Set(ByVal value As Int16)
            _ArtistId = value
        End Set
    End Property

    Public Property AlbumArtUrl As String
        Get
            Return _AlbumArtUrl
        End Get
        Set(ByVal value As String)
            _AlbumArtUrl = value
        End Set
    End Property

    Public Property Price As Decimal
        Get
            Return _Price
        End Get
        Set(ByVal value As Decimal)
            _Price = value
        End Set
    End Property

    Public Property Genre As Genre
        Get
            Return _genre
        End Get
        Set(ByVal value As Genre)
            _genre = value
        End Set
    End Property


End Class

Genre.vb

Public Class Genre

    Dim _genreId As Int32
    Dim _Name As String
    Dim _Description As String
    Dim _Albums As List(Of Album)

    Public Property GenreId As Int32
        Get
            Return _genreId
        End Get
        Set(ByVal value As Int32)
            _genreId = value
        End Set
    End Property

    Public Property Name As String
        Get
            Return _Name
        End Get
        Set(ByVal value As String)
            _Name = value
        End Set
    End Property

    Public Property Description As String
        Get
            Return _Description
        End Get
        Set(ByVal value As String)
            _Description = value
        End Set
    End Property

    Public Property Albums As List(Of Album)
        Get
            Return _Albums
        End Get
        Set(ByVal value As List(Of Album))
            _Albums = value
        End Set
    End Property

End Class

MusicStoreEntities.vb

Imports System.Data.Entity

Namespace MvcApplication1

    Public Class MusicStoreEntities
        Inherits DbContext

        Public Property Albums As DbSet(Of Album)
        Public Property Genres As DbSet(Of Genre)

    End Class
End Namespace

Answer 1

Just a hypothetical explanation:

The MusicStore example uses Entity Framework Code-First development. And it doesn't have any special configuration, neither with code-first attributes on the model classes nor by using the fluent API.

That means that EntityFramework infers the database structure and all relationships completely from a set of conventions which are defined behind the scenes and based on the class and property names of the model classes.

The important classes for the failing query are (simplified, only the relevant properties):

public class Genre
{
    public int GenreId { get; set; }
    public string Name { get; set; }
    public List<Album> Albums { get; set; }
}

public class Album
{
    public int AlbumId { get; set; }
    public int GenreId { get; set; }
    public virtual Genre Genre { get; set; }
}

And the DbContext is:

public class MusicStoreEntities : DbContext
{
    public DbSet<Album> Albums { get; set; }
    public DbSet<Genre> Genres { get; set; }
    // other sets...         
}

Now, due to the Genre property in the Album class and the Albums collection in the Genre class Entity Framework knows that there is a One-to-Many relationship between Genre and Album which is mapped in the database as a foreign-key relationship: The Albums table must have a foreign key to the Genres table. EF now has to figure out what's the name of this foreign key to generate the proper SQL statements. Our model doesn't define the foreign key name explicitely (which would also be possible), so EF follows a set of conventions which are in this case:

• Does the Album class have a property which is named

  [name of navigation property to target class]+[name of primary key property name in target class]

  In our case this would be [Genre]+[GenreId] = GenreGenreId -> No, we haven't such a property.

• Does the Album class have a property which is named

  [name of target class]+[name of primary key property name in target class]

  Again in our case this would be [Genre]+[GenreId] = GenreGenreId -> No, we haven't such a property.

• Does the Album class have a property which is named

  [name of primary key property name in target class]

  In our case this would be [GenreId] = GenreId -> Yes, we have such a property in Album.

So, Entity Framework will assume that the foreign key name in the Album class is GenreId. And the database schema in the DB which is delivered with the MVCMusicStore sample does indeed have such a column and foreign-key relationship.

So, our query should work.

But what would happen if we remove public int GenreId { get; set; } from the Album class or write it wrong ("GenresId" or something)? None of the three convention rules above would apply and EF would assume that our model class does not have a property which represents the foreign key of the relationship. But it still assumes that there is a relationship (because of the navigation properties). To generate SQL statements which involve this relationship EF has to assume any name of the foreign key column in the DB. And for this assumption it follows the first of the three rules above, so it assumes the foreign key name is GenreGenreId - but in the database schema the name is GenreId -> Boom!

So, final question: Does the Album class of the example you are running have a GenreId property? (I've seen that MusicStore has several example steps, and not in all of them Album has this property.)

(If it definitely has such a property, forget this post.)

Edit:

GenreId in Genre and GenreId in Album must have the same type, otherwise the third convention rule above does not apply. In your example you have different types (Int16 and Int32). This could be the problem.

Answer 2

I have a similiar problem, and it seems to be because the Album class has a GenreID and the Genre class has a list of albums. So EF (I don't yet understand why) will now start looking for the column GenreGenreID in the queries it generates for the Albums DbSet. If you create an entity configuration for Genre that ignores the albums field in Genres, then this will "fix" the problem. Or if you add an ignore attribute to the albums property on Genre it will also "fix" the problem.

Option 1:

    public class GenreConfiguration : EntityTypeConfiguration<Genre>
    {
        public GenreConfiguration()
            : base()
        {
            HasKey(p => p.GenreId);
            Property(p => p.GenreId).
                HasColumnName("GenreId").
                HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity).
                IsRequired();
            Property(p => p.Name).
                HasColumnName("Name").
                IsRequired();
            Ignore(p => p.Albums);
        }
    }

In the OnModelCreating method your dbcontext you will need something like this

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
       modelBuilder.Configurations.Add<Genre>(new GenreConfiguration());
       modelBuilder.Entity<Genre>().ToTable("Genre");
    }

Option 2:

    public class Genre
    {
        public int GenreId { get; set; }
        public string Name { get; set; }

        [NotMapped]
        public List<Album> Albums { get; set; }
    }

You're problem now will be that you have to populate the albums field on genre yourself. I am not well versed in EF myself, but I'm pretty sure that is how I was able to solve this problem.