1

I'm generating Entity SQL to provide dynamic query support in my application. I have however been unable to find how one is able to specify spatial conditions in Entity SQL using Entity Framework 5.

A query using Linq to Entities against a model with an entity containing a spatial field like:

var a = new Model1Container();
var b = from c in a.Entity1
        where c.Loc.Intersects(System.Data.Spatial.DbGeography.FromText("POINT (43 -73)"))
        select c;

generates the SQL that one would expect for SQL Server 2012, such as:

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[Loc] AS [Loc]
FROM [dbo].[Entity1] AS [Extent1]
WHERE ([Extent1].[Loc].STIntersects(geography::Parse(N'POINT (43 -73)'))) = 1

How does one rewrite the above Linq to Entities query using ESQL? Or is this impossible?

apaderno
  • 28,547
  • 16
  • 75
  • 90
Hernan Gatta
  • 13
  • 4

2 Answers2

3

We have a set of canonical functions that can be used in EntitySQL to operate with spatial types, which include instance construction from well-known text. For instance, this is valid EntitySQL for constructing a point:

GeometryFromText('POINT (43 -73)')

The full set of spatial canonical functions is declared in the SpatialEdmFunctions class, but this class is used for creating DbExpression trees programmatically, so the reference documentation available for this class is not in the most appropriate form for EntitySQL usage. I will follow up with our documentation team to see if there is a better resource available or if we need to add these to the EntitySQL documentation.

divega
  • 6,320
  • 1
  • 31
  • 31
  • 1
    I apologize for my late answer, but I have only now been able to dig into the code that deals with this. I was able to pull the sources for EF off CodePlex and examine the class you mentioned. I found all the functions that I needed for my application in there, so thanks! It would most definitely be great if these functions were documented. In my case, I'm building a dynamic query compiler that translates a hierarchical data structure that I let my users manipulate into E-SQL. Thus, being able to look at everything that is available to me in E-SQL on the documentation would be/is great. – Hernan Gatta May 24 '13 at 19:56
  • 1
    The documentation is currently found [here](http://msdn.microsoft.com/en-us/library/hh749497.aspx), which is great. – Hernan Gatta Jun 05 '13 at 19:28
0
var a = new YourDbContext();
var b = a.Entity1.Where(c=>c.Loc.Intersects(System.Data.Spatial.DbGeography.FromText("POINT (43 -73)"));
Robert McKee
  • 21,305
  • 1
  • 43
  • 57
  • Thank you for your answer, although the idea was to write the equivalent of that query in Entity SQL manually instead of going through LINQ expressions. – Hernan Gatta May 24 '13 at 20:01