Problem with Relational Database in MVC Linq to Entities

Question

I was hoping someone could tell me what is wrong here. I have three tables, LU_LOC_Suburb, Listings, ListingMessages.

---

Listings has the following columns

ID

SuburbID (Coming from LU_LOC_Suburb)

more...

---

ListingMessages has the following columns

ID

ListingID (Coming from Listings)

more...

---

I'm trying to create a Messages page where I first get all the Message for a User;

IQueryable<ListingMessage> Messages = from x in DBEntities.ListingMessageSet.Include("Listings")
                                      where x.Listings.Users.ID == UserID
                                      select x;

I then send this to a View, lets call it Messages for User.

I also limit the messages by listing by taking all the messages for a user and then only selecting the ones that are related to a particular listing;

Messages = from x in Messages
           where x.Listings.ID == ListingID
           select x;

I then send this to a View, lets call it Messages for Listing.

In my View Pages, I want to write the suburb name to screen which I can do by;

<%= Html.Encode(item.Listings.LU_LOC_Suburb.Name) %>

Now here's the problem...

This gives the error - Object reference not set to an instance of an object - when I DON'T limit the Messages by Listing (in the Messages for User View). I do not get this error when I limit the messages by listing.

I dont understand why this is happening. I know it's something simple and am hoping you guys can help me resolve this?

Thanks in advance,

Sheefy

Answer 1

Could it be that you haven't "Included" the "listings" in the Message query?

Answer 2

As you may or may not know, IQueryable is a 'lazy' representation of the actual query. It is not executed until enumerated over. From your code sample, it seems like you're sending the IQueryable directly to your view. Not sure if that is 'wrong' but I always make sure the query is actually executed in the controller. You can do this by adding a ToList() to the statement, when adding it to your ViewModel (or ViewData).

I'm not saying that this will solve your problem, but it will make debugging much easier.

Answer 3

Just incase anyone stumbles past this post and is wondering how to overcome the problem, I found the solution here.

By using the .Include("RelatedTable.ParentTable") I've been able to get around the problem eventhough I still don't quite understand why it was not bringing in the related tables when I wasn't narrowing it down by ListingID.