Nested select group by linq statement

Question

I have spent days (maybe weeks) trying to resolve this issue. I have two tables in which I am trying to organize my data into the following hierarchy. Here is an example of what this should like:

<ul><strong>PageTitle</strong>

<li>Category A</li>
<li>Category B</li>
<li>Category C</li>

I have my query running successfully in LinQPad, but I can't make it work in MVC. Here is the code I am using in MVC.

public IEnumerable<wl> List()
{
    IEnumerable<wl> wlTest;
    using (LibEntities _libEntity = new LibEntities())  {  
             wlTest = (from p in table1
                       join c in table2 on p.PK equals c.FK
                       group p by new { c.title } into g
                       select new wl
                        {
                          TitleName = g.Key.title,
                          Category = from p in g
                                     group p by g.cat_id into d
                                     select new wl{d.Key.cat_id}
                      }).ToList();

            return wlTest;

    }
}

Here is wl class info:

public class wl
{
    public string TitleName { get; set; }
    public string Category { get; set; }
}

I keep getting

Cannot initialize type 'LibraryProject.Model.wl' with a collection initializer because it does not implement 'System.Collections.IEnumerable'.

Okay, here's a very similar query in LinqPad.

from f_a_p in Find_article_progs
join f_a in Find_articles on f_a_p.Prog_num equals f_a.DbProg 
where f_a_p.Parent_id == 183
group f_a by new {f_a_p.Prog_title} into d
select new {
    d.Key.Prog_title, 
    Links = from f_a in d
            group f_a by new {f_a.DbTitle} into c
            select new {
                c.Key.DbTitle
            }
}

Can anyone help me with this?

Answer 1

This is the problem:

select new wl{d.Key.cat_id}

It's not clear what you actually intended to do here (the meaningless type names really don't help) but you might want something like:

select new wl { CategoryId = d.Key.cat_id }

That's now an object initializer rather than a collection initializer.

It seems unlikely that you actually want new wl at all, mind you - given that you're already creating a wl. Likewise unless your Category property is actually a collection, you probably want to get a single entry from the group rather than all of a query's results.

If you could improve your naming and give more details about what you're trying to do (and your data hierarchy) we may be able to help you more.

(The query you've given won't with LINQPad either, by the way...)

Answer 2

in here don't type g.Key.title_name because g.Key is already your title name

TitleName = g.Key.title_name  // wrong
TitleName = g.Key // correct

And when selecting your categories I think you want to do something like this:

Categories = (from c in g 
               where c.TitleName == g.Key 
                 select c).ToList();

I don't know much about your entity, and your class names are killing me. So I can't give your more advice unless you provide me more information about your entities.