views:

313

answers:

1

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

+2  A: 

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);
Rob Conery
Thanks for the response, Rob. This indeed does work. I believe I do have SQL Profiler available but not installed. I have a full copy of SQL Server, but only have SQL Express installed on my dev box. I'll look into it further when I get some breathing room.Dave