LINQ: How to make a nested select statement conditional?

Question

I am working in an ASP.NET project with EF and I have some trouble with finding a correct query. I need to work without lazy-loading.

I have following data structure: A Module contains pages. A Page contains PageItems. A PageItem contains an Item. An Item can contains other Items.

Leaving out the recursive aspect of Items containing Items at first, I need a query to feed the structure of a single module into a treeview.

What I want is :

The Module with the given ID
- Including all pages
  - Including all PageItems
    - Including all Items with ParentItem_ID == 0
      - Including all ChildItems

I started with this:

return base._entities.Modules
            .Include(m => m.Paginas
                .Select(p => p.PaginaItems
                    .Select(pi => pi.Item)
                    .Select(i => i.ChildItems)))
            .Where(m => m.Module_ID == id)
            .FirstOrDefault();

This query works, but it ignores the item-hierarchy and displays all items as direct child of a page. What would actually need is something like this:

return base._entities.Modules
           .Include(m => m.Paginas
               .Select(p => p.PaginaItems
                   .Select(pi => pi.Item)
                   .Where(i => i.I_ParentItem_ID == 0)
                   .Select(i => i.ChildItems)))
           .Where(m => m.Module_ID == id)
           .FirstOrDefault();

But it does not work.

I an relatively new to LINQ, and any help would really be appreciated.

In this case you should use explicit joins.

Gert Arnold
– Gert Arnold

2012-04-26 12:23:41 +00:00
Commented Apr 26, 2012 at 12:23 — Gert Arnold
– Gert Arnold, Commented Apr 26, 2012 at 12:23

Gert Arnold · Accepted Answer · 2012-04-26 17:45:40Z

2

As you need a condition in a nested collection you should use join, like:

(from mod in c.Modules
join pag in c.Paginas on mod.Module_Id equals pag.Module_Id
join pi in c.PaginaItems on pag.Pagina_Id equals pi.Pagina_Id
join item in c.Items.Where(i => i.I_ParentItem_ID == 0) on pi.PaginaItem_Id
    equals item.PaginaItem_Id
join ci in c.ChildItems on item.I_ParentItem_ID equals ci.I_ParentItem_ID
select mod).FirstOrDefault()

I abbreviated base._entities to c (for context) and made some assumptions about Id names and possibly overlooked the odd syntax error, but this should be a good start.

answered Apr 26, 2012 at 17:45

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ckonig Over a year ago

WOW that was really helpful. Now I've got it. Although I had to alter the query a bit: instead of select mod I had to write select new{mod,pag,pi,item} and then return qry.mod, otherwise the navigation properties of Module were null.

Collectives™ on Stack Overflow

LINQ: How to make a nested select statement conditional?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related