Linq-to-SQL and SQL Geography type

Question

Is there any way to use LINQ-to-SQL in Visual Studio 2012 to map to an SQL geography type? I need to be able to insert SQL geography types into a database and also query for SQL geography types. I already referred to this link, but it doesn't seem to address my question.

Answer 1

LINQ-to-SQL does not natively support SQL geography types as you have already discvered. To work around this use a combination of views, stored procedures and computed columns in the database. In brief:

• Include or add a computed column to the table(s) that converts the geography data type to varbinary. LINQ-to-SQL can map this computed column to a byte array.
• Create view for each table that will have geo data, but do not include the unsupported geography data type column.
• Use the views in your LINQ-to-SQL queries instead of the original tables.
• To insert or update geography data, use stored procedures or raw SQL queries to perform those actions.

Here is an (untested!) illustration of the steps:

Create a table: e.g.

CREATE TABLE Locations (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(50) NOT NULL,
    Location GEOGRAPHY NOT NULL,
    LocationVarbinary AS CAST(Location AS VARBINARY(MAX))
)

nb: if the table you want already exists add the geography and computed columns using native SQL.

Create views based on the wanted tables but do not include the unsupported geography data type columns: e.g.

CREATE VIEW LocationsView AS
SELECT Id, Name, Location.Lat AS Latitude, Location.Long AS Longitude
FROM Locations

Use these views in your LINQ-to-SQL queries instead of the tables: e.g.

var db = new MyDataContext();
var locations = from l in db.LocationsView
                select l;

To insert or update geography data, use stored procedures or raw SQL queries to perform the inserts or updates. e.g.

CREATE PROCEDURE InsertLocation
    @Name NVARCHAR(50),
    @Latitude FLOAT,
    @Longitude FLOAT
AS
BEGIN
    DECLARE @Location GEOGRAPHY = geography::Point(@Latitude, @Longitude, 4326)
    INSERT INTO Locations (Name, Location)
    VALUES (@Name, @Location)
END

Call the stored procedures from your C# code: e.g.

var db = new MyDataContext();
db.InsertLocation("New Location", 47.65100, -122.34900);

To use the geo data (for example to get locations within a distance) use a combination of the SqlFunctions class and the STDistance method from SQL Server’s geography data type. e.g.

var db = new MyDataContext();
var point = DbGeography.FromText("POINT(-122.34900 47.65100)");
var distance = 1000; // meters

var locations = from l in db.LocationsView
                where SqlFunctions.DataLength(l.LocationVarbinary) > 0
                let location = DbGeography.FromBinary(l.LocationVarbinary)
                where location.Distance(point) <= distance
                select l;

This query should return all rows from the LocationsView where the Location column is within 1000 meters of the specified point

nb: DbGeography.FromBinary cannot handle NULL values so SqlFunctions.DataLength(l.LocationVarbinary) > 0 filters out 0 length LocationVarbinary rows (i.e. where the computed source Location column is null).