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

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

Answer 1

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

Answer 2

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.