how to get Related Records using LINQ

Question

Hello I have this table structure:

**ProductsTable**
ProductID
ProductName

**CategoriesTable**
CategoryID
CategoryName

**ProductCategories**
ProductID
CategoryID

Each Product can belong to multiple categories. Now I need a way to find Related Products. But I want it in this way:

Let's say Product1 belong to Laptop, Accessories. So only those products who belong to both categories (not just one) can be retrieved.

SQL Query will work, however if you can give LINQ it will be best.

Thanks in Advance

Marc V.

Answer 1

Update

Here is my sql solution with setup:

declare @p table(id int, name varchar(10))
declare @c table(id int, name varchar(10))
declare @pc table(pid int, cid int)

insert into @p (id, name) values (1, 'laptop')
insert into @p (id, name) values (2, 'desktop')
insert into @p (id, name) values (3, 'milk')

insert into @c (id, name) values (1, 'computer')
insert into @c (id, name) values (2, 'device')
insert into @c (id, name) values (3, 'food')

insert into @pc (pid, cid) values (1, 1)
insert into @pc (pid, cid) values (1, 2)
--insert into @pc (pid, cid) values (1, 3)
insert into @pc (pid, cid) values (2, 1)
insert into @pc (pid, cid) values (2, 2)
insert into @pc (pid, cid) values (3, 3)


declare @productId int;
set @productId = 1;

select * 
from @p p 
where  
--count of categories that current product shares with the source product 
--should be equal to the number of categories the source product belongs to
(
select count(*) 
from @pc pc 
where pc.pid = p.id
and pc.cid in (
      select cid from @pc pc
      where pc.pid = @productId
  )
) = (select count(*) from @pc pc where pc.pid = @productId)
and
p.id <> @productId