SingleOrDefault query returns null (sometimes) even though the data is there

Question

I'm a SubSonic newbe and am writing my first app with it. I have a database with two tables called Member and Club. The Club table has three fields which link to the primary key (ID) in the Member table (PresidentID, VicePresidentID, and TreasurerID). If I make three calls to Member.SingleOrDefault, they all return valid data. i.e.:

President = Member.SingleOrDefault(x => x.ID == club.PresidentID);
VicePresident = Member.SingleOrDefault(x => x.ID == club.VicePresidentID);
Treasurer = Member.SingleOrDefault(x => x.ID == club.TreasurerID);

SubSonic nicely creates a Members property in the active record Club class which refers back to the Members table. However, if I make the same calls through the Members property of the Club activerecord class, some return nulls. i.e.:

President = Club.Members.SingleOrDefault(x => x.ID == club.PresidentID);
VicePresident = Club.Members.SingleOrDefault(x => x.ID == club.VicePresidentID);
Treasurer = Club.Members.SingleOrDefault(x => x.ID == club.TreasurerID);

One or possibly two of the calls will return data, but the others will return null. This happens with the exact same member IDs, What am I doing wrong?

Many thanks for any pointers.

Dave Head

Answer 1

I think it's getting confused with the IQueryable FK setup - meaning that it's not honoring all of the foreign keys when creating the child IQueryables.

If you have SQL Profiler it would be great to know what's getting generated on those calls. For now you can get around this by using:

President = Members.SingleOrDefault(x=>x.ID==club.PresidentID && x.ClubID=club.ClubID);