LINQ, how to specify to include items from List<>

Ok, I am trying to find get all subcategories from my database (using query result shaping) that belong to the category that I supply. My class SubCategory includes a List<> of Categories.

The problem is that in the linq statement, g is referring to SubCategory (which in the end contains Categories<>). So the statement below is not allowed.

How do I change the Linq statement to generate the correct SQL query to include all SubCategories that contain the matching Category.

 public class SubCategory

    {
        public int SubCategoryId { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public List<Article> Articles { get; set; }
        public List<Category> Categories {开发者_如何学C get; set; }
    }

//incorrect code below:
var SubCategories = storeDB.SubCategories.Include("Categories").Single(g => g.Name == category);

This worked for me (maybe too simple):

var Category = storeDB.Categories.Include("SubCategories").Single(c => c.Name == category);
return Category.SubCategories;

I find it a bit confusing that each SubCategory can belong to more than one Category - have you got this relationship the right way around?

Regardless, I think its probably more readable if you change the select to work on Categories first - i.e. something like:

var subCatQuery   = from cat in storeDB.Categories
                    where cat.Name == category
                    select cat.SubCategories;

which you can then execute to get your IEnumerable<>:

var subCategories = subCatQuery.ToList();

I find that much more readable/understandable.

(I also find the query syntax easier to read here than the fluent style)

my preferred answer would be to use a linq join. if there aren't but 2 data sets you can use the linq operations .join and .groupjoin vs a from statement. this would be my approach.

    Dictionary<MainQuery, List<subQuery>> Query = 
    dbStore.List<MainQuery>.include("IncludeAllObjects")
    .groupjoin(db.List<SubTable>.Include("moreSubQueryTables"), 
    mainQueryA=>mainQueryA.PropToJoinOn,
    subQueryB => sunQueryB.PropToJoinOn,
    ((main, sub)=> new {main, sub})
    .ToDictionary(x=>x.main, x=>x.sub.ToList());