CODEWITHSUNDEEP
c#linq-to-sql
Al-Mustapha
Al-Mustapha

Reputation: 29

Selecting Specific Child Records In Linq TO SQL C#

I have a table say Category with the following fields:

cat_id, cat_name and Cat_desc

I also have another table product with the following fields:

pro_id, cat_id, pro_name and pro_desc, is_finished

Ordinarily, if I select a category with Linq to sql, it returns the category with all the associated products.

But I want to select a category but the products returned should be only product with is_finished value that is true.

any suggestion/code sample will be appreciated.

Upvotes: 1

Views: 186

Answers (2)

StriplingWarrior
StriplingWarrior

Reputation: 156728

This is probably the wisest way to do what you're asking:

var query = from product in products
            select new {
                product, 
                finishedCategories = product.categories.Where(c => c.is_finished)
            };

This creates an anonymous type that has the data you're looking for. Note that if you access .product.categories you'll still get all of that product's categories (lazily-loaded). But if you use .finishedCategories you'll just get the categories that were finished.

Upvotes: 0

ElGavilan
ElGavilan

Reputation: 6924

You need to join the tables:

select * from product p
left join Category c on c.cat_id = p.cat_id
where c.cat_name = 'yourcategory' and p.is_finished = 1

In linq to SQL:

var query = from product in products
            join category in categories on category.cat_id = product.cat_id
            select new { product.is_finished = true, category.cat_name = "yourcategory" };

Upvotes: 1

Related Questions