LINQ to SQL

The first step in using LINQ to SQL — is a creation of the mapper classes which will represent a database tables. Let's take some examples for a database we have.

Working with LINQ to SQL and spatial data

The main goal of current sub topic is to understand, how to work with technology and spatial types of the SQL Server at the time when spatial is not supported by the current LINQ to SQL version.

Lets take a first look at how LINQ to SQL works.

var result = from item in db.Features_Boundaries_Country
             where item.CountryName == "Chad"
             select item.CountryBoundary;

Each LINQ statement is translated into the SQL statement and then is sended to SQL Server. The retrived data (rows) is mapped to objects. LINQ to SQL tracks all changes we made with the program objects and by a command translates and send them back to server.

For example a LINQ statement presented above is translated into the next SQL-statement (we can look on it during debuggin, for example).

select [t0].[CountryBoundary]
from [Features_Boundaries_Country] AS [t0]
where [t0].[CountryName] = @p0

In this statement @p0 will be replaced with the string "Chad".

Now lets take a more interesting task: select boundaries which intersects given bounding box.
We can define a bounding box as a polygon. (I haven't yet a methods for parsing WKT strings, o some more optimized methods, so i define a bounding box with a lot of code.)

GeographicCRS crs = (GeographicCRS)EPSG.Instance.
                                            GetCoordinateReferenceSystem("4326");
List<DirectPosition> points = new List<DirectPosition>();
points.Add(new DirectPosition(new double[2] { -28, 40 }));
points.Add(new DirectPosition(new double[2] { 30, 40 }));
points.Add(new DirectPosition(new double[2] { 30, 5 }));
points.Add(new DirectPosition(new double[2] { -28, 5 }));
points.Add(new DirectPosition(new double[2] { -28, 40 }));
Polygon envelope = new Polygon(new LinearRing(points, crs), crs);
SqlGeography sqlEnvelope = SqlGeography.STPolyFromWKB(new                                                                     SqlBytes(envelope.AsBinary), 4326);

At the next step we will define (not worked) LINQ query.

1. FeaturesDB db = new FeaturesDB(@"...");
2.  
3. var q = from item in db.Features_Boundaries_Country
4.         where item.CountryBoundary.STIntersects(sqlEnvelope).Value
5.         select item.CountryBoundary;
6.  
7. Console.WriteLine(q.ToList<SqlGeography>()[0].STAsText().
                                                ToSqlString().ToString());
8. 
   

This query will could be compiled without syntaxes errors, but we will get a run-time error on the execution of line 7, at the time then LINQ will try to send a request to server.

We will get a next exception message: "Method 'System.Data.SqlTypes.SqlBoolean STIntersects(Microsoft.SqlServer.Types.SqlGeography)' has no supported translation to SQL.".

It means, what LINQ to SQL doesn't know how to translate this call into SQL.

In this situation we can use the database stored procedure or function.

Stored Procedures

We can create a stored procedure which will fetch a records by criteria of intersection the given bonding rectangle.

CREATE PROCEDURE [dbo].[sp_bbx_Features_Boundaries_Country]
  @boundingBox varbinary(max)
AS
BEGIN
  SET NOCOUNT ON;
  SELECT *
    FROM dbo.Features_Boundaries_Country
    WHERE GEOGRAPHY::STGeomFromWKB(@boundingBox, 4326).STIntersects(CountryBoundary) = 1;
  RETURN;
END

This stored procedure takes on input a WKB representation of the geometry object, converts it into valid SQL Server geography object and selects all needed data from a certain feature table.

After that we must write a wrapper for the stored procedure in the data context class.

[MethodInfo(Name="dbo.sp_bbx_Features_Boundaries_Country",IsComposable=false)]
public ISingleResult<Features_Boundaries_Country> sp_bbx_Features_Boundaries_Country ([Parameter(DbType="varbinary(max)",Name=  "boundingBox")] byte[] g)
{
  IExecuteResult execResult = this.ExecuteMethodCall(this, ((MethodInfo)
                                            (MethodInfo.GetCurrentMethod())), g);
  ISingleResult<Features_Boundaries_Country> result =
            ((ISingleResult<Features_Boundaries_Country>)execResult.ReturnValue);
  return result;
}

Example of using.

FeaturesDB db = new FeaturesDB(@"...");

var q = from item in db.sp_bbx_Features_Boundaries_Country(envelope.AsBinary)
        select new { item.CountryBoundary, item.CountryName};

foreach (var item in q)
  Console.WriteLine(item.CountryName);

Output of this example:
One of disadwantages of this approach is in, what we have to define a one stored procedure for each feature table and must use a different methods for different feature collections. We can avoid this by using a generic methods and dynamic SQL (keeping in mind what dynamic SQL often works more slowly than stored procedures of functions).

So to realize this universalization we need a stored procedure like presented below.

CREATE PROCEDURE [dbo].[sp_BBX]
  @feature varchar(100),
  @boundingBox varbinary(max)
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;
  
  DECLARE @columnName varchar(30);
  DECLARE @query varchar(MAX);
  DECLARE @wktBoundingBox varchar(MAX);
  
  SET @columnName = (SELECT Geometry_Column
                        FROM dbo.Geometry_Column_Information
                        WHERE F_Table_Name = @feature);
  
  SET @wktBoundingBox = (GEOGRAPHY::STGeomFromWKB(@boundingBox, 4326)).STAsText();
  
  SET @query = 'SELECT * FROM ' + @feature +
    ' WHERE GEOGRAPHY::STGeomFromText(''' +
    @wktBoundingBox + ''', 4326).STIntersects(' + @columnName + ') = 1';

  execute(@query);
END

This procedure have two imput parameters: the boundary box object in WKB format and additional parameter — the name of feature over which filtering must be realized. I want to remind, what feature table in out database has the same names as features they store.

Inside the procedure we look for geometry column name for this feature, transforms a WKB to Geography database and collect a query string which then dynamicly exutes.

In program we need to realize some methods. The first — is a central, public generic method, wich will be called from the LINQ queries.

public ISingleResult<T> bbx_function<T>(string feature, byte[] g)
{
  if (typeof(T).Equals(typeof(SFACA.LINQ.Features_Boundaries_Country)))
    return ((ISingleResult<T>)bbx_Features_Boundaries_Country(feature, g));
  else if (typeof(T).Equals(typeof(Features_Boundaries_CountryRegion)))
    return ((ISingleResult<T>)bbx_Features_Boundaries_CountryRegion(feature, g));
  else
    throw new ArgumentException();
}

Depending on the actual type of T this method calls one of special methods (one for each feature), which will execute a generic stored procedure on the server and accepts it results. These methods just are wrappers around the same stored procedure and they are presented below.

[Function(Name = "dbo.sp_BBX")]
private ISingleResult<Features_Boundaries_Country> bbx_Features_Boundaries_Country([Parameter(DbType = "varchar(100)", Name = "feature")] string feature, [Parameter(DbType = "varbinary(max)", Name = "boundingBox")] byte[] g)
{
  IExecuteResult execResult = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), new object[2] { feature, g });
  ISingleResult<Features_Boundaries_Country> result = ((ISingleResult<Features_Boundaries_Country>)execResult.ReturnValue);
  return result;
}

[Function(Name = "dbo.sp_BBX")]
private ISingleResult<Features_Boundaries_CountryRegion> bbx_Features_Boundaries_CountryRegion([Parameter(DbType = "varchar(100)", Name = "feature")] string feature, [Parameter(DbType = "varbinary(max)", Name = "boundingBox")] byte[] g)
{
  IExecuteResult execResult = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), new object[2] { feature, g });
  ISingleResult<Features_Boundaries_CountryRegion> result = ((ISingleResult<Features_Boundaries_CountryRegion>)execResult.ReturnValue);
  return result;
}

The example of using solution is presented below.

var qq = db.bbx_function<Features_Boundaries_Country>("Features_Boundaries_Country", envelope.AsBinary);

foreach (var item in qq)
  Console.WriteLine(item.CountryName);

The output of this example is the same as on the figure 2.

But described approach has a significant failure. One another example.

var q = from item in db.bbx_function<Features_Boundaries_Country>
                            ("Features_Boundaries_Country", envelope.AsBinary)
        where item.CountryName.StartsWith("C")
        select item;

foreach (var item in q)
    Console.WriteLine(item.CountryName);

In this example we do all as before (select boundaries from specified rectangle) and adds one extra condition: from the set of all boundaries which intersects a given bounding box we select just those, which starts with letter "C".

Then we call a stored procedure, one of described above, it returns all data, all boundaries which fall into specified bounding box and have the first letter "C".

In this example

1. LINQ to SQL call a stored procedure which selects features by bounding box creteria.
2. SQL Server returns all finded features to the program.
3. The program enumerate retrieved features and select into the result set just those, that have a first letter "C" in the country name field.

The failure is in the second stage. It is much better if the second first letter criteria could be processed on the server, and among the network would be transfered not all 18 features, but just needed 2.

Table-valued SQL Functions

To solve a problem with stored procedure we can use a table-valued SQL functions (aka inline functions).

By using a table-valued inline function we allow for LINQ to SLQ to build a SQL query with all filter criterias which will be processed on the server side.

ALTER FUNCTION [dbo].[f_bbx_Features_Boundaries_Country]
(  
  @boundingBox varbinary(max)
)
RETURNS TABLE
AS
RETURN
(
  SELECT *
    FROM dbo.Features_Boundaries_Country
    WHERE GEOGRAPHY::STGeomFromWKB(@boundingBox,
                                        4326).STIntersects(CountryBoundary) = 1
)

One remark. We can't use any dynamic SQL in the tabled-value function, so it is imposible to realize a "generic" approach as at it was made for stored procedures. So for each feature we have to define its own function.

The program wrapper for the sql-function.

[Function(Name = "dbo.f_bbx_Features_Boundaries_Country", IsComposable = true)]
public IQueryable<Features_Boundaries_Country> bbx_Features_Boundaries_Country
                                ([Parameter(DbType = "varbinary(max)")] byte[] g)
{
   return this.CreateMethodCallQuery<Features_Boundaries_Country>(this,
                              ((MethodInfo)(MethodInfo.GetCurrentMethod())), g);
}

And example of use.

var q = from item in db.bbx_Features_Boundaries_Country(
                                                 sqlEnvelope.STAsBinary().Buffer)
        where item.CountryName.StartsWith("C")
        select item;

foreach (var item in q)
  Console.WriteLine(item.CountryName);

The have a same output as on figure 2.

And in the debug mode we can look at the sql-query, generated by LINQ to SQL for this example, here is a small screenshort.
The sql-query in text:

SELECT [t0].[FeatureID], [t0].[CountryName], [t0].[CountryBoundary]
  FROM [dbo].[f_bbx_Features_Boundaries_Country](@p0) AS [t0]
  WHERE [t0].[CountryName] LIKE @p1