CODEWITHSUNDEEP
c#postgresqlentity-frameworklinqone-to-many
Daniel Kohli
Daniel Kohli

Reputation: 28

Querying number of relationship from the one side of one-to-many in C# EF Core/LINQ

So I have a really complex query where I would like to return the top 10 products within a specified radius with the highest order volume. So far, I have a working query to get all products within a certain radius (using a raw SQL query). The problem now is sorting by the number of orders. Below is the query. I can provide more details as needed. Thanks in advance!

var prods = _context.Products
                    .FromSql($"SELECT p.* FROM (SELECT s.\"storefrontID\", z.radius, z.distance_unit * DEGREES(ACOS(COS(RADIANS(z.latpoint)) * COS(RADIANS(a.lat)) * COS(RADIANS(z.longpoint - a.lon)) + SIN(RADIANS(z.latpoint)) * SIN(RADIANS(a.lat)))) AS distance FROM public.\"Addresses\" AS a, public.\"Storefronts\" AS s JOIN (SELECT {lat} AS latpoint, {lon} AS longpoint, {radius} AS radius, {distance_unit} AS distance_unit) AS z ON 1=1 WHERE s.\"addressID\" = a.\"addressID\" AND a.lat BETWEEN z.latpoint - (z.radius / z.distance_unit) AND z.latpoint  + (z.radius / z.distance_unit) AND a.lon BETWEEN z.longpoint - (z.radius / (z.distance_unit * COS(RADIANS(z.latpoint)))) AND z.longpoint + (z.radius / (z.distance_unit * COS(RADIANS(z.latpoint))))) AS a, public.\"Storefronts\" AS s WHERE distance <= radius AND s.\"storefrontID\" = a.\"storefrontID\"")
                    .Include(p => p.orderProductConnectors.Count())
                    .OrderByDescending(p => p.orderProductConnectors)
                    .AsNoTracking()
                    .ToList();

Of note, I'm using postgreSQL as my DB solution.

Upvotes: 0

Views: 59

Answers (1)

NetMage
NetMage

Reputation: 26936

Guessing:

var prods = _context.Products
                    .FromSql($"SELECT p.* FROM (SELECT s.\"storefrontID\", z.radius, z.distance_unit * DEGREES(ACOS(COS(RADIANS(z.latpoint)) * COS(RADIANS(a.lat)) * COS(RADIANS(z.longpoint - a.lon)) + SIN(RADIANS(z.latpoint)) * SIN(RADIANS(a.lat)))) AS distance FROM public.\"Addresses\" AS a, public.\"Storefronts\" AS s JOIN (SELECT {lat} AS latpoint, {lon} AS longpoint, {radius} AS radius, {distance_unit} AS distance_unit) AS z ON 1=1 WHERE s.\"addressID\" = a.\"addressID\" AND a.lat BETWEEN z.latpoint - (z.radius / z.distance_unit) AND z.latpoint  + (z.radius / z.distance_unit) AND a.lon BETWEEN z.longpoint - (z.radius / (z.distance_unit * COS(RADIANS(z.latpoint)))) AND z.longpoint + (z.radius / (z.distance_unit * COS(RADIANS(z.latpoint))))) AS a, public.\"Storefronts\" AS s WHERE distance <= radius AND s.\"storefrontID\" = a.\"storefrontID\"")
                    .OrderByDescending(p => p.orderProductConnectors.Count())
                    .AsNoTracking()
                    .ToList();

Upvotes: 1

Related Questions