I am trying to sort the venues according to nearby location buti am getting LINQ error here. I have a method name GetDistanceM that takes 4 arguments lat1, lat2, long1, long2 and return distance between two geocordinates. Here iam trying to sort venues based on Distance.
public async Task<IActionResult> Explore(string sortOrder){
var venues = from v in _context.Venues where v.IsApproved select new VenueModel{
Category = v.Category,
Price = v.Price,
Name = v.Name,
Id = v.Id,
City = v.City,
GetDistance = GetDistanceM(v.Latitude, v.Longitude, 27.692387, 85.318110)
};
switch (sortOrder)
{
case "Price":
venues = venues.OrderBy(s => s.Price);
break;
case "Name":
venues = venues.OrderBy(s => s.Name);
break;
case "Location":
venues = venues.OrderBy(x => x.GetDistance);
break;
default:
venues = venues.OrderByDescending(s => s.Name);
break;
}
This works perfect when i sort with "Name" and Price. But it doesnt work with Location. I am getting InvalidOperationException.
The cause of the problem is that _context.Venues is an IQueryable<...>
You have to be aware of the difference between IQueryables and IEnumerables.
IEnumerable
An object that implements IEnumerable represents a sequence of objects. It holds everything to get the first element of the sequence, and once you've got an element you can ask for the next element, as long as there is a next element.
On the lowest level this is done by asking for the Enumerator, using GetEnumerator, and then repeatedly calling MoveNext() until there are no more elements. Every time you've got an element using MoveNext(), this element can be accessed using property Current.
On a higher level you enumerate this sequence using foreach or one of the LINQ functions that don't return IEnumerable, like ToList(), FirstOrDefault(), Count(), Any(), etc. Deep inside these functions will call GetEnumerator() and MoveNext / Current
IQueryable
An object that implements IQueryable does not represent the sequence itself, it represents a query: the potential to get an IEnumerable. For this, the IQueryable holds an Expression and a Provider. The Expression holds the query in some generic format, the Provider knows who must execute the query (usually a database management system) and the language that this DBMS uses (usually SQL)
Concatenating LINQ statements of an IQueryable does not execute the query, it will only change the Expression.
An IQueryable also implements IEnumerable. As soon as you start enumerating, the Expression is sent to the Provider, who will translate the Expression into the SQL and execute the query at the DBMS. The returned data is represented as an IEnumerable, so you can call MoveNext / Current (either explicitly or by using ToList(), Any(), FirstOrDefault(), etc).
But what does this have to do with my question?
The problem is that the Provider of your IQueryable does not know the function GetDistance(), hence it can't translate it into SQL. In fact, there are several standard LINQ functions that can't be translated into SQL. See Supported and Unsupported LINQ Methods (LINQ to Entities).
Not so good solution: Use AsEnumerable
The easiest way to solve this, is to select the data that is input for your GetDistance formula, then call AsEnumerable, which will execute your query. Your local process knows GetDistance, so you can call it after AsEnumerable:
var venues = context.Venues.Where(venue => venue.IsApproved)
.Select(venue => new
{
Category = v.Category,
Price = v.Price,
Name = v.Name,
Id = v.Id,
City = v.City,
// can't call GetDistance yet, select the input parameters:
Latitude = v.Latitude,
Longitude= v.Longitude,
})
// execute the query:
.AsEnumerable()
// now you can call GetDistance:
.Select(v => new VenueModel
{
Category = v.Category,
Price = v.Price,
Name = v.Name,
Id = v.Id,
City = v.City,
Distance = GetDistanceM(v.Latitude, v.Longitude, 27.692387, 85.318110)
})
.OrderBy(venueModel => venuModel.Distance);
Better Solution: create an extension method for IQueryable
Although this solves your problem, it has the disadvantage that your fetched data has to be sorted by your local process. The DBMS is much more optimized to sort your data than your local process.
Alas, you didn't tell us what GetDistance() does. It seems it takes the Latitude and Longitude from the Venue and returns the distance to a certain point using a formula.
What you could do is translate the function GetDistance into an extension method of IQueryable that takes an IQueryable as input, and returns the Selected data. If you are not familiar with extension methods, see extension methods demystified
public IQueryable<VenueModel> SelectVenueModels(this IQueryable<Venue> venues,
double X, double Y)
{
return venues.Select(venue => new VenueModel()
{
Category = v.Category,
Price = v.Price,
Name = v.Name,
Id = v.Id,
City = v.City,
// Calculate the distance as you would do in GetDistance:
Distance = Math.Sqrt(
Math.Pow( (v.Latitude-X), 2) + Math.Pow( (v.Longitude-Y), 2) ),
});
Usage:
var venues = _context.Venues.Where(venue => venue.IsApproved)
.SelectVenueModels(27.692387, 85.318110)
.OrderBy(venueModel => ...);
Note: it might be that the entity framework type that you use does not know how to translate functions like SQRT and POW, in that case you'll have to translate them into other methods. For entity framework you can use class SQLFunctions
Extension method to Sort
Now that we've mastered extension methods, why not create an extension method that inputs an IQueryable<VenueModel> and a sortOrder string, and returns the properly ordered IQueryable<VenueModel>?
public IOrderedQueryable<VenueModel> OrderBy(this IQueryable<VenueModel> venueModels,
string sortOrder)
{
switch (sortOrder)
{
case "Price":
venues = venues.OrderBy(s => s.Price);
break;
case "Name":
venues = venues.OrderBy(s => s.Name);
break;
case "Location":
venues = venues.OrderBy(x => x.Distance);
break;
default:
venues = venues.OrderByDescending(s => s.Name);
break;
}
}
// TODO: improve code, so it is case insensitive
Usage:
string sortOrder = ...;
var venues = _context.Venues.Where(venue => venue.IsApproved)
.SelectVenueModels(27.692387, 85.318110)
.OrderBy(sortOrder);
But why would this work?
The difference is that this OrderBy does not execute the query, it only changes the expression. The input of the expression is a VenueModel, no GetDistance is called anymore, only functions that your Provider knows how to translate into SQL are used