CODEWITHSUNDEEP
c#entity-framework
TheWebs
TheWebs

Reputation: 12923

Entity Framework Query for inner join

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?

Upvotes: 85

Views: 239646

Answers (3)

Michael Blackburn
Michael Blackburn

Reputation: 3229

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

Upvotes: 76

The Lonely Coder
The Lonely Coder

Reputation: 633

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

Upvotes: 8

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236208

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

Upvotes: 110

Related Questions