CODEWITHSUNDEEP
c#asp.netlinq
k80sg
k80sg

Reputation: 2473

Linq query help

I am trying to convert this short SQL statement into linq but I am facing some difficulties, here's my latest attempt, apparently it has an error:

SQL:

select ProductID from products where
categoryID in (select categoryID from categories_sub 
               where categoryID='15' and category_sub_name = 'chiffon')

Linq:

'15' and 'chiffon' is replaced by parameter 'cID' and 'subCatID' here: 

IQueryable<Categories_Sub > cat = (from c in db.Categories_Sub
                             where c.CategoryID == cID
                              & c.Category_Sub_ID == subCatID
                             select c);   


var subcat = (from c in db.Products
              where cat.Contains(c.ProductID)
              select c);

Upvotes: 1

Views: 77

Answers (3)

Yahia
Yahia

Reputation: 70369

Try

var Result = from p in products 
               from subc in categories_sub 
               where subc.categoryID=15 and 
                     subc.category_sub_name = "chiffon" and 
                     p.categoryID = subc.categoryID 
               select p.ProductID;

Upvotes: 3

Marty Neal
Marty Neal

Reputation: 9523

I've reformatted the sql to talk about it in a little more depth

1 select ProductID
2 from products 
3 where categoryID in
4   (select categoryID 
5        from categories_sub 
6        where categoryID='15' and 
7              category_sub_name = 'chiffon')

Line 3 supposes you're looking for products with a specific categoryID

But your subquery starting on line 4, only returns a list of 0 or more categoryIDs that must = '15'

In which case, why not

1 select ProductID
2 from products 
3 where categoryID = '15'

I know this isn't an answer but it was too big to fit inside a comment. I'll try to answer later.

Upvotes: 0

Dan Abramov
Dan Abramov

Reputation: 268215

Minimally, your code won't compile because there is an extra space before closing generic bracket.
The first line should start with IQueryable<Categories_Sub>.

Then, there is not enough information in your question. What LINQ provider are you using? What makes you think there is an error in query?

Upvotes: 0

Related Questions