Using Entity Framework to fetch all results within a distance of N

Question

I've created a column in my table with the Geography type using entity framework code first. I'm calling a linq statement to fetch the first result from my table that compares the location I pass in with all locations in the table. What I want is to just return all rows that are within 20 miles or less of my location.

Here is what I have so far.

var myLocation = DbGeography.FromText("POINT(-122.453164 37.723057)");

using (var repo = new YogaSpaceRepository())
        {
            YogaSpace space = repo.FirstWithinDistance(myLocation);
        } 

public YogaSpace FirstWithinDistance(DbGeography myLocation)
    {
       var yogaSpace = (from u in context.YogaSpaces
            orderby u.Location.Distance(myLocation)
            select u).FirstOrDefault();

        return yogaSpace;
    }

what is the type of Distance? – Ehsan Sajjad Jan 22 '15 at 06:40 — Ehsan Sajjad, Jan 22 '15 at 06:40

score 2 · Answer 1 · edited Apr 05 '17 at 06:26

Here is what I have and it seems to work. I just return an IQueryable and set the meters to miles.

public IQueryable<YogaSpace> AllWithinDistance(DbGeography myLocation)
    {
        var yogaSpace = (from u in context.YogaSpaces
                         orderby u.Location.Distance(myLocation)
                         where u.Location.Distance(myLocation) <= 32187 //20 miles = 32187 meters, 10 miles = 16093 meters, 5 miles = 8047 meters
                         select u);

        return yogaSpace;
    }

The sql generated:

 declare @p3 sys.geography
set @p3=convert(sys.geography,0xE6100000010C6A12BC218DDC42405FEE93A3009D5EC0)
declare @p4 sys.geography
set @p4=convert(sys.geography,0xE6100000010C6A12BC218DDC42405FEE93A3009D5EC0)
exec sp_executesql N'SELECT 
    [Project1].[YogaSpaceId] AS [YogaSpaceId], 
    [Project1].[DateCreated] AS [DateCreated], 
    [Project1].[Name] AS [Name], 
    [Project1].[Rating] AS [Rating], 
    [Project1].[Status] AS [Status], 
    [Project1].[OwnerId] AS [OwnerId], 
    [Project1].[Location] AS [Location], 
    [Project1].[Thumbnail] AS [Thumbnail]
    FROM ( SELECT 
        [Extent1].[Location].STDistance(@p__linq__0) AS [C1], 
        [Extent1].[YogaSpaceId] AS [YogaSpaceId], 
        [Extent1].[DateCreated] AS [DateCreated], 
        [Extent1].[Name] AS [Name], 
        [Extent1].[Rating] AS [Rating], 
        [Extent1].[Status] AS [Status], 
        [Extent1].[OwnerId] AS [OwnerId], 
        [Extent1].[Location] AS [Location], 
        [Extent1].[Thumbnail] AS [Thumbnail]
        FROM [dbo].[YogaSpaces] AS [Extent1]
        WHERE ([Extent1].[Location].STDistance(@p__linq__1)) <= cast(32187 as float(53))
    )  AS [Project1]
    ORDER BY [Project1].[C1] ASC',N'@p__linq__1 [geography],@p__linq__0 [geography]',@p__linq__1=@p3,@p__linq__0=@p4

its long and it won't fit into the comment section. I will post it as another answer. — chuckd, Jan 22 '15 at 08:27
I see.. perhaps you should have editted this answer instead. But it answers my query as to whether it would be run on SqlServer or not. It is because the `Distance()` method is translated into `STDistance()`. — kjbartel, Jan 22 '15 at 08:47

score 1 · Answer 2 · answered Nov 03 '20 at 05:42

In your Nuget Package Manage: Install-Package NetTopologySuite
In your database add a new column - ALTER TABLE TableName ADD Location geography;
Add new property in your c# model - public Point Location { get; set; } so now each time you need to set this property you MUST set the SRID (spatial reference system id) to 4326, which is the spatial reference system used by Google maps (WGS84).
```
User u = new User
{
  Username = "user1",
  FirstName = "Maureen",
  LastName = "Miles",
  Location = new Point(16.3738, 48.2082) 
  {
    SRID = 4326
  }
};
```

We also need to use UseNetTopologySuite option in our EF context. Our context will look like this:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
  optionsBuilder.UseSqlServer(
   @"my connection string",
   x => x.UseNetTopologySuite());
}

5. Now it is ready, Find all the users within 700km:

   Point myLocation = new Point(13.4050, 52.5200)
   {
      SRID = 4326
   };

   var ctx = new MyContext();
   double radiusMeters = 700000;
   User[] usersWithinRadius = ctx.Users.Where(x => x.Location.Distance(myLocation) <= radiusMeters).ToArray();

score -1 · Answer 3 · answered Jan 22 '15 at 08:29

    declare @p3 sys.geography
set @p3=convert(sys.geography,0xE6100000010C6A12BC218DDC42405FEE93A3009D5EC0)
declare @p4 sys.geography
set @p4=convert(sys.geography,0xE6100000010C6A12BC218DDC42405FEE93A3009D5EC0)
exec sp_executesql N'SELECT 
    [Project1].[YogaSpaceId] AS [YogaSpaceId], 
    [Project1].[DateCreated] AS [DateCreated], 
    [Project1].[Name] AS [Name], 
    [Project1].[Rating] AS [Rating], 
    [Project1].[Status] AS [Status], 
    [Project1].[OwnerId] AS [OwnerId], 
    [Project1].[Location] AS [Location], 
    [Project1].[Thumbnail] AS [Thumbnail]
    FROM ( SELECT 
        [Extent1].[Location].STDistance(@p__linq__0) AS [C1], 
        [Extent1].[YogaSpaceId] AS [YogaSpaceId], 
        [Extent1].[DateCreated] AS [DateCreated], 
        [Extent1].[Name] AS [Name], 
        [Extent1].[Rating] AS [Rating], 
        [Extent1].[Status] AS [Status], 
        [Extent1].[OwnerId] AS [OwnerId], 
        [Extent1].[Location] AS [Location], 
        [Extent1].[Thumbnail] AS [Thumbnail]
        FROM [dbo].[YogaSpaces] AS [Extent1]
        WHERE ([Extent1].[Location].STDistance(@p__linq__1)) <= cast(32187 as float(53))
    )  AS [Project1]
    ORDER BY [Project1].[C1] ASC',N'@p__linq__1 [geography],@p__linq__0 [geography]',@p__linq__1=@p3,@p__linq__0=@p4

fhnaseer · Answer 4 · 2015-01-22T07:26:42.070

-2

public IList<YogaSpace> FirstWithinDistance(DbGeography myLocation, double N)
{
    var spaces = context.YogaSpaces();
    return spaces .Where(y => CalculateDistance(yogaSpace.Location, myLocation) <= N).ToList();
}

Here CalculateDistance returns distance between two points,

edited Jan 22 '15 at 07:26

answered Jan 22 '15 at 07:00

fhnaseer

7,159
16
60
112

I don't want the firstordefault, I want all results reurned within the distance N. I also read that 'N' is in meters, is there a way to use miles? Or should I just convert (in code) 1609 meters = 1 mile? – chuckd Jan 22 '15 at 07:03
Here is how I've changed my query var yogaSpace = (from u in context.YogaSpaces orderby u.Location.Distance(myLocation) where u.Location.Distance(myLocation) > 1 select u); – chuckd Jan 22 '15 at 07:04
Updated answer. Instead of using FirstOrDefault use Where. It will return list of yoga spaces that are in that range. To convert meter to mile you have to do that manually. If you know in what units values are written in database and what input you are giving as N, then that should not be a problem, – fhnaseer Jan 22 '15 at 07:08
I'm pretty sure this won't work... or if it does it will be really slow as you'd get all records and do the `.Where()` in your program not on the server. – kjbartel Jan 22 '15 at 07:24
Linq does this for you. You can also try getting all entries first. Save it to some variable and then do a linq query on it. And about the working, if you know about the units then it will work fine, – fhnaseer Jan 22 '15 at 07:28

Using Entity Framework to fetch all results within a distance of N

4 Answers4