Entity Framework average with grouping

Question

Trying to add averaging for the following columns: longitude and latitude

This is my SQL query:

SELECT
    ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, 
    r.StateProvinceRegion, 
    AVG(s.Longitude) AS Longitude, 
    AVG(s.Latitude) AS Latitude
FROM
    RestaurantAddress r 
INNER JOIN
    Restaurant s ON s.RestaurantId = r.RestaurantId
GROUP BY
    StateProvinceRegion

Entity Framework code:

public VersionResponse GetRegions()
{
    var regionList = from restaurantAddress in _context.RestaurantAddress
                     join restaurant in _context.Restaurant on restaurantAddress.RestaurantId equals restaurant.RestaurantId
                     select new { restaurantAddress.StateProvinceRegion, 
                                  restaurant.Longitude, restaurant.Latitude, 
                                  average = (new double?[] { restaurant.Longitude, restaurant.Latitude }).Average() 
                                };

    return new VersionResponse()
               {
                    Data = regionList
               };
}

Currently when I try get the average of longitude and latitude, I get a http 500 error saying there was an issue with processing the LINQ expression AsQueryable<Nullable<double>>(new Nullable<double>[]

Is there a way I can add averaging in my query in Entity Framework?

Answer 1

It seems to me, that every Restaurant has exactly one Address, and you want the Average Location of all Restaurants per StateProvinceRegion. If your Restaurant has several Addresses, then the code won't change.

My advice would be to fetch each Restaurant with its Address, and then GroupBy StateProvinceRegion. Use the overload of GroupBy that has a parameter resultSelector.

In the resultSelector of the StateProvinceRegion you calculate the average Longitude / Lattitude.

var result = dbContext.Restaurant.Join(dbContext.Address,

    restaurant => restaurant.RestaurantId, // from every Restaurant take the primary key
    address => address.RestaurantId,       // from every Address take the foreign key
                                           // to the Restaurant

    (restaurant, address) => new           // when they match, use the Restaurant and 
    {                                      // the Address to make one new object
        
        StateProvinceRegion = restaurant.StateProvinceRegion,
        Longitude = address.Longitude,
        Latitude = address.Latitude,
    })

    // Result: per restaurant its StateProvinceRegion and the Longitude and Latitude
    // of the Restaurant's Address

    // from the joinResult make groups that have equal value for StateProvinceRegion
    .GroupBy(joinResult => joinResult.StateProvinceRegion,

        // parameter resultSelector: take the StateProvinceRegion
        // and all Longitudes / Latitudes that have this StateProvinceRegion
        // to make one new object:
        (stateProvinceRegion, joinResultsWithThisStateProvinceRegion) => new
        {
            StateProvinceRegion = stateProvinceRegion,

            Longitude = joinResultsWithThisStateProvinceRegion
                .Select(joinResult => joinResult.Longitude)
                .Average(),

            Latitude = joinResultsWithThisStateProvinceRegion
                .Select(joinResult => joinResult.Latitude)
                .Average(),
        });

Simple comme bonjour!

Answer 2

You need to add a GroupBy LINQ call in..

I generally work with/feel more comfortable with method syntax rather than query syntax so this may be a bit off:

select ( ... your existing query ...)
group by StateProvinceRegion into g
select new {
  StateProvinceRegion = g.Key,
  AvgLat = g.Average(x => x.Latitude),
  AvgLong = g.Average(x => xLongitude)
}

When you group something in LINQ you don't get a group in the SQL sense of the word; SQL takes the detail lines, groups on the key columns specified in the GROUP BY then runs the aggregates specified in the select and returns just the key and the aggregate results. LINQ stops sooner than this and keeps the detail that SQL throws away, but you have to directly process the detail to run the aggregations - all LINQ does is divide up a list of things into a list of lists of things, so your original list of address/lat/long becomes like a Dictionary<(address), List<(address, last, long)>> - that is to say that for each unique address there is an associated list of all the address/lat/long that have that address.

The address is stored in g.Key and the list detail is g. This means you retrieve the address from the key, and you calculate eg the average Latitude with the typical list.Average(element => element.Property) pattern