Entity Framework - How to handle table relationships?

Question

I have developed a fairly small asp.net MVC application using the repository pattern and Linq to Sql. I would now like to access and update the same data using the Entity Framework, however I am having trouble understanding if my syntax is correct for table relationships in the Entity Framework.

Say I am retrieving a row of data for a User and I would also like to retrieve the User's assosiated Business (foreign key in the User table). Right now I am doing something like this:

using (MyDatabaseEntities context = new MyDatabaseEntities())
{
    User user = db.User.FirstOrDefault(u => u.Users.UserId == userId);

    if (!user.BusinessReference.IsLoaded)
    {
        user.BusinessReference.Load();
    }

 return user;
}

From the examples I've seen I should do something like this to access the business table data:

foreach (Business business in user.BusinessReference)
{
    var b = business;
    ViewData["BusinessName"] = b.BusinessName;                    
}

Am I going about this the right way? Any advice would be greatly appreciated.

Answer 1

No, that doesn't seem quite right. This would be more like it:

User user = db.User.Include("Business").FirstOrDefault(u => u.UserId == userId);

This assumes that your User Entity has a navigation property named Business.

Answer 2

Except for the foreach part which does not really say what you are trying to do since you are in the loop overwriting the ViewData["BusinessName"] you are pretty much on track with what's going on.

If this is let's say a user being displayed and you want to also display some BusinessNames they should be available in the view. The boring thing with Entity Framework and the reason I chose to temporary use other solutions is the having to load the references.

a better solution in the example you provided might be:

using (MyDatabaseEntities context = new MyDatabaseEntities())
{
    return (from u in user.Include("Business")
      where u.UserId == userId
      select u).FirstOrDefault();
}

Unfortunately you can't preload any references deeper than that in an easy way, in that case your need to loop through all the way and Load.

Answer 3

I would do this:

1. Make a presentation model for your view and change the view to a strongly typed view.
2. Use L2E to project onto the presentation model.

Like this:

var model = (from u in Context.Users 
             where u.UserId == userId
             select new UserPresentation
             {
                 UserName = u.Name,
                 BusinessName = u.Business.BusinessName,
                 SomeOtherDatumYourViewNeeds = // ...
             }).FirstOrDefault();

Some important points here:

• You don't have to worry about eager loading. The data you need will always be loaded.
• You are not asking the SQL Server to return date a you don't need, like properties of User or Business which you don't intend to display in the view.
• Your view is now decoupled from your entity model. They can both evolve independently.