How to pass query results with FK relations(EF) to the View

Question

Hi, I am not able to figure out this simple thing in ASP.NET MVC:

I have two tables:

  Customer:
    CustomerID
    FirstName
    LastName
    AddressID
    AddressTemporaryID

  Address:
    AddressID
    Street
    City

I have my relations(FK) set in DB and trying to use Entity Framework. My navigation properties(FK) are named Addresses and Addresses1. I created repository:

  public interface ICustomertRepository {
    IQueryable<Customer> FindAllCustomers();
  }

  public class CustomerRepository : MvcApplication2.Models.ICustomerRepository {
    public IQueryable<Customer> FindAllCustomers() {
      return db.Customers;
    }
  }

And my HomeController has this:

  public ActionResult Index()
  {
    var query = customerRepository.FindAllCustomers();
    return View(query);
  }

But how can I access values in my View? I can see item.Adresses.Street/item.Adresses1.Street with Intellisense in Model but when I will try it, I get NullReferenceException:

<% foreach (var item in Model)
   { %>
<tr>
    <td>
        <%= Html.Encode(item.FirstName) %>
    </td>
    <td>
        <%= Html.Encode(item.Surname) %>
    </td>
    <td>
        <%= Html.Encode(item.Adresses.Street) %>
    </td>
    <td>
        <%= Html.Encode(item.Adresses1.Street) %>
    </td>
</tr>
<% } %>

I am completely lost. I tried to search everywhere but without any success.

Answer 1

Are you sure that

 var query = customerRepository.FindAllCustomers();

works properly? Have you checked on the controller side that the query is in fact populated? You should write some unit tests, but at the very least, set a breakpoint on that line, and see if query gets populated correctly.

Answer 2

EF does not do automatic lazy loading.

You need to either manually load the related objects (not feasible for a multiple result set like your query), or instruct EF to include the desired properties for a whole query:

public IQueryable<Customer> FindAllCustomers() {
    return db.Customers.Include("Adresses").Include("Adresses1");
  }

You can find a comparison of how to do it between EF and LinqtoSQL (if you are used to it) in this post. It will at least show you the EF syntax for that anyway.

Answer 3

Actually in your scenario where the AddressId and Address1Id are FKs in the table:

While the actual references (Address and Address1) are not loaded unless you either do an Include() or an explicit AddressReference.Load().

The actual FK value is loaded into the Entity into the EntityReference's EntityKey i.e.

addressID = item.AddressReference.EntityKey.EntityKeyValues[0].Value;

Hope this helps Alex