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