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?

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
– Sergey Berezovskiy, Commented Apr 15, 2013 at 22:06
Isn't where sl.id = s.id redundant with your join condition? — cp.engr
– cp.engr, Commented Oct 6, 2015 at 22:08

Sergey Berezovskiy · Accepted Answer · 2014-03-10 15:04:02Z

110

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, 2014 at 15:04

answered Apr 15, 2013 at 22:04

Sergey Berezovskiy

237k44 gold badges441 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jonathan Wood Over a year ago

Isn't that a left outer join?

Sergey Berezovskiy Over a year ago

@JonathanWood No, that is inner join. Left outer join will be generated by group join.

r.hamd Over a year ago

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

user4227915 Over a year ago

Many thanks... I've used List<Item> items = (..your code..).toList();

Michael Blackburn Over a year ago

@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 · Accepted Answer · 2016-07-05 19:48:47Z

76

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 5, 2016 at 19:48

answered Apr 21, 2016 at 15:34

Michael Blackburn

3,2771 gold badge28 silver badges18 bronze badges

3 Comments

Cardin Over a year ago

This wouldn't work if the FK to ServiceAssignment happened to be nullable, would it?

Michael Blackburn Over a year ago

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

Cardin Over a year ago

@MichaelBlackburnThat's true! It is more understandable. Thanks for the clarification. :)

The Lonely Coder · Accepted Answer · 2014-09-17 16:26:22Z

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, 2014 at 16:26

answered Sep 17, 2014 at 16:07

The Lonely Coder

6339 silver badges12 bronze badges

Collectives™ on Stack Overflow

Entity Framework Query for inner join

3 Answers 3

5 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related