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?

Answer 1

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 

Answer 2

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