SubSonic 2.1 help needed c#

Question

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

Answer 1

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

Answer 2

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