DbGeography Intersects

Question

I'm using DbGeography with Entity Framework 6 using this model:

public class County
{
    public int Id { get; set; }

    public string Name { get; set; }

    public string Code { get; set; }

    public DbGeography Area { get; set; }

}

I'm then trying to execute the Intersects method like below:

public County GetCurrentCounty(double latitude, double longitude)
{  
    var point = DbGeography.PointFromText(
        "POINT("
        + longitude.ToString(CultureInfo.InvariantCulture) + " "
        + latitude.ToString(CultureInfo.InvariantCulture) + ")",
        4326);

    var area = db.Counties.FirstOrDefault(x =>
        point.Intersects(x.Area));


    var area1 = db.Counties.FirstOrDefault(x =>
        x.Area.Intersects(point));
}

However the query created looks like this for both methods. Is there something I can do to not select the entire table and perform a query in the database instead?

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[Code] AS [Code], 
    [Extent1].[Area] AS [Area]
    FROM [Election].[County] AS [Extent1]

There's something wrong with this query. `FirstOrDefault` should be translated to `SELECT TOP (1)...` Did you use `Database.Log` handler to log this query? — arekzyla, Mar 22 '18 at 20:43

score 0 · Accepted Answer · answered Apr 18 '18 at 13:05

Can't say what the error was but the second time it showed the correct values. T-SQL generated by Entity Framework:

SELECT TOP (1) 
[Extent1].[Id] AS [Id], 
[Extent1].[Name] AS [Name], 
[Extent1].[Code] AS [Code], 
[Extent1].[Area] AS [Area]
FROM [Election].[County] AS [Extent1]
WHERE ([Extent1].[Area].STIntersects(@p__linq__0)) = 1


-- p__linq__0: 'POINT (10.0000000 32.0000000)' (Type = Object)

Can be tested like this manually:

declare @p__linq__0 varchar(max)
set @p__linq__0 = 'POINT (10.0000000 32.0000000)' 

SELECT TOP (1) 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[Code] AS [Code], 
    [Extent1].[Area] AS [Area]
    FROM [Election].[County] AS [Extent1]
    WHERE ([Extent1].[Area].STIntersects(@p__linq__0)) = 1

More information can be found here:

https://learn.microsoft.com/en-us/sql/t-sql/spatial-geometry/stintersects-geometry-data-type

DbGeography Intersects

1 Answers1