locked
convert this sql into linq RRS feed

  • Question

  • User318173063 posted

    I have this SQL statement that i am converting in to Linq. I am not getting the same results when i run the linq.

    In the SQL query this line

    AND 
                             dbo.PositionAppliedFor.PositionAppliedForID = dbo.InterviewResult.PositionAppliedForID

    makes the difference.

    I am not able to incorporate that line in linq

    here is the SQL query.

    SELECT        dbo.JobApplicant.FirstName, dbo.JobApplicant.LastName
    FROM            dbo.JobApplicant INNER JOIN
                             dbo.PositionAppliedFor ON dbo.JobApplicant.JobApplicantID = dbo.PositionAppliedFor.JobApplicantID INNER JOIN
                             dbo.job_posting ON dbo.PositionAppliedFor.JobPostingID = dbo.job_posting.job_posting_id LEFT OUTER JOIN
                             dbo.InterviewResult ON dbo.JobApplicant.JobApplicantID = dbo.InterviewResult.JobApplicantID AND 
                             dbo.PositionAppliedFor.PositionAppliedForID = dbo.InterviewResult.PositionAppliedForID

    Here is my LINQ

    var query = from applicant in context.JobApplicants
                            join PositionApplied in context.PositionAppliedFors on applicant.JobApplicantID equals PositionApplied.JobApplicantID
                            join postings in context.job_postings on PositionApplied.JobPostingID equals postings.job_posting_id
                            join interview in context.InterviewResults on applicant.JobApplicantID equals interview.JobApplicantID
                            //&& PositionApplied.PositionAppliedForID == interview.PositionAppliedForID
                            into prods
                            from x in prods.DefaultIfEmpty()  

    Thanks

    Thursday, April 14, 2016 11:07 PM

Answers

  • User1559292362 posted

    Hi sumo55,

    I am not able to incorporate that line in linq

    As the SQL statement you providing, I create a demo for your reference.

     

    var query = from applicant in context.JobApplicants
                                join PositionApplied in context.PositionAppliedFors on applicant.JobApplicantID equals PositionApplied.JobApplicantID
                                join postings in context.job_postings on PositionApplied.JobPostingID equals postings.job_posting_id
                                join interview in context.InterviewResults on new { applicant.JobApplicantID, PositionApplied.PositionAppliedForID } equals new
                                {
                                    interview.JobApplicantID,
                                    interview.PositionAppliedForID
                                }
                                //&& PositionApplied.PositionAppliedForID  interview.PositionAppliedForID 
                                into prods
                                from x in prods.DefaultIfEmpty()
                                select new
                                {
                                    applicant.FirstName,
                                    applicant.LastName
                                };
    

     

    Best regards,

    Cole Wu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 15, 2016 4:22 AM