Full text + spatial search with Entity Framework 4

Question

Let's say I have a SQL 2005 database with a table called Restaurants. The Restaurants table has the following columns:

• RestaurantId
• Name
• Latitude
• Longitude

I want to let users search for restaurants by name and/or address. How do I write the LINQ query to support this? I need to be able to support the possibility that the user doesn't enter in a name or address, just the name, just the address, or both name and address.

My initial idea was to write a stored procedure to calculate the distance between two lat/long pairs and a table value function for calling FREETEXTTABLE and using some conditional Join calls on my query. However, it appears that Entity Framework 4 doesn't support table value functions.

Answer 1

You certainly can write a proc which returns entity types. Indeed, in EF 1 that was the only option for procs. The proc returns a set of values, not a table, but I can't see that you actually need this.

You can also do free-form T-SQL in EF 4 using Context.ExecuteStoreQuery.

Answer 2

You cannot write any LINQ that supports geospatial queries at this point of time - be it EF or LinqToSql. This is because there is no LINQ syntax which can handle the special ST<whatever> spatial syntax that exists in SQL Server 2008. (eg. STIntersects(..))

You will need to write a Stored Procedure which you can then get access to via EF.

If you wish to return a Sql GEOGRAPHY field in a result, you will need to return a VARBINARY(MAX) i think as the equivalent field type for the C# code.

Hope This Helps.