Add Max and Average in linq query

user_14084060 0
Viewed 60

I've got an sql query below:

SELECT MAX(r.Suburb), AVG(s.Longitude) AS Longitude, AVG(s.Latitude) AS Latitude 
From CafeAddress r inner join Cafe s on s.CafeId = r.CafeId
WHERE r.Region = 'Sydney'

which outputs:

Bankstown   174.759541622222    -36.8552809611111

The query should be getting all suburbs in the region e.g. Sydney is the region and suburbs would include Bankstown etc..

How would I add max and average in my linq query below? (region id is a user input (string))

var region = from cafeAddress in _context.CafeAddress
                         join cafe in _context.Cafe on cafeAddress.CafeId equals cafe.CafeId
                         where cafeAddress.StateProvinceRegion == regionId
                         select new { StateProvinceRegion = cafeAddress.Suburb.Max(), cafe.Latitude,  cafe.Longitude };

I'm only able to do it through using a group by but I'm trying to avoid that.

sql linq c#
1 Answers

I think this would be getting fairly close:

var query =
    from cafeAddress in _context.CafeAddress
    join cafe in _context.Cafe on cafeAddress.CafeId equals cafe.CafeId
    where cafeAddress.StateProvinceRegion == regionId
    orderby cafeAddress.Suburb descending
    group new
    {
        cafeAddress.Suburb,
        cafe.Latitude,
        cafe.Longitude
    } by cafeAddress.StateProvinceRegion into g
    select new
    {
        StateProvinceRegion = g.First().Suburb,
        Latitude = g.Average(x => x.Latitude),
        Longitude = g.Average(x => x.Longitude),
    };

Or this slight variant:

var query =
    from cafeAddress in _context.CafeAddress
    join cafe in _context.Cafe on cafeAddress.CafeId equals cafe.CafeId
    where cafeAddress.StateProvinceRegion == regionId
    orderby cafeAddress.Suburb descending
    group new
    {
        cafeAddress.Suburb,
        cafe.Latitude,
        cafe.Longitude
    } by cafeAddress.StateProvinceRegion into gs
    from g in gs.Take(1)
    select new
    {
        StateProvinceRegion = g.Suburb,
        Latitude = gs.Average(x => x.Latitude),
        Longitude = gs.Average(x => x.Longitude),
    };
user_2597690
Related