Subsonic query, i suck! -

I am using Subsonic 2.2 in one of my projects. I have a comment section where I am called a table called the comments. First of all I ask all the records with ParentId = 0, and then in the foreach statement I ask for all records with ParentId = currentRecord.Id. Now I know this is a bad habit, but I do not know how to remove it in a single query through the subscore, I am missing something important.

I hope someone can talk to me right direction Thanks for your time!

Kind regards, Mark

  [WebMethod] Public listing & lt; Comment & gt; GetComments (int help) {DAL.CommentCollection Coll = new DAL.CommentCollection (); Subsonic.corey qry = new subsonic configuration (DAL.Comment.Schema); Qry.AddWhere (DAL.Comumn.Column.ArticleID, Support); Qry.AddWhere (DAL.Comumn.Column.ParentID, 0); Qry.AddWhere (DAL.Comment.Columns.IsActive, True); Qry.AddWhere (DAL.Comment.Columns.Is removed, incorrect); Qry.ORDER_BY (DAL.Comumn.Column.CreatedOn, "ASC"); Qry.PageSize = Class. Settings. Control. notes. GetCommentsPerPage (); Coll.LoadAndCloseReader (qry.ExecuteReader ()); Forrest (item in DAL.) {Comment C = New Comment (); C.Date = Convert. ToDateTime (item.CreatedOn). ToLongDateString (); C.UserName = item.User.UserName; C.FullText = item.FullText; C.Gravatar = Classes.Data.HashString (item.User.GravatarId); C.IsSub = false; C.CommentId = (int) item CommentID; Comments.Add (c); // GetReplies (item). Comment, help); } Return comment; } Private Zero GetReplies (integer CommentId, int help) {DAL.CommentCollection Coll = new DAL.CommentCollection (); Subsonic.corey qry = new subsonic configuration (DAL.Comment.Schema); Qry.AddWhere (DAL.Comumn.Column.ArticleID, Support); Qry.AddWhere (DAL.Comment.Column.ParentID, CommentId); Qry.AddWhere (DAL.Comment.Columns.IsActive, True); Qry.AddWhere (DAL.Comment.Columns.Is removed, incorrect); Qry.ORDER_BY (DAL.Comumn.Column.CreatedOn, "ASC"); Qry.PageSize = Class. Settings. Control. notes. GetCommentsPerPage (); Coll.LoadAndCloseReader (qry.ExecuteReader ()); Forrest (item in DAL.) {Comment C = New Comment (); C.Date = Convert. ToDateTime (item.CreatedOn). ToLongDateString (); C.UserName = item.User.UserName; C.FullText = item.FullText; C.Gravatar = Classes.Data.HashString (item.User.GravatarId); C.IsSub = True; C.CommentId = (int) item CommentID; Comments.Add (c); }}  

If all the items are linked together with the article ID, then Load them once and sort them into memory.

  // Load all article comments from Database DAL. Comment Collections DB Comments = New Select () (DAL.Comment.Schema) Where (DAL. ArticleIDColumn) IseEqualTo (help). And (DAL.Comment.IsActiveColumn). ICEEqualTo (true). And (DAL.Comment.IsDeletedColumn). ICEEqualTo (wrong). Order By (DAL.Comment.Columns.ParentID, DAL.Comment Columns were created). ExxchangeExclusion & lt; DAL.CommentCollection & gt; (); // Now sort all comments, but the required DAL.CommentCollection parent = new DAL.CommentCollection (); Foreign (comment parents in DAL DB Commendants) {If parents (parent == 0) (parents, parents);} and break; // the way we ordered the above items Now all parents should be processed. Now loop through the parents collection and add the children's last collection of parents to the children under foreign hair (in the parents name of DAL parents) {Comments.add (parent); foreign issues Parent ID: 0 & Possible Child. Parent ID == Parent.ID) {comments.add (possible child);}}} Return comment (if possible, child in DB committees) ;