CODEWITHSUNDEEP
c#linq-to-sql
The Muffin Man
The Muffin Man

Reputation: 20004

Help with multi table linq query

I have a database set up like so: alt text

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

Upvotes: 0

Views: 124

Answers (3)

slash shogdhe
slash shogdhe

Reputation: 4187

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

Upvotes: 1

user372724
user372724

Reputation:

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

Upvotes: 0

Matthew Whited
Matthew Whited

Reputation: 22433

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();

Upvotes: 3

Related Questions