What's required for ExecuteStoreQuery to respect relationships?

Question

At the moment it seems to me kind of arbitrary when it fails. Here is my case.

Entity Foo:

 class Foo {
      int FooID {get;set;
      User Creator {get;set;}
      Bar TheBar {get;set;}
      DateTime CreatedDateTime {get;set;}
 }

Entity User:

 class User {
      int UserID {get;set;}
      ObjectWhatchamacallit Foos {get;set;}
      DateTime LastLogInDateTime {get;set;}
 }

So

 return DB.ExecuteStoreQuery<Foo>("SELECT *, 
      Created AS CreatedDateTime,
      LastLogIn AS LastLogInDateTime
      FROM
      [User] 
      JOIN Foo ON Foo.CreatorID = [User].UserID
      JOIN Bar ON Foo.BarID = Bar.BarID",
      "Foo");

will load the Foos fine, with the Bars but not the Creators.

 return DB.ExecuteStoreQuery<User>("SELECT *, 
      Created AS CreatedDateTime,
      LastLogIn AS LastLogInDateTime
      FROM
      [User] 
      JOIN Foo ON Foo.CreatorID = [User].UserID",
      "User");

doesn't perform any better. Users are loaded, but without Foo.

Can this be because

1. of the aliases in both Foo and User?
2. User is an SQL keyword? (I've tried aliasin [User] AS Creator, to no avail)
3. of something else?

Updates

I'm running into this problem time and time again. I've realized aliasing (1) probably doesn't have anything to do with it. User being a T-SQL keyword (2) might be an issue in this particular instance but isn't common among all the times I've had this problem. So the answer to the general question is it's something else (3).

One concern is that some of the relationship names might have been changed from the ones originally suggested by the edmx-designer corresponding to the storage key column names. But to me this seams arbitrary in any case.

So I'm putting out again the general question stated in the title, how exactly does EF deduce that a subset of the columns returned by the query is actually part of a related enitity?

Answer 1

It turns out I was missguided. The answer to What's required for ExecuteStoreQuery to respect relationships? is knock yourself out, nothing will.

The only reason this sometimes worked for me was that I had automatic loads hidden away in my business layer, a fact I would have easily discovered had I paid attention to IntelliTrace.

Until yesterday there was no hope and I was forced to accept that my model was to complicated for EF, and I prepared to balance the line between 50000 lines of T-SQL and a controller riddled with explicit loads.

For really heavy queries I've started to divide up the query into several, still using ExecuteStoreQuery. All the related results are automatically loaded into the context and relations set up appropriately.

The new release of EF June 2011 CTF may prove to be just what I need.

I am aware that similar functionality was available in EFExtensions. Stored procdures returning multiple result sets. As of writing I'm not sure how many trips to the database this query requires but I can't imagine why more than one would be required. At maximum one per resultset which is still alot better than explicit loading.

Let's see how this works out.