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
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); 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()
