Linq select with join

Question

I have a basic selection task to be carried out

var query = db.Candidate.Where(.....

but I want the where to be on another table called Tag

in sql it would be

select * from Candidate
join Tag on Tag.candidateId = Candidate.tagId and Tag.tagId = 7

Thus getting all Candidates with tag 7

Im trying to do this directly with a Where, is this possible or do I need to use Linq to SQL.

Directly its not possible. You first need to join table and then fetch data from that joint. — Dhrumil, May 26 '15 at 11:03
http://stackoverflow.com/questions/9720225/how-to-perform-join-between-multiple-tables-in-linq-lambda — Somnath Kharat, May 26 '15 at 11:05
@CodeCaster - Sorry if I sounded unclear, but I meant the same thing as Yuval's answer. — Dhrumil, May 26 '15 at 11:06
@Harvey sure, that's a way, but it's not impossible. See my answer. — CodeCaster, May 26 '15 at 11:08
@CodeCaster - Yeah. Yet still, you will have to perform one or the other type of inclusion/join before putting where. OP was directly going for where so that's what I replied is not possible. — Dhrumil, May 26 '15 at 11:10

score 2 · Answer 1 · answered May 26 '15 at 11:05

2

You can use a Join in LINQ to do that:

var candidates = from candidate in db.Candidates
                 join on tag in db.Tags
                 where candidate.CandidateId == tag.TagId
                 select candidate

answered May 26 '15 at 11:05

Yuval Itzchakov

146,575
32
257
321

score 2 · Answer 2 · answered May 26 '15 at 11:08

2

Try doing it with .Join as shown below :-

var query = db.Candidate.Join(db.Tags, r => r.tagId , p => p.tagId , (r,p) => new{r.Name});

answered May 26 '15 at 11:08

Neel

11,625
3
43
61

CodeCaster · Accepted Answer · 2015-05-26T11:28:43.623

1

You didn't show the relations, but it must be possible to do something like:

var query = db.Candidate
              .Include(c => c.Tags)
              .Where(c => c.Tags.Any(t => t.ID == 7))

Just query the candidates where a candidate has any tag that has the ID you're looking for. This will be written out more or less as the SQL you show.

You can omit the Include() if you have lazy loading enabled.

edited May 26 '15 at 11:28

answered May 26 '15 at 11:06

CodeCaster

147,647
23
218
272

Linq select with join

3 Answers3