linq operators like in

Question

HI,

I have 3 tables: Clips, Books and relationships between ClipBook

Problem is: i need get book that has bookID=4 with some clips i mean many-to-many

in simple text sql it will be something like this:

select * from Clips where clipID in (select clipID from ClipBook where bookID=4)

Question is:

How can i do this with Linq without operator Join of course

Answer 1

See this question:

How can you handle an IN sub-query with LINQ to SQL?

Answer 2

the Contains method is in Linq converted into the IN operator, example:

String[] cities = {"London", "Paris", "Madrid"};

from p in context.Person
where cities.Contains(p.Adress.City)
select p

is translated into a sql clause like: .. WHERE a.City in ('London', 'Paris', 'Madrid') where a is the Address table alias after the join with the Person table.

edit:

you could write a single query without a subquery, but this will be converted to a join most probably:

var clips = (from c in context.ClipBooks
             where c.BookID == 4
             select c.Clip).Distinct();

or

var clips =  from c in context.Clip
             where c.ClicBooks.Any( cb => cb.BookID == 4)
             select c

Any is translated in Exists()

Answer 3

this could be a solution;

from cb in ClipBooks
where cb.BookID == 4
select cb.Clip;

or

ClipBooks.Where(cb => cb.BookId == 4).Select(cb => cb.Clip);