I have two tables, and I would like to get only the data from table A by filtering through the related table B. I would like to know how to write this query with Linq to Sql in C #. I've written the example below on Sql Server to make understanding easier.
Declare @tabela_a as table (
ID_A int, --PK
nome varchar(20)
)
Declare @tabela_b as table (
ID_B int, -- PK
ID_A int, -- FK
nome varchar(20)
)
insert into @tabela_a values (1, 'AAA')
insert into @tabela_a values (2, 'BBB')
insert into @tabela_a values (3, 'CCC')
insert into @tabela_b values (1, 1, 'A1')
insert into @tabela_b values (2, 1, 'A2')
insert into @tabela_b values (3, 1, 'A3')
insert into @tabela_b values (4, 2, 'B1')
insert into @tabela_b values (5, 2, 'B2')
insert into @tabela_b values (6, 2, 'B3')
-- Exemplo de Query que eu gostaria que seja executada
SELECT * FROM @TABELA_A A
WHERE EXISTS (
SELECT NULL FROM @TABELA_B B
WHERE ID_A = A.ID_A
AND B.NOME IN ('A1', 'A2', 'B3')
)