Clever Geek Handbook

Linq as in

HI,

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

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

in plain text sql it would be something like this:

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

Question:

How can I do this using Linq without operator Join of course

+1


source to share


3 answers


this might be the solution;

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


or

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


source


See this question:



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

+2


source


The Contains method is found in Linq converted to IN operator like:

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

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

is converted to an sql clause like: .. WHERE a.City in ('London', 'Paris', 'Madrid')

where a is the address table alias after joining the Person table.

edit:

you can write one query without a subquery, but this will most likely be converted to a join:

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

Anything translates to Exists ()

+1


source






More articles:


All Articles