CODEWITHSUNDEEP
sqlsql-serverlinq
DevAssasin
DevAssasin

Reputation: 31

SQL equivalent on a LINQ query with ANY as a predicate

I have a LINQ query such as:

var myOffices=return table1
                .Where(o => o.department.Any());

table1 has a virtual department property which is of type ICollection<department>.

I tried the below SQL but it gives me incorrect records:

SELECT * FROM table1 T
WHERE EXISTS (SELECT 1 FROM department D)

table1 and department are linked via a foreign key reference

please advise.

Upvotes: 0

Views: 667

Answers (1)

Pac0
Pac0

Reputation: 23149

You are missing the fact that you want elements in table1 where some department are linked to these particular table1 element.

Suppose in department the foreign key for table1 is fk_id_table1 and the primary key for table1 is id_table1 , the query should be : 

SELECT * FROM table1 T 
WHERE EXISTS (
    SELECT 1 FROM department D 
    WHERE D.fk_id_table1 = T.id_table1    -- This is the missing condition
)

Upvotes: 3

Related Questions