entity framework - What's required for ExecuteStoreQuery to respect relationships? -

at moment seems me kind of arbitrary when fails. here 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 createddatetime,       lastlogin lastlogindatetime             [user]        join foo on foo.creatorid = [user].userid       join bar on foo.barid = bar.barid",       "foo"); 

will load foos fine, bars not creators.

 return db.executestorequery<user>("select *,        created createddatetime,       lastlogin lastlogindatetime             [user]        join foo on foo.creatorid = [user].userid",       "user"); 

doesn't perform better. users loaded, without foo.

can because

1. of aliases in both foo , user?
2. user sql keyword? (i've tried aliasin [user] creator, no avail)
3. of else?

updates

i'm running problem time , time again. i've realized aliasing (1) doesn't have it. user being t-sql keyword (2) might issue in particular instance isn't common among times i've had problem. answer general question it's else (3).

one concern of relationship names might have been changed ones suggested edmx-designer corresponding storage key column names. me seams arbitrary in case.

so i'm putting out again general question stated in title, how ef deduce subset of columns returned query part of related enitity?

it turns out missguided. answer what's required executestorequery respect relationships? knock out, nothing will.

the reason worked me had automatic loads hidden away in business layer, fact have discovered had paid attention intellitrace.

until yesterday there no hope , forced accept model complicated ef, , prepared balance line between 50000 lines of t-sql , controller riddled explicit loads.

for heavy queries i've started divide query several, still using executestorequery. related results automatically loaded context , relations set appropriately.

the new release of ef june 2011 ctf may prove need.

i aware similar functionality available in efextensions. stored procdures returning multiple result sets. of writing i'm not sure how many trips database query requires can't imagine why more 1 required. @ maximum 1 per resultset still alot better explicit loading.

let's see how works out.