How to use SQL Spatial Data with WCF ODATA Spatial · OData

Note: The below code is extracted from a working Windows Azure project on Visual Studio 2012. You may need to adapt it for your purpose. A quote with our contribution in your code will be highly appreciated.

Abstract

With the emerging demand of “Global Monitoring” and the introduction of new embedded technologies, the vision of Internet of Things is increasingly becoming a reality. This will lead to infrastructures that support the deployment of billions of sensors and carry Petabytes of data from these sensors to consumer applications.

Developing such solutions in a scalable and robust manner through heterogeneous pre-existing networks and systems is a challenge. Companies face distracting, time consuming and misplaced investments when trying to address all these problems at once, often failing to deliver their solution to the market, not because of the technology itself but due to the need of supporting infrastructure.

A common scenario we will be looking into in this article consists in using OData in conjunction with a SQL-based backend, leveraging Odata Spatial Model and SQL Spatial Type.

One of the goals of OData and .Net underlying frameworks is to offer a robust end to end solution to fulfill these issues.

According to OData, one of the key features of the next global solutions, is common spatial properties which include time and geography. To implement this feature, Odata v3 supports the Spatial Model.

On the backend side, SQL Server provides a Spatial Type, which is well distributed and already widely used.

However, when using Spatial Model, a gap appears in the flow of service as the OData spatial types are not cross compatible with the SQL Spatial library. Any attempt to build a WCF service against Entity with SQL data will fail with gentle kind of “not compatible type” message.

This short article will show you a trick to fill this gap as shown in the previous diagram.

Wrapping the Geography data Model

First we have to define wrappers which define a common data model for both types. We use the explicit and implicit operator to achieve this. For the Geographic Point:

public class GeographyPointWrapper
{
public static CultureInfo defaultCulture = CultureInfo.GetCultureInfo("En-Us");
// implicit Spatial Geography Point to GeographyPointWrapper conversion operator
public static implicit operator GeographyPointWrapper(GeographyPoint gp)
{
double l_lat = (double)(gp != null ? gp.Latitude : 0);
double l_lon = (double)(gp != null ? gp.Longitude : 0);
double? l_alt = gp != null ? gp.Z : 0;
return GeographyPointWrapper.Create(l_lat, l_lon, l_alt);
}
// implicit GeographyPointWrapper to Spatial Geography Point conversion operator
public static implicit operator GeographyPoint(GeographyPointWrapper p)
{
double l_lat = (double)(p != null ? p.Latitude : 0);
double l_lon = (double)(p != null ? p.Longitude : 0);
double? l_alt = p != null ? p.Altitude : 0;
return GeographyPoint.Create(l_lat, l_lon, l_alt);
}
// implicit SQLGeography to GeographyPointWrapper conversion operator
public static implicit operator GeographyPointWrapper(DbGeography dbg)
{
double l_lat = (double)(dbg != null && dbg.Latitude != null ? dbg.Latitude : 0);
double l_lon = (double)(dbg != null && dbg.Longitude != null ? dbg.Longitude : 0);
double? l_alt = dbg != null ? dbg.Elevation : 0;
return GeographyPointWrapper.Create(l_lat, l_lon, l_alt);
}
// implicit GeographyPointWrapper to SQLGeography conversion operator
public static implicit operator DbGeography(GeographyPointWrapper p)
{
// has to be optimized !!
return DbGeography.FromText("POINT(" + p.Latitude.ToString(defaultCulture)
+ " " + p.Longitude.ToString(defaultCulture) + ")");
}
public static GeographyPointWrapper Create(double lat, double lon, double? alt)
{
return new GeographyPointWrapper(lat, lon, alt);
}
double _lat;
double _lon;
double? _alt;
protected GeographyPointWrapper(double lat, double lon, double? alt)
{
_lat = lat;
_lon = lon;
_alt = alt;
}
public double Latitude { get { return _lat; } set { _lat = value; } }
public double Longitude { get { return _lon; } set { _lon = value; } }
public double? Altitude { get { return _alt; } set { _alt = value; } }
}

Exposing the Services

After this, we need to define a service model which deals with the Entity framework and WCF tools.

[IgnorePropertiesAttribute("location")]
 [DataServiceKey("THING_KEY")]
public class IOfThings_Things
 {
 GeographyPointWrapper _w;
public IOfThings_Things()
 {
 }
[Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
 public long THING_KEY { get; set; }
 public long CLASS_KEY { get; set; }
 public System.Data.Spatial.DbGeography location { get { return _w; } set { _w = value; } }
 public Nullable timestamp { get; set; }
 public string metadataURI { get; set; }
 public string serviceURI { get; set; }
 public string manifestURI { get; set; }
[NotMappedAttribute]
 public GeographyPoint wcf_location { get { return _w; } set { _w = value; } }
 }

On the Entity framework view, we set the [NotMappedAttribute] to the wcf_location property.

On the WCF view, we set the [IgnorePropertiesAttribute] to the location property.

Each property has the same wrapper model, which makes the cast with the implicit operator.

This allows to implement each Wrapper type as needed in your own services.

Implementing the entity

Because we want to show some changes to the code, we chose to code the entity from the ground up. But you can generate the DBContext using Entity tools and edit it later. Remember that when using the Entity tool, all your changes to the generated code will be lost when you update the DB model.

public partial class IOfThings_Context : DbContext
 {
const string ds = "your azure connection string ";
 public static IOfThings_Context defaultContext = new IOfThings_Context();
public IOfThings_Context() : base(ds) { }
public DbSet<IOfThings_Things> IOfThings_Things { get; set; }
 }
public class IOfThings_Entity
 {
 public IQueryable<IOfThings_Things> IOfThings_Things
 {
 get
 {
 var l_res = from t in IOfThings_Context.defaultContext.IOfThings_Things select t;
 return l_res;
 }
 }
 }

If you want to add write capabilities, you need to add the support of the IUpdatable interface which is a bit complex.

public class IOfThings_Entity : IUpdatable
 {
 public IQueryable<IOfThings_Things> IOfThings_Things
 {
 get
 {
 var l_res = from t in IOfThings_Context.defaultContext.IOfThings_Things select t;
 return l_res;
 }
 }
// Creates an object in the container.
 object IUpdatable.CreateResource(string containerName, string fullTypeName)
 {
 // create the object using reflection
 var objType = Type.GetType(fullTypeName);
 var resourceToAdd = Activator.CreateInstance(objType);
 // add the course to the courses in-memory list
 IOfThings_Context.defaultContext.IOfThings_Things.Add((IOfThings_Things)resourceToAdd);
 return resourceToAdd;
 }
// Gets the object referenced by the resource.
 object IUpdatable.GetResource(IQueryable query, string fullTypeName)
 {
 object resource = query.Cast<IOfThings_Things>().SingleOrDefault();
// fullTypeName can be null for deletes
 if (fullTypeName != null && resource.GetType().FullName != fullTypeName)
 throw new ApplicationException("Unexpected type for this resource.");
 return resource;
 }
// Resets the value of the object to its default value.
 object IUpdatable.ResetResource(object resource)
 {
 throw new NotImplementedException();
 }
// Sets the value of the given property on the object.
 void IUpdatable.SetValue(object targetResource, string propertyName, object propertyValue)
 {
 // get the property info using reflection
 Type targetType = targetResource.GetType();
 PropertyInfo property = targetType.GetProperty(propertyName);
 // set the property value
 property.SetValue(targetResource, propertyValue, null);
 }
// Gets the value of a property on an object.
 object IUpdatable.GetValue(object targetResource, string propertyName)
 {
 // get the property info using reflection
 var targetType = targetResource.GetType();
 var targetProperty = targetType.GetProperty(propertyName);
 // retrun the value of the property
 return targetProperty.GetValue(targetResource, null);
 }
// Sets the related object for a reference.
 void IUpdatable.SetReference(object targetResource, string propertyName, object propertyValue)
 {
 ((IUpdatable)this).SetValue(targetResource, propertyName, propertyValue);
 }
// Adds the object to the related objects collection.
 void IUpdatable.AddReferenceToCollection(object targetResource, string propertyName, object resourceToBeAdded)
 {
 PropertyInfo pi = targetResource.GetType().GetProperty(propertyName);
 if (pi == null) throw new Exception("Can't find property");
 IList collection = (IList)pi.GetValue(targetResource, null);
 collection.Add(resourceToBeAdded);
 }
// Removes the object from the related objects collection.
 void IUpdatable.RemoveReferenceFromCollection(
 object targetResource, string propertyName, object resourceToBeRemoved)
 {
 PropertyInfo pi = targetResource.GetType().GetProperty(propertyName);
 if (pi == null)
 throw new Exception("Can't find property");
 IList collection = (IList)pi.GetValue(targetResource, null);
 collection.Remove(resourceToBeRemoved);
 }
// Deletes the resource.
 void IUpdatable.DeleteResource(object targetResource)
 {
 // remove the course form the courses list
 IOfThings_Context.defaultContext.IOfThings_Things.Remove((IOfThings_Things)targetResource);
 }
// Saves all the pending changes.
 void IUpdatable.SaveChanges()
 {
 IOfThings_Context.defaultContext.SaveChanges();
 }
// Returns the actual instance of the resource represented
 // by the resource object.
 object IUpdatable.ResolveResource(object resource)
 {
 return resource;
 }
// Reverts all the pending changes.
 void IUpdatable.ClearChanges()
 {
 throw new NotSupportedException();
 }
 }

WCF

Finally, the WCF DataService:

[JSONPSupportBehavior]
 [ServiceBehavior(IncludeExceptionDetailInFaults = true)]
 public class IOfThings : DataService<IOfThings_Entity>
 {
 // This method is called only once to initialize service-wide policies.
 public static void InitializeService(DataServiceConfiguration config)
 {
 config.SetEntitySetAccessRule("*", EntitySetRights.All);
 // config.SetServiceOperationAccessRule("MyServiceOperation", ServiceOperationRights.All);
 config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V3;
 config.DataServiceBehavior.AcceptProjectionRequests = true;
 config.DataServiceBehavior.AcceptCountRequests = true;
 config.UseVerboseErrors = true;
 }
 }

Note that we use the JSONPSupportBehavior for JSON support. You can find it at https://code.msdn.microsoft.com/DataServicesJSONP

Finally

Now you have a clean skeleton to bridge OData Spatial and SQL Spatial models and use them in conjunction.

It took us few hours to find this solution and we Hope this will save you time, enjoy the ride!

Guillaume Pelletier -- Guillaume.Pelletier@dotvision.com

Guillaume Deroy -- Guillaume.Deroy@dotvision.com