LINQ to SQL: Child property is null even though it was returned

Question

Scenario: A Customer entity has properties for State and Color. i.e. Washington and green. The SQL Server foreign key relationships are setup fine, and all have correct data with proper referential integrity.

Consider this LINQ to SQL query:

var cust = (from c in db.Customers
  join s in db.States on c.StateID equals s.ID
  join cl in db.Color on c.ColorID equals cl.ID
  where c.ID == someCustomerID
  select c).SingleOrDefault();

Given: The SQL statement that is produced, when viewed through SQL Server Profiler generates a proper query. Capturing and running the same query in SQL Management Studio returns the data as expected.

The cust.StateID and cust.State properties are populated fine. I am able to inspect those properties fine just after executing the above L2S query.

Problem: The cust.Color object, evaluates to null. The good news is that the cust.ColorID s populated with the correct FK number from the DB. There IS a row in the Color table with that ID.

Question: how can this query be changed or improved to include the Color entity?

Edit: Closing This Question

The root of the problem was on the LINQ To SQL designer and a previous screw-up on the FK relationship. The association between Customer and Color had the association to another field on the Customer. This was total PEBKAC, and I hadn't checked the L2S designer for any problems. I deleted the Color table from the L2S designer, refreshed the table list in Server Explorer, and dragged the Color table over to L2S again. The association was corrected.

Answer 1

Do you have a LoadOptions object associated with your data context? Try adding that with this:

var lo = new LoadOptions(x => x.Color);
lo.LoadWith<Color>();
using (var db = new SomeDataContext())
{
  db.LoadOptions = lo;
. . . 
}

Answer 2

The root of the problem was on the LINQ To SQL designer and a previous screw-up on the FK relationship. The association between Customer and Color had the association to another field on the Customer. This was total PEBKAC, and I hadn't checked the L2S designer for any problems. I deleted the Color table from the L2S designer, refreshed the table list in Server Explorer, and dragged the Color table over to L2S again. The association was corrected.