CODEWITHSUNDEEP
c#linq
Simon Price
Simon Price

Reputation: 3261

LINQ grp.Max(...) not giving just max value

I am working with LINQ \ EF and I am trying to replace a SQL Stored Proc using Dapper with Entity Framework.

I am 99% there with a particular aspect of the call, however I am getting 2 too many results because the select is not getting just the max value.

The SQL 

SELECT
    MAX(QS.QSVersion) AS LatestVersion,
    QS.QSNo           AS VerQSno
FROM
    [forms].QS
INNER JOIN
    [forms].QSCollectionMappings qsc
        ON qsc.QSNo = QS.QSNo
WHERE
    qsc.QSCollectionTypeId = @QSCollectionTypeId
    AND QS.StatusId = 2
GROUP BY
    QS.QSNo;

The LINQ 

ar join = (from qs in QS
            join qscm in QSCollectionMappings on 
                    new { QuestionNumber = qs.QSNo, Version = qs.QSVersion } equals 
                    new {QuestionNumber = qscm.QSNo, Version = qscm.QS.QSVersion} 
            where qscm.QSCollectionTypeId == collectionId && qs.StatusId == 2
            group qs by new
            {
                QuestionNumber = qs.QSNo,
                QuestionVersion = qs.QSVersion,

            } into grp
            select new {
                QuestionNumber = grp.Key.QuestionNumber,
                QuestionVersion = grp.Max(x => x.QSVersion)
            });

The results 

 The results 

     QSNo   |Version

|34 |-| 1.0 |
|38 |-| 1.0 |  // Should not show
|276|-| 1.0 |  // Should not show
|38 |-| 2.0 |
|276|-| 2.0 |

I would be grateful if I could be advised on what I have done wrong here so that I can fix the issue.

Thanks

Simon

Upvotes: 0

Views: 58

Answers (1)

Haytam
Haytam

Reputation: 4733

You are grouping by both the QSNo and the QSVersion, technically, each unique combination will be on a seperate group, which means the results are correct.

What you should do instead is to group on the QSNo alone, and then use Max on the versions.

I can't test the code, but this snippet should work:

    var join = (from qs in QS
        join qscm in QSCollectionMappings on 
                new { QuestionNumber = qs.QSNo, Version = qs.QSVersion } equals 
                new {QuestionNumber = qscm.QSNo, Version = qscm.QS.QSVersion} 
        where qscm.QSCollectionTypeId == 3 && qs.StatusId == 2
        group qs by qs.QSNo into grp
        select new {
            QuestionNumber = grp.Key,
            QuestionVersion = grp.Max(p => p.QSVersion)
        });

Upvotes: 2

Related Questions