Entity Framework Code First Navigation

Entity Framework folks can't claim their ORM can implement things the POCO way, classess are tainted with attributes, it's hard to call it Plain Old CLR Object anymore. I think that's why they just settle with Code-First, whatever that mean :-)


The Objects

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel.DataAnnotations;

namespace EfCodeFirstNavigation.Model
{


    public class Person
    {
        [Key]
        public int PersonID { get; set; }
        public string PersonName { get; set; }
        public int TheCountryID { get; set; }
        

        [ForeignKey("TheCountryID")]
        public virtual Country Country { get; set; }
    }

    public class Country
    {
        [Key]
        public int CountryID { get; set; }
        public string CountryName { get; set; }
        public int Population { get; set; }

        public virtual ICollection<Person> Persons { get; set; }
    }


}

The Relational Mapping and sample data(in comments)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;



namespace EfCodeFirstNavigation.Model
{
    public class CatalogContext : DbContext
    {

        public DbSet<Person> Persons { get; set; }
        public DbSet<Country> Countries { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();


            
        }
    }
}

/* DDL
 

create table Country
(
CountryID int identity(1,1) primary key,
CountryName varchar(100) not null,
Population int not null
);


create table Person
(
PersonID int identity(1,1) primary key,
PersonName varchar(100) not null,
TheCountryID int not null references Country(CountryID)
);


insert into Country(CountryName, Population) values('Philippines', 9)
insert into Country(CountryName, Population) values('China', 2)

insert into Person(PersonName, TheCountryID) values('Michael',1)
insert into Person(PersonName, TheCountryID) values('Jolin',2)
 
*/

Read database:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EfCodeFirstNavigation.Model;

using System.Data.SQLite;

namespace EfCodeFirstNavigation
{
    class Program
    {
        static void Main(string[] args)
        {
            var db = new CatalogContext();
            
            // I don't know why the need to use Include(this is for eager loading, analogous to Fetch of NHibernate) 
            // I thought Entity Framework supported lazy loading already.
            // I'll just research next time why lazy loading doesn't work.
            foreach(var p in db.Persons.Include("Country"))
            {
                Console.WriteLine("{0} {1}", p.PersonName, p.Country.CountryName);
            }
        }
    }//Program
}

Here's the generated query of Entity Framework:

SELECT 
[Extent1].[PersonID] AS [PersonID], 
[Extent1].[PersonName] AS [PersonName], 
[Extent1].[TheCountryID] AS [TheCountryID], 
[Extent2].[CountryID] AS [CountryID], 
[Extent2].[CountryName] AS [CountryName], 
[Extent2].[Population] AS [Population]
FROM  [dbo].[Person] AS [Extent1]
INNER JOIN [dbo].[Country] AS [Extent2] ON [Extent1].[TheCountryID] = [Extent2].[CountryID]

UPDATE(an hour later)

When not including Include, this error happen...

There is already an open DataReader associated with this Command which must be closed first.

..., the solution is to add MultipleActiveResultSets=True to your connection string, rationale can be found from MSDN. You can now remove Include from your Linq. Entity Framework supports lazy loading.