Retrieving data from related tables with EF 6 and C#

Question

I'd like to retrieve data from my DB with two tables (Scientists and Countries). This is how i programmed my class

 public class Scientist 
    {
        public long ScientistID { set; get; }
        public string Name { set;   get; }
        public string Surname { set; get; }
        public string BornDate { set; get; }
        public string Subject { set; get; }
        public long? CountryID { set; get; }
        
        public virtual Country Country { set; get; }

    }


    public class Country
    {
        public long CountryID { set; get; }

        public string CountryName { set; get; }

        public string Zone { set; get; }

        public virtual List<Scientist> Scientists { set; get; }
    }

    public class PeopleOfScienceContext : DbContext
    {
        public PeopleOfScienceContext() : base("ScientistsConnectionString")
        {
            Database.SetInitializer<PeopleOfScienceContext>(new CreateDatabaseIfNotExists<PeopleOfScienceContext>());
        }

        public DbSet<Scientist> Scientists { get; set; }
        public DbSet<Country> Countries { get; set; }


       
    } 

Is there a "simple" way to download from DB both content since they're correlated? I mean, with something like:

 List<Scientist> scients = ctx.Scientists.ToList(); //ctx was initialized don't worry!

I can download all my data from Scientist table, but i cannot download the "CountryName" info since it is stored in another table and it remains blank. I'd like to avoid to create a "JOIN" query; since i'm learning a Frame Work i've been told to "write the bare minimum" code. My second idea was to download both tables and merge them "client side", but still seems an useless complication of the task (and i bet this method couldn't scale well with big tables). Am I missing the simplest solution or there isn't such a thing in EF 6?

Answer 1

I believe the concept you are looking for is Eager Loading.

Although you could explicitly join the Scientist and Country tables in a Linq Query, because you already have the navigation property defined, you should be able to simply Include the navigation:

List<Scientist> scientists = ctx.Scientists
   .Include(s => s.Country)
   .ToList()

... or the async equivalent (since this is I/O bound work)

var scientists = await ctx.Scientists
   .Include(s => s.Country)
   .ToListAsync();

And you should now be able to dereference the country like so:

scientist.Country.CountryName 

In the event of the relationship being optional (i.e. CountryId can be NULL in the database), then you could use the nullsafe dereference operator:

scientist.Country?.CountryName