CODEWITHSUNDEEP
linqlinq-to-sql
maztt
maztt

Reputation: 12294

linq to sql fetching all the records category wise in the list<> and then looping

i am fetching all the records from the database with the help of this query organization wise. they become about 30-40 records

     List<PagesRef> paages = (from pagess in pagerepository.GetAllPages()
                  join pagesref in pagerepository.GetAllPageRef()
                  on pagess.int_PageId equals pagesref.int_PageId
                  where (pagess.int_PostStatusId != 3 && pagess.int_OrganizationId == Authorization.OrganizationID)
                  && pagesref.int_PageRefId == pagesref.Pages.PagesRefs.FirstOrDefault(m => m.int_PageId == pagess.int_PageId && m.bit_Active == true && (m.vcr_PageTitle != null && m.vcr_PageTitle != "")).int_PageRefId
                  select pagesref).ToList();

next the next step what i want to do is to loop through the above list as linq to object query without going to the database to generate 3 level hierarchical record. can some one give me some insight or idea how can i do it?

edit

    var parentrecord = paages.Where(n => n.Pages.int_PageParent == 0).OrderBy(m => m.Pages.int_SortOrder == null).OrderBy(m => m.int_PageId);

            foreach (var secondlevel in parentrecord) // if parentrecord found
            {
                var seclevel = paages.Where(m => m.Pages.int_PageParent == secondlevel.Pages.int_PageId).OrderBy(m => m.Pages.int_SortOrder == null).OrderBy(m => m.Pages.int_SortOrder);

                secondlevel.vcr_PageTitle = "parent";
                pagesreff.Add(secondlevel);   // if parentrecord found then loop and add in there

                foreach (var thdlevel in seclevel)
                {
                   var thirdlevel = paages.Where(m => m.Pages.int_PageParent == thdlevel.Pages.int_PageId).OrderBy(m => m.Pages.int_SortOrder == null).OrderBy(m => m.int_PageId).OrderBy(m => m.Pages.int_SortOrder);

                    thdlevel.vcr_PageTitle = "child";
                    pagesreff.Add(thdlevel);   // if parentrecord child found then loop and add in there

                    foreach (var thd in thirdlevel)
                    {
                        thd.vcr_PageTitle = "subchild";
                        pagesreff.Add(thd);   // if parentrecord child found then loop and add in there
                    }
                }
            }

Upvotes: 0

Views: 269

Answers (1)

Kirill Bestemyanov
Kirill Bestemyanov

Reputation: 11964

After ToList(); linq-to-sql go to database and get rows. After that, you have collection of objects and can do what you want with linq to objects:

var filteredList = paages.Where(someFilter);

there will be no new sql requests.

Update Your problem is that you filter in navigation property, so you should load your navigation property with your first query. I'm not sure (linq-to-sql was many years ago:)), but this should help you (I assume that m.Pages is of type Page):

List<PagesRef> paages = (from pagess in pagerepository.GetAllPages()
                  join pagesref in pagerepository.GetAllPageRef()
                  on pagess.int_PageId equals pagesref.int_PageId
                  where (pagess.int_PostStatusId != 3 && pagess.int_OrganizationId == Authorization.OrganizationID)
                  && pagesref.int_PageRefId == pagesref.Pages.PagesRefs.FirstOrDefault(m => m.int_PageId == pagess.int_PageId && m.bit_Active == true && (m.vcr_PageTitle != null && m.vcr_PageTitle != "")).int_PageRefId
                  select pagesref).AssociateWith<Page>.ToList();

Upvotes: 1

Related Questions