Help with multi table linq query

I have a database set up like so:

Lets say that I have a product group with 开发者_C百科id of 12. How do I get the CategoryID for this particular Product Group? Would I have to do a join?

Think of it in parts...

How would you do this in SQL?

SELECT DISTINCT sc.CategoryID
FROM SubCategory sc
WHERE sc.SubCategory IN (
    SELEcT DISTINCT scpg.SubCategoryID
    FROM SubCategoryProductGroups scpg
    WHERE scpg.ProductGroupID = 12)

To do this in LINQ2SQL it would be something like this...

using (var db = new MyDataContext()) {
    var query = (from sc in db.SubCategories
                 where (from scpg in db.SubCategoryProductGroups
                        where scpg.ProductGroupID == 12
                        select scgp.SubCategoryID).Distinct().Contains(sc.SubCategoryID)
                 select sc.CategoryID).Distinct();
}

... You could also use joins ...

SQL...

SELECT DISTINCT sc.CategoryID
FROM SubCategory sc
JOIN SubCategoryProductGroups scpg ON sc.SubCategoryID = scpg.SubCategoryID
WHERE scpg.ProductGroupID = 12

LINQ2SQL...

var query = (from sc in db.SubCategories
             join scpg in db.SubCategoryProductGroups 
                     on sc.SubCategoryID equals scpg.SubCategoryID
             where scpg.ProductGroupID == 12
             select sc.CategoryID).Distinct();

... If you LINQ model knows of the relationship you could probably do this ...

var query = (from sc in db.SubCategories
             where sc.SubCategoryProductGroups
                     .Any(s=>s.ProductGroupID == 12)
             select sc.CategoryID).Distinct();

dc.subcatogorysproductsgroups.where(o=> o.productgroupid == 12).select(j=> o.subcatogory.catogory);

one thing is if u r using linq to sql then there is no need of using join if the database if fully normilized. and before using this code ensure the dc.defferedloadingenabled is set to be true. or use DataLoadOptions and Dc.loadPropery

Hope this helps

var result = from sc in SubCategory
    join scpg in SubCategoryProductGroups
    on sc.SubCategoryID equals scpg.SubCategoryID
    where scpg.ProductsGroupID =12
    select sc.CategoryID