EF 4.1 Code First : How to load entity references into memory?

Question

I just started working with EF 4.1 Code First and noticed that by default, references (navigation properties), are not loaded into memory with a POCO entity you queried with LINQ-to-Entity. I have had no success with making the referenced entities load using DbEntityEntry.Reference. When I call DbReferenceEntry.Load, the following exception is thrown:

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

Closing a DataReader is not something I really want to have to do when I'm in the middle of several LINQ queries.

For example, the following will not work:

using (db1 = new NorthindDbContext(new SqlConnection(this.NORTHWIND))) { 
orders = db1.Orders.Where(o => !(o.CustomerId == null || o.ShipperId == null || o.EmployeeID == null));
            foreach (var o in orders) {
                Shipper s = o.Shipper;//exception: "There is already an open DataReader associated with this Command which must be closed first."
                DbEntityEntry<Order> entry = db1.Entry(o);
                DbReferenceEntry<Order, Shipper> shipper_reference = entry.Reference<Shipper>("Shipper");
                if (!shipper_reference.IsLoaded) {
                    shipper_reference.Load();                   
                }
            }
        }

Here is the Order class:

public partial class Order
{
    public System.Int32 ID { get; set; }                
    public System.Nullable<System.DateTime> OrderDate { get; set; }
    public System.Nullable<System.DateTime> RequiredDate { get; set; }
    public System.Nullable<System.DateTime> ShippedDate { get; set; }       
    public System.Nullable<System.Decimal> Freight { get; set; }        
    public Employee Employee { get; set; }
    public Int32 CustomerId { get; set; }
    public Customer Customer { get; set; }
    public Int32 EmployeeID { get; set; }
    /// <summary>
    /// marked virtual for lazy loading
    /// </summary>
    public virtual Shipper Shipper { get; set; }
    public Int32 ShipperId { get; set; }
} 

I have tried marking the Order.Shipper property as virtual, and I still get the same exception if when I run the code.

The ObjectQuery.Include method, does work:

[TestMethod]    
//configure MARS here?
//Order.Shipper is not marked virtual now
//...
            using (db = new NorthindDbContext(new SqlConnection(this.NORTHWIND))) {                         
            db.Orders.Include(o => o.Shipper)
.Where(o => !(o.CustomerId == null || o.ShipperId == null || o.EmployeeID == null));
            foreach (var o in orders) {
                Shipper s = o.Shipper;//null
                DbEntityEntry<Order> entry = db.Entry(o);
                DbReferenceEntry<Order, Shipper> shipper_reference = entry.Reference<Shipper>("Shipper");
                if (!shipper_reference.IsLoaded) {
                    shipper_reference.Load();//"There is already an open DataReader associated with this Command which must be closed first."
                }


            }

With EF 4.1 Code First, how do you make a referenced entity load into memory?

Answer 1

If you need multiple database reads work in the same time you must allow MARS on your connection string but your real problem is elsewhere.

EF doesn't load navigation properties by default. You must either use lazy or eager loading. Lazy loading needs all navigation properties in entity to be virtual:

public class Order
{
    ...

    public virtual Shipper Shipper { get; set; }
}

Once lazy loading and proxy creation is allowed on context (default) your property will be automatically loaded when first time accessed by your code. This access must happen within scope of the same context used to load the order (you can still meet the error with opened DataReader here).

Other way is to load Shipper directly with Order by using eager loading:

var query = context.Orders.Include(o => o.Shipper)...