How Entity framework works with lambda in ASP.NET MVC

Question

I am walking through Mvc Music Store tutorial from www.ASP.net website.

I have problem understanding how Entity Framework has created tables and how those lambda expressions work altogether. I do know basics of EF and Lambda and I have done previous tutorials in this series.

My question is related to Part 4 of the tutorial : Part 4: Models and Data Access There are three classes in Model: Artist, Genre and Album as below.

public class Artist
{
    public int ArtistId { get; set; }
    public string Name { get; set; }
}
public partial class Genre
{
    public int      GenreId     { get; set; }
    public string   Name        { get; set; }
    public string   Description { get; set; }
    public List<Album> Albums   { get; set; }
}
public class Album
{
    public int      AlbumId     { get; set;}
    public int      GenreId     { get; set; }
    public int      ArtistId    { get; set; }
    public string   Title       { get; set; }
    public decimal  Price       { get; set; }
    public string   AlbumArtUrl { get; set; }
    public Genre    Genre       { get; set; }
    public Artist   Artist      { get; set; }
}

However I saw only following columns of each table created by EF.

Artist table: ArtistId, Name

Genre table: GenreId, Name, Description

Album table: AlbumId, GenreId, ArtistId, Title, Price, AlbumArtUrl

Confusion one

Artist table is fine, but Genre table has nothing about List<Album> Albums from the class definition. In Album table public Genre Genre and public Artist Artist is missing.

Below is a statement to add default data to database in SampleData.cs file

new Album 
{ 
    Title = "A Copland Celebration, Vol. I", 
    Genre = genres.Single(g => g.Name == "Classical"), 
    Price = 8.99M, 
    Artist = artists
        .Single(a => a.Name == "Aaron Copland & London Symphony Orchestra"), 
    AlbumArtUrl = "/Content/Images/placeholder.gif" 
}

Question

Now there aren't any fields with names like Genre and Artist in the database table?? GenreId and ArtistId are not inserted but the Album table in database is populated with those values. ???

Confusion two

In Browse action method of StoreController,

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

I dont know how include works here. There is List of Albums field in Genres model class but not in database. I can guess that include() might save list of album there.

Question

But I dont understand why there is "Albums" parameter to Include method? There are two list of Albums one in Genre model class and the other in the class derived from DbContext for EF. How this statement will return only Albums with matching Genre??

Any help is appreciated. Thanks

Answer 1

Artist table is fine, but Genre table has nothing about List Albums from the class definition. In Album table public Genre Genre and public Artist Artist is missing.

Now there aren't any fields with names like Genre and Artist in the database table?? GenreId and ArtistId are not inserted but the Album table in database is populated with those values.

You don't have to directly access your Foreign Key Properties (ArtistID and GenreID) because they're being handled for you via EF Navigation properties. All you have to do is set the Artist and/or Genre and EF handles the rest for you.

The Genre and the Artist properties are navigation properties. They're not actually stored in the database, it's a way for you to access the related Genre entity and the related Artist entity for Album, or to access all of the associated Albums to your Genre.

When you access the Artist property on your Album entity for example - what EF is doing is it's going to give you the information in the Artist Table that has the ArtistID listed in your album.

For example - you have an AlbumA - it has a single GenreA and a single ArtistA. Rather than storing all the information in a table, it's breaking the information into separate tables to for better storage, and re usability. Now you've gotten your AlbumA in your code - but you want to access the associated GenreA's name. You'd use:

AlbumA.Genre.Name

This will go to your album table - get the GenreId, then go to your Genre table, and return the Genre with that ID, and then give you the name property. This saves you the trouble of having to write the function to Get the genre by id for the album you already have, giving you more easily readable code.

But I dont understand why there is "Albums" parameter to Include method? There are two list of Albums one in Genre model class and the other in the class derived from DbContext for EF. How this statement will return only Albums with matching Genre??

What the Include function does, it is eagerly loads your entity. See this blog post for good information about that.

When you load the Genre object, it's only loading the information on the Genre table. When you want to access the associated Album - EF has to make an extra database call. Sometimes this is really bad for performance reasons, so include allows you to do a join, and retrieve all the associated data in a single call, rather than many trips to the database.

Some extra resources about what are navigation properties, and how they work:

1. What are navigation properties
2. Getting started with Navigation Properties