Entity Framework Query for inner join

Question

What would be the query for:

select s.* from Service s 
inner join ServiceAssignment sa on sa.ServiceId = s.Id
where  sa.LocationId = 1

in entity framework?

This is what I wrote:

 var serv = (from s in db.Services
                join sl in Location on s.id equals sl.id
                where sl.id = s.id
                select s).ToList();

but it's wrong. Can some one guide me to the path?

Look there : http://stackoverflow.com/questions/37324/what-is-the-syntax-for-an-inner-join-in-linq-to-sql — Lucien Baron, Apr 15 '13 at 21:58
You are using assignment `=` instead of comparison `==` in `where` clause. Also you don't need that if you already joined on that fields. — Sergey Berezovskiy, Apr 15 '13 at 22:06
Isn't `where sl.id = s.id` redundant with your join condition? — cp.engr, Oct 06 '15 at 22:08

Sergey Berezovskiy · Accepted Answer · 2014-03-10T15:04:02.637

109

from s in db.Services
join sa in db.ServiceAssignments on s.Id equals sa.ServiceId
where sa.LocationId == 1
select s

Where db is your DbContext. Generated query will look like (sample for EF6):

SELECT [Extent1].[Id] AS [Id]
       -- other fields from Services table
FROM [dbo].[Services] AS [Extent1]
INNER JOIN [dbo].[ServiceAssignments] AS [Extent2]
    ON [Extent1].[Id] = [Extent2].[ServiceId]
WHERE [Extent2].[LocationId] = 1

edited Mar 10 '14 at 15:04

answered Apr 15 '13 at 22:04

Sergey Berezovskiy

232,247
41
429
459

3

Isn't that a left outer join? – Jonathan Wood Aug 29 '13 at 22:55
3

@JonathanWood No, that is inner join. Left outer join will be generated by group join. – Sergey Berezovskiy Mar 10 '14 at 14:56
Could we use from s in db.Services join sa in db.ServiceAssignments on s.Id equals sa.ServiceId **|| s.Id equals null** where sa.LocationId == 1 select s – r.hamd Sep 23 '15 at 12:54
Many thanks... I've used `List items = (..your code..).toList();` – Nov 24 '15 at 12:38
6

@JoSmo if you have a navigation property (from example below) you can use Method syntax no problem. But if not, it would look like this (pretty hairy): `db.Services.Join(db.ServiceAssignments, s=>s.Id, sa=>sa.ServiceId, (s,sa)=> new{service = s, alert=sa}).Where(ssa=>ssa.alert.LocationId ==1).Select(ssa=>ssa.service);` – Michael Blackburn Apr 21 '16 at 15:29

Michael Blackburn · Answer 2 · 2016-07-05T19:48:47.657

73

In case anyone's interested in the Method syntax, if you have a navigation property, it's way easy:

db.Services.Where(s=>s.ServiceAssignment.LocationId == 1);

If you don't, unless there's some Join() override I'm unaware of, I think it looks pretty gnarly (and I'm a Method syntax purist):

db.Services.Join(db.ServiceAssignments, 
     s => s.Id,
     sa => sa.ServiceId, 
     (s, sa) => new {service = s, asgnmt = sa})
.Where(ssa => ssa.asgnmt.LocationId == 1)
.Select(ssa => ssa.service);

edited Jul 05 '16 at 19:48

answered Apr 21 '16 at 15:34

Michael Blackburn

3,161
1
25
18

This wouldn't work if the FK to ServiceAssignment happened to be nullable, would it? – Cardin Jul 20 '16 at 03:30
3

@Cardin in that case I would suggest the C#6 ?. operator. If you don't have that, just check for null before using the navigation property. Generally, you don't add a bunch of defensive code in examples, so as not to confuse the primary point. If the FK is nullable, it would look like this: (C#6) `db.Services.Where(s=>s?.ServiceAssignment.LocationId == 1);` or like this in C#5: `db.Services.Where(s=>s.ServiceAssignment != null && s.ServiceAssignment.LocationId == 1);` – Michael Blackburn Jul 25 '16 at 18:10
1

@MichaelBlackburnThat's true! It is more understandable. Thanks for the clarification. :) – Cardin Jul 26 '16 at 01:15

The Lonely Coder · Answer 3 · 2014-09-17T16:26:22.340

8

You could use a navigation property if its available. It produces an inner join in the SQL.

from s in db.Services
where s.ServiceAssignment.LocationId == 1
select s

edited Sep 17 '14 at 16:26

answered Sep 17 '14 at 16:07

The Lonely Coder

613
9
12

Entity Framework Query for inner join

3 Answers3

Linked