Nested Join Data Inside Linq Result

Question

I'm trying to create a WebAPI that returns Json data for use by jQuery. It's fairly simple data... a category and under each category a list of links to websites, each with a title and URL. I've mocked it up with static data and it works great. Now, I'm trying to move on to live data. I have a table with a category id and category title. Another table hold a link id, category id, link title and link URL.

This represents how I need the link data to be nested with the category only listed once:

LegalLinks[] legalLinks = new LegalLinks[]
{
    new LegalLinks {
        Id = 1,
        Title = "Test Category One.1",
        Links = new LinkDetails[]
        {
            new LinkDetails { Title = "Link One", Link = "http://www.google.com/" },
            new LinkDetails { Title = "Link Two", Link = "http://www.google.com/" }
        }
    },
    new LegalLinks {
        Id = 2,
        Title = "Test Category Two.2",
        Links = new LinkDetails[]
        {
            new LinkDetails { Title = "Link Three", Link = "http://www.google.com/" },
            new LinkDetails { Title = "Link Four", Link = "http://www.google.com/" },
            new LinkDetails { Title = "Link Five", Link = "http://www.google.com/" }
        }
    }
};

I am able to successfully return just the category data. Where I fail is the nested link data. Here are my models:

public class LegalLinks_categoriesDTO
{
    public int CategoryId { get; set; }
    public string Category { get; set; }

    public virtual IEnumerable<LegalLinks_linksDTO> Links { get; set; }
}

public class LegalLinks_linksDTO
{
    public string Title { get; set; }

    public string LinkURL { get; set; }
}

And my API controller:

    private LegalLinksContext db = new LegalLinksContext();

    public IQueryable<LegalLinks_categoriesDTO> GetLegalLinks()
    {
        var legalLinks = from category in db.Categories
                         join link in db.Links on category.Id equals link.CategoryId into categoryLinks
                         from l in categoryLinks.DefaultIfEmpty()
                         orderby category.Title
                         select new LegalLinks_categoriesDTO()
                         {
                             CategoryId = category.Id,
                             Category = category.Title,
                             Links = new LegalLinks_linksDTO { Title = l.Title, LinkURL = l.LinkURL }
                         };

        return legalLinks;
    }

I get an error about not being able to convert type LegalLinks_linksDTO to IEnumerable LegalLinks_linksDTO. I've tried different variations of that line for the past 6 hours, reading everything published about Linq and joins. How do I get the join data properly nested?

---

Edit 1

Ok, I found a working solution, using a sub select. Not sure if this is the most efficient method, but it works and returns the data in the way I need it.

    private IQueryable<LegalLinks_categoriesDTO> GetLegalLinks()
    {
        var legalLinks = from category in db.Categories
                         orderby category.Title
                         select new LegalLinks_categoriesDTO()
                         {
                             CategoryId = category.Id,
                             Category = category.Title,
                             Links = from a in db.Links
                                     where a.CategoryId == category.Id
                                     select new LegalLinks_LinkDetails
                                     {
                                         Title = a.Title,
                                         LinkURL = a.LinkURL
                                     }
                         };

        return legalLinks;
    }

Answer 1

I removed from l in categoryLinks.DefaultIfEmpty() statement, and collected categories and relative group of links in an anonymous object, then from that object, I projected the instances of the _categoriesDto, and into its initializer list, I made the instance of _linksDTO for each item of the links group belonging to a given category.

var legalLinks = from category in db.Categories
                 join link in db.Links on category.Id equals link.CategoryId into categoryLinks
                 orderby category.Title
                 select new { category, links = categoryLinks.DefaultIfEmpty() } into dto 
                 select new LegalLinks_categoriesDTO()
                 {
                   CategoryId = dto.category.Id,
                   Category = dto.category.Title,
                   Links = dto.links.First() != null ? dto.links.Select(lnk => new LegalLinks_linksDTO { Title = lnk.Title, LinkURL = lnk.LinkUrl }) : null
                 };

This works also for categories without links, the property links will be null (but you can eventually assign it a default value)..

Answer 2

The issue is that you are trying to assign a LegalLinks_linksDTO to an IEnumerable<LegalLinks_linksDTO>. This cannot work.

What you can do however is keep the grouping without using DefaultIfEmpty(). Example:

var legalLinks = from category in db.Categories
                 join link in db.Links on category.Id equals link.CategoryId into categoryLinks
                 orderby category.Title
                 select new LegalLinks_categoriesDTO()
                 {
                     CategoryId = category.Id,
                     Category = category.Title,
                     Links = categoryLinks.Select(l => new LegalLinks_linksDTO { Title = l.Title, LinkURL = l.LinkURL })
                 };

return legalLinks;