c# - Whats wrong with this group join -

i have profile class has father property self references itself, want load profile fathers profile in 1 sql join statement

select * profile left join profile father on profile.fatherid = father.id profile.id = 650 

so created following linq statement, instead of running sql statement runs following statements

select * profiles  select * profiles id = 650 

then in memory groups them together, not want load whole database.

    private class result     {         public profile profile { get; set; }         public ienumerable<profile> fathers { get; set; }     }     private result mapfather(profile p, ienumerable<profile> father)     {         return new result() {profile = p, fathers = father.defaultifempty()};     }     var profiles = p in db.profiles p.id.equals(650) select p;    var fathers = f in db.profiles select f;    var groupjoin = profiles.groupjoin(fathers,                                         p => p.fatherid,                                          f => f.id,                                          mapfather).tolist(); 

your call groupjoin() getting ienumerable version because of call mapfather func<,> instead of expression>. therefore calls profiles , fathers being executed before groupjoin.

try inlining mapfather():

var groupjoin = profiles.groupjoin(fathers,                                     p => p.fatherid,                                      f => f.id,                                      (p, fathers) => new result() {profile = p, fathers = father.defaultifempty()}).tolist();