I could find how convert a SQL sentence to LINQ using join, group and select from multiple tables RRS feed

  • Question

  • Hi community,

    I have this SQL sentence:

    SELECT p.CountryCode, p.CityName, MIN(p.ZipCode), MIN (pc.PostalCodeId)
    FROM PostalCodes p
    INNER JOIN PostalCodes pc ON p.CountryCode = pc.CountryCode AND p.ZipCode = pc.ZipCode
    GROUP BY p.CountryCode, p.CityName

    I'm trying to convert it to LINQ but I don't know how to be able to get MIN (pc.PostalCodeId) because pc is not availbale in the select.

    var x = from p in list
                        join pc in list on
                            new { country = p.CountryCode, zip = p.ZipCode } equals new { country = pc.CountryCode, zip = pc.ZipCode }
                        group p by new { pc.CountryCode, p.CityName }
                        into r
                        select new ODCityNames
                            CountryCode = r.Key.CountryCode,
                            CityName = r.Key.CityName,
                            ZipCode = r.Min(m => m.ZipCode),
                            PostalCodeId =  /** MIN (pc.PostalCodeId) **/

    Andres Duque

    Wednesday, September 23, 2020 3:01 AM

All replies

  • Try ‘PostalCodeId = r.Min(m => m.PostalCodeId)’.

    Wednesday, September 23, 2020 8:28 AM
  • Why not also use r.Min(m => m.PostalCodeId) like what you done with ZipCode? Your group by statement have glued both entities into r.
    Wednesday, September 23, 2020 8:29 AM