views:

87

answers:

2

Hello everyone,

I got the following setup for my database:

Category Table (Fields: CategoryID(PK), Title);

Menu Table (Fields: MenuID(PK) CategoryID(FK), Title);

Page Table (Fields PageID(PK), MenuID(FK), Title, Content, CreatedOn);

Now for one page I want to know how many Pages a particular category holds. I have no clue how to make such query with SubSonic. The way I'm doing it now is like this:

int count = 0;
DAL.MenuCollection coll = new DAL.MenuCollection().WHERE(DAL.ObjectMenu.Columns.CategoryID, _catid);

foreach(DAL.Menu item in coll)
{

  DAL.PageCollection collTemp = new DAL.PageCollection().WHERE(DAL.Page.Columns.MenuID, _menuid);
  count+= collTemp.Count;

}

This will work but isn't there a better way to write it within a single statement? This looks kinda bad I think,

I Hope someone can point me in the right direction. Thank you for reading Kind regard,s Mark

+1  A: 

You need some joins and then some post-retrieval work. If you want to do this all at once, load up a query with what you need to know using joins and then roll a loop over it, organizing it as you need to.

Here's more on joins and queries in general: http://www.subsonicproject.com/docs/Simple%5FQuery%5FTool

Rob Conery
Since i cant compare to rob i will just add my answer as a comment , you could always create it as a view and then just count the number of lines returned
RC1140
A: 

Thank you Rob . I will try when I'm at home. I keep you posted regards, Mark

Mark
You've got two accounts - this one (http://stackoverflow.com/users/186445/mark) and the one used to ask the question (http://stackoverflow.com/users/201340/mark). e-mail [email protected] about getting them merged
ChrisF