Why does a simple entity data model relationship return null instead of a reference to an object?

Question

I have 2 simple tables and a foreign key defined in SQL Server Express:

---

Product

• ProductID [auto-inc]
• Name
• CompanyID [not-null]

Company

• CompanyID [auto-inc]
• Name

FK_Product_Company

• Product.CompanyID = Company.CompanyID

---

I created an ADO.NET Entity Data Model and added all the tables to it. The .edmx file shows the 1-to-many relationship in the designer. I manually populated the database with data that would make sure each product has a company. Whenever I try to access a product's company, however, it always returns null instead of an instance of a company.

For example, running the following code in C#, returns null:

var _db = new MyDBEntities();
var product = (from p in _db.Product
               where p.ProductID == 3
               select p).First();
product.Company // == null

Are there any steps I'm missing to get this to work?

Thanks

---

Things I have tried:

---

Running the following SQL, returns the company record correctly.

SELECT Company.*
FROM Product
  LEFT JOIN Company ON (Product.CompanyID = Company.CompanyID)
WHERE Product.ProductID = 3

---

The next thing I would do to debug this issue, would be to run the following code:

var _db = new MyDBEntities();
var product = (from p in _db.Product
               where p.ProductID == 3
               select p).First();
var company = (from c in _db.Company
               where c.CompanyID == product.CompanyID
               select c).First();

However, that does not compile because the field Product.CompanyID is hidden by the ORM generator and I don't see an option in the designer to add it.

---

The next best thing to run I guess is the following, and it did return a company:

var _db = new MyDBEntities();
var company = (from c in _db.Company
               where c.CompanyID == 2
               select c).First();

Just to be clear, Product with ID 3 corresponds to Company with ID 2.

---

I tried creating the exact same relationship using LINQ to SQL Classes and it works fine.

Of course instead of using the DB Entities class, I am using the Data Context class.

Answer 1

I can't see any cause to why you would not receive your company record. The obvious; you definitely have a "p.ProductID" record equal to 3?

I also use "FirstOrDefault()" for selecting a single record, as I understand this will cleanly return a null record where it does not exist instead of throwing an exception on record non-existence.

Answer 2

You need to add the .Include method to your query.

var _db = new MyDBEntities();
var product = (from p in _db.Product.Include("Company")
               where p.ProductID == 3
               select p).First();

Answer 3

You can use Include as suggested above.

Alternatively try calling

product.Company.Load() to retrieve the object.

The Entity Framework uses deferred loading to avoid unnecessary queries until the object is actually required..

There is some more info about deferred loading here : http://www.code-magazine.com/article.aspx?quickid=0711051&page=4

Answer 4

Deeploading is supported by the Entity framework, but lazy loading isn't (lazy loading will be supported in Entity framework 2.0).

With Load() or .Include("Company") like the previous posts say it will work.

Here is a walkthough how you can make the entity framework lazy: http://blogs.msdn.com/jkowalski/archive/2008/05/12/transparent-lazy-loading-for-entity-framework-part-1.aspx