How can I load related objects in EF?

Question

I want to show Categories of Products I test two approaches : 1.

public ActionResult Index()
    {
        NORTHWNDEntities _db = new NORTHWNDEntities();
        IList<ProductViewModel> pList = new List<ProductViewModel>();
        foreach (var p in _db.ProductSet.Include("Category"))
        {

            ProductViewModel p1 = new ProductViewModel(){Name = p.ProductName,Price =p.UnitPrice ?? 0,Category = p.Category.CategoryName};
            pList.Add(p1);
        }

        return View(pList);
    }

2.

public ActionResult Index()
    {
        NORTHWNDEntities _db = new NORTHWNDEntities();
        IList<ProductViewModel> pList = new List<ProductViewModel>();
        foreach (var p in _db.ProductSet)
        {
            p.CategoryReference.Load();
            ProductViewModel p1 = new ProductViewModel(){Name = p.ProductName,Price =p.UnitPrice ?? 0,Category = p.Category.CategoryName};
            pList.Add(p1);
        }

        return View(pList);
    }

I like second way because I hate magic strings.
I want to know is there another approaches for this ?
And which is better ?

Answer 1

Look at SQL Profiler and you'll see that second method executes more sql statements. It does it for every category, so it is slower.

You could do it like:

var pList = _db.ProductSet.Select(p =>  new ProductViewModel(){Name = p.ProductName,Price =p.UnitPrice ?? 0,Category = p.Category.CategoryName});

Both of your solution take to many data from database. You need only few fields, but take whole entities. My solution takes only needed fields.