How can I query the foreign key objects using Entity Framework?

Question

I am trying to learn about Entity Framework 6, and I am running into an issue, that I have been able to reproduce in a test project:

A Movie has a Nameand a Revenue. A Revenue has a GrossIncome:

public class Movie
{
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }
        public Revenue Revenue { get; set; }
}

public class Revenue
{
        [Key]
        public int Id { get; set; }
        public double GrossIncome { get; set; }
}

I am trying to use EF6 code-first to persist some data about movies in the database:

public class MovieContext: DbContext
{
    public MovieContext(): base("name=testDB") { }
    public DbSet<Movie> Movies { get; set; }
    public DbSet<Revenue> Revenues { get; set; }
}

I start by inserting a new movie, with its associated revenue in the DB:

using (var context = new MovieContext())
{
    Revenue revenue = new Revenue()
                {
                    GrossIncome = 10
                };
    Movie movie = new Movie()
                {
                    Name = "foo",
                    Revenue = revenue
                };

    context.Movies.Add(movie);
    context.SaveChanges();
}

I can see in SQL Server that the tables are created, and that a Movies.Revenue_Id column has been created, with a foreign key relationship to Revenue.Id.

If I try to query it using SQL, it works fine:

SELECT Movies.Name, Revenues.GrossIncome
FROM Movies
LEFT JOIN Revenues ON Movies.Revenue_Id = Revenues.Id

returns

Name    GrossIncome
----------------------
foo         10

However, if I try to use Entity Framework to query the data:

using (var context = new MovieContext())
{
    List<Movie> movieList = context.Movies.ToList();
    Console.WriteLine("Movie Name: " + movieList[0].Name);

    if (movieList[0].Revenue == null)
    {
        Console.WriteLine("Revenue is null!");
    }
    else
    {
        Console.WriteLine(movieList[0].Revenue.GrossIncome);
    }

    Console.ReadLine();
}

The console reads:

Movie Name: foo     <- It shows that the query works, and that the data in the main table is fetched.
Revenue is null!    <- Even though the data in the DB is correct, EF does not read the data from the foreign key.

My question is simple: what am I doing wrong? How are the foreign key values supposed to be read?

Denbighshire

Maybe you have lazy loading instead of eager loading? Try an Include() of the related object. – Testaceous

Answer 1

Just include the child entity you want to load:

using (var context = new MovieContext())
{
    List<Movie> movieList = context.Movies
                                   .Include(m => m.Revenue)   // ADD THIS INCLUDE
                                   .ToList();
    Console.WriteLine("Movie Name: " + movieList[0].Name);

    if (movieList[0].Revenue == null)
    {
        Console.WriteLine("Revenue is null!");
    }
    else
    {
        Console.WriteLine(movieList[0].Revenue.GrossIncome);
    }

    Console.ReadLine();
}

This will load the movies - and also make sure that all the references to their respective .Revenue references have been loaded, too.

Brebner

This works thanks. For future readers, turning the Revenue property virtual in the Movie class also solves the issue, as it enables lazy loading. – Denbighshire

I would also like to add what I did, in addition to the comments above, is add the package: Microsoft.EntityFrameworkCore.Proxies then under the ConfigureServices method where I add the dbcontext included a call to the UseLazyLoadingProxies() method: services.AddDbContext<MyDbContext>(options=>options .UseLazyLoadingProxies() .UseSqlServer(Configuration.GetConnectionString("CONN")) ); – Glume

@jamesstap: this looks like you're using EF Core - this question was and is about the "original" EF6 on the full, classic .NET framework (not .NET Core) – Brebner

@Brebner that is correct and notable, however this question resembled my issue the best I could find. just hoping this helps the next person. – Glume

Answer 2

If for whatever reason you can't use the .Include() method in your linq query, you can use the following code:

await dbContext.Entry(movie).Reference(m => m.Revenue).LoadAsync();

This works for a single entry.

Dahabeah