c# - EF LINQ spatial query times out using Contains() method in SQL Server -

i have spatial query (sql server 2012) using linq (lambda syntax) times out after approximately 30secs, , cannot work out why. have checked indexes, , run tuning advisor indexing appears in order. trying polygon contains given 2d point.

    pointxy point = new pointxy(x, y);     parcel parcel = db.parcels.where(p => p.geom.contains(point)).firstordefault(); 

i have tried rearranging query using within() method, same result:

    parcel parcel = db.parcels.where(p => point.within(p.geom)).firstordefault(); 

however, if request count of number of matching polygons, (correct) result returned immediately.

    int count = db.parcels.where(p => point.within(p.geom)).count(); 

i cannot work out how resolve this, , wondering if there fundamentally wrong approach.

you currently cannot specify index hints entity framework (ef6), never force use of spatial index. possibly there way change linq query trick ef using spatial index, not find way. solution bypass ef , use raw sql via sqlquery().

string sql = "select * [dbo].[parcels] with(index([idx_spatial])) (geometry::point(@x, @y, 2193).stwithin([shape])) = 1"; var args = new dbparameter[] {      new sqlparameter { parametername = "x", value = point.xcoordinate },      new sqlparameter { parametername = "y", value = point.ycoordinate },  };  parcel parcel = db.database.sqlquery<parcel>(sql, args).firstordefault(); 

i not find way pass geometry type parameter, passed x/y coordinates individually.