CODEWITHSUNDEEP
c#linq
Ammar Khan
Ammar Khan

Reputation: 2585

How to get list of record within a range using LINQ

I am trying to integrate Google Map on my page, but before I integrate, I need to extract data from the database in a way that, I need to extract the lat and lon of a place then I want to extract those places also which are nearby the extracted place lat/lon.

I have a query which need mofiication

public static List<PlaceMap> GetPlace(this IEnumerable<Place> places, string hotelName, ref DataEntities ctx)
{             
   var place= ctx.places
       .Where(h=>h.HotelName==hotelName)
       .Select(s => new PlaceMap
       {
           PlaceName= s.PlaceName,
           Latitude = s.Latitude,
           Longitude = s.Longitude,
           NearByPlaces = ????

        }).SingleOrDefault();
    return place;
}

Upvotes: 0

Views: 501

Answers (2)

Kris Vandermotten
Kris Vandermotten

Reputation: 10201

NearByPlaces = (from p in ctx.places
                where p.Latitude > s.Latitude - latrange && 
                      p.Latitude < s.Latitude + latrange &&
                      p.Longitude > s.Longitude - longrange && 
                      p.Longitude < S.Longitude + longrange
                select /* whatever */).ToArray()

That being said, such a query can be rather inefficient if you have a lot of places (e.g. millions) and you don't have a spatial index on your database. Obviously, in order to have a spatial index, you need to use spatial types to store the location, not separate Latitude and Longitude columns. In SQL Server, you would use a SqlGeography column. That could be queried with the entity framework as a DbGeography.

This should answer your actual question, but it will of course not solve the other issues with your method, see the comments others have posted to your question.

Upvotes: 0

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

Check out DBGeography of Entity Framework:

public static List<PlaceMap> GetPlace(this IEnumerable<Place> places, string hotelName, ref DataEntities ctx)
{
    var place= ctx.places
        .Where(h=>h.HotelName==hotelName)
        .Select(s => new PlaceMap
        {
            PlaceName= s.PlaceName,
            Latitude = s.Latitude,
            Longitude = s.Longitude,
            NearByPlaces = ctx.places.Where
            (
                x=>
                DbGeography.FromText("POINT(" + s.Longitude + " " + s.Latitude + ")")  
                .Distance
                (
                    DbGeography.FromText("POINT(" + x.Longitude + " " + x.Latitude + ")")
                ) < YourRadiousInMeters
            )                  
        }).SingleOrDefault();
    return place;
}

Upvotes: 2

Related Questions