Skip to content
Advertisement

Join multiple columns from the same table using Linq

I would like to convert following sql query into Linq to SQL

select distinct r.CompanyLogo, j.JobName, j.JobId, ur.UserId, r.JobSeekerID
, ur.FirstName, j.JobType, j.JobCareerLevel, j.JobLocation
from UserInterest m
join job j on m.FunctionalId = j.FunctionId or m.Careerlevel = j.CarrerLevelId or m.SalId = j.SalaryRangeId
join UserRegistration ur on j.UserId = ur.UserId
join EmplrRegistration r on j.UserId = r.JobSeekerID
where m.Status = 1 and m.UserId = 1

going through this I have so far tried following which didn’t work out

    var list = (from m in entities.UserInterests
                from j in entities.Jobs
                where m.FunctionalId == j.FunctionId || m.SalId == j.SalaryRangeId || m.Careerlevel == j.CarrerLevelId
                && m.Status == true && m.UserId == 1
                join ur in entities.UserRegistrations on m.UserId equals ur.UserId
                join r in entities.EmplrRegistrations on m.UserId equals r.JobSeekerID
                select new { r.CompanyLogo, j.JobName, j.JobId, ur.UserId, r.JobSeekerID
                , ur.FirstName, j.JobType, j.JobCareerLevel, j.JobLocation }).Distinct().ToList();

Edit: following query is being generated against Svyatoslav Danyliv answer which is returning 7 rows instead of 6

SELECT 1 AS [C1], [Extent4].[CompanyLogo] AS [CompanyLogo], [Extent2].[JobName] AS [JobName]
,[Extent2].[JobId] AS [JobId], [Extent3].[UserId] AS [UserId]
,[Extent4].[JobSeekerID] AS [JobSeekerID], [Extent3].[FirstName] AS [FirstName]
,[Extent2].[JobType] AS [JobType], [Extent2].[JobCareerLevel] AS [JobCareerLevel]
,[Extent2].[JobLocation] AS [JobLocation]
FROM [dbo].[UserInterest] AS [Extent1]
INNER JOIN [dbo].[Job] AS [Extent2] ON ([Extent1].[FunctionalId] = [Extent2].[FunctionId])
OR (([Extent1].[FunctionalId] IS NULL) AND ([Extent2].[FunctionId] IS NULL))
OR ([Extent1].[Careerlevel] = [Extent2].[CarrerLevelId])
OR (([Extent1].[Careerlevel] IS NULL) AND ([Extent2].[CarrerLevelId] IS NULL))
OR ([Extent1].[SalId] = [Extent2].[SalaryRangeId])
OR (([Extent1].[SalId] IS NULL)
AND ([Extent2].[SalaryRangeId] IS NULL))
INNER JOIN [dbo].[UserRegistration] AS [Extent3] ON [Extent2].[UserId] = [Extent3].[UserId]
INNER JOIN [dbo].[EmplrRegistration] AS [Extent4] ON [Extent2].[UserId] = [Extent4].[JobSeekerID]
WHERE (1 = [Extent1].[Status]) AND (1 = [Extent1].[UserId])

Advertisement

Answer

Join which contains not just AND expressions is possible via from x in entities.Where(x => ..). You have did that partially and made mistake in where condition.

Corrected query, looks the same as original SQL

var query = 
   from m in entities.UserInterests
   from j in entities.Jobs.Where(j => 
      m.FunctionalId != null && m.FunctionalId == j.FunctionId || 
      m.Careerlevel != null && m.Careerlevel == j.CarrerLevelId || 
      m.SalId != null && m.SalId == j.SalaryRangeId)
   join ur in entities.UserRegistrations on j.UserId equals ur.UserId
   join r in entities.EmplrRegistrations on j.UserId equals r.JobSeekerID
   where m.Status == true && m.UserId == 1
   select new { r.CompanyLogo, j.JobName, j.JobId, ur.UserId, r.JobSeekerID
                , ur.FirstName, j.JobType, j.JobCareerLevel, j.JobLocation };

var list = query.Distinct().ToList();
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement