CODEWITHSUNDEEP
c#linq
Simon Price
Simon Price

Reputation: 3261

LINQ giving difference results to SQL

I have been tasked with replacing SQL query with a LINQ query and in the main I get the data that I would expect, however I think there is a join that has gone wrong somewhere and I'm not sure how or where as most of the time I avoid EF where I can in favour of dapper.

The SQL I have been given 

    SELECT
            SFM.FieldId,
            QSRA.Answer,
            SFM.FieldNo
    FROM
            [forms].QS
        INNER JOIN
            [sessions].QSR
                ON QSR.QSNo = QS.QSNo
        INNER JOIN
            (
                SELECT
                        MAX(QS.QSVersion) AS LatestVersion
                FROM
                        [forms].QS
                    INNER JOIN
                        [sessions].QSR
                            ON QSR.QSNo = QS.QSNo
                WHERE
                        QSR.QsrId = @QSRID
                        AND QS.StatusId = 2
            )                            AS QSLatestVer
                ON QS.QSVersion = QSLatestVer.LatestVersion
        INNER JOIN
            [forms].QSSectionMappings    QSM
                ON QSM.QSId = QS.QSId
        INNER JOIN
            [forms].SectionFieldMappings SFM
                ON SFM.SectionId = QSM.SectionId
        INNER JOIN
            [sessions].QSRAnswers        QSRA
                ON (
                       QSRA.QsrId = QSR.QsrId
                       AND QSRA.FieldNo = SFM.FieldNo
                   )
    WHERE
            QSR.QsrId = @QSRID;

The LINQ I have used to replace it with and then am going to look at refining. 

var results = (from qs in QS

               join qsr in QSRs on qs.QSNo equals qsr.QSNo
               join qsm in QSSectionMappings on qs.QSId equals qsm.QSId
               join sfm in SectionFieldMappings on qsm.SectionId equals sfm.SectionId
               join qsra in QSRAnswers on qsr.QsrId equals qsra.QsrId
               join sub in (from subQs in QS
                     join subQsr in QSRs on subQs.QSNo equals subQsr.QSNo
                     where subQs.StatusId == 2 && subQsr.QsrId == Guid.Parse(qsrIdGuid)
                     select subQs.QSVersion
                 ) on qs.QSVersion equals sub
               where qsr.QsrId == Guid.Parse(qsrIdGuid)
               group new
               {
                   FieldId = sfm.FieldId,
                   Answer = qsra.Answer,
                   FieldNo = decimal.Parse(sfm.FieldNo),
               } by new
               {
                   FieldId = sfm.FieldId,
                   Answer = qsra.Answer,
                   FieldNo = sfm.FieldNo
               } into g

                 select new
                 {
                     FieldId = g.Key.FieldId,
                     Answer =  g.Key.Answer,
                     FieldNo = g.Key.FieldNo, 

               }
);

The results I get with the SQL are

FieldId |Answer |FieldNo

40D10975-AF2E-4518-AC35-08D7C70E1BF9 |3/17/2020 12:00:00 AM |1

71A95FD5-08E0-4201-AC36-08D7C70E1BF9 |3/25/2020 12:00:00 AM |2

The results I get with LINQ are

FieldId |Answer |FieldNo

40d10975-af2e-4518-ac35-08d7c70e1bf9 |3/17/2020 12:00:00 AM |1 --Correct

40d10975-af2e-4518-ac35-08d7c70e1bf9 |3/25/2020 12:00:00 AM |1 --Wrong

71a95fd5-08e0-4201-ac36-08d7c70e1bf9 |3/17/2020 12:00:00 AM |2 --Wrong

71a95fd5-08e0-4201-ac36-08d7c70e1bf9 |3/25/2020 12:00:00 AM |2 --Correct

I would appreciate if you could let me know where I am going wrong in the join

The results are the same with the nested select, and the grouping as without the grouping.

Upvotes: 0

Views: 61

Answers (1)

juharr
juharr

Reputation: 32296

In the SQL you have the following for joining the QSRAnwers table

INNER JOIN
    [sessions].QSRAnswers        QSRA
        ON (
               QSRA.QsrId = QSR.QsrId
               AND QSRA.FieldNo = SFM.FieldNo
           )

However in the Linq code you have

join qsra in QSRAnswers on qsr.QsrId equals qsra.QsrId

So you're missing the FieldNo comparison for that join. Just change it to

join qsra in QSRAnswers 
    on new{qsr.QsrId, sfm.FieldNo} equals new{qsra.QsrId, qsra.FieldNo}

To get the same functionality.

Upvotes: 1

Related Questions