LINQ giving difference results to SQL

Question

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.

have you tried to see [actual SQL query](https://stackoverflow.com/questions/37527783/get-sql-code-from-an-entity-framework-core-iqueryablet)? — Guru Stron, May 05 '20 at 15:55
Your join for the `QSRAnswers` does not have both comparisons. Try `new{qsr.QsrId, sfm.FieldNo} equals new{qsra.QsrId, qsra.FieldNo}` Also why are you doing a group by in the LINQ when you don't do one in the SQL? — juharr, May 05 '20 at 15:55
@GuruStron yes, I am for some reason unable to bring this in correctly into the solution — Simon Price, May 05 '20 at 16:01
@juharr i tried the group by because I was getting the more results than I was expecting. Where in the code should I be trying the new'd instance like your comment? — Simon Price, May 05 '20 at 16:02
Instead of `join qsra in QSRAnswers on qsr.QsrId equals qsra.QsrId` you should do `join qsra in QSRAnswers on new{qsr.QsrId, sfm.FieldNo} equals new{qsra.QsrId, qsra.FieldNo}` That's how you can do compound FK joins in Linq. — juharr, May 05 '20 at 16:04
@juharr I get CS1941 The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'Join'. when trying that — Simon Price, May 05 '20 at 16:08
What are the types of the `FieldNo` properties in the `QSRAnswers` and `SectionFieldMappings` tables? I'm guessing one is nullable and the other is not. — juharr, May 05 '20 at 16:10
Its ok, looking at the objects someone has coded one as a string, and one as an int... — Simon Price, May 05 '20 at 16:11
That likely means they are different types in the DB as well. I guess you can try doing `ToString` on the one that's an `int`, but honestly they should be the same type across the board if they are really the same data. — juharr, May 05 '20 at 16:14

score 1 · Accepted Answer · answered May 05 '20 at 16:07

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.

I have a type issue and its not converting, but see that this should work if i had the right types — Simon Price, May 05 '20 at 16:55

LINQ giving difference results to SQL

1 Answers1