July 28, 2011 Note: This is an outdated post. Recently, the ADO.NET team has released a CTP with Spatial support as a first class citizen of the Entity Framework!!! See the following posts that I wrote as I explored the new API:
http://www.jasonfollas.com/blog/archive/2011/07/20/entity-framework-spatial-first-look.aspx
http://www.jasonfollas.com/blog/archive/2011/07/21/entity-framework-spatial-dbgeography-members.aspx
http://www.jasonfollas.com/blog/archive/2011/07/27/entity-framework-spatial-a-real-world-example.aspx
The Entity Framework does not support using User Defined Types (at least in the SQLCLR sense of the term) as properties of an entity. Yesterday, Julie Lerman contacted me to see if we could find a workaround to this current limitation, particularly for the SQL Server Spatial Types (Geometry and Geography).
Whenever I hear of someone wanting to use Spatial data in their application, my first thought is always “what do they want to do with the data once they have it?” This is because most of the time (in my limited observation), an application does not need the spatial data itself, but rather, it just needs to use that data in the predicate of a query (i.e., the query results contain no spatial information). For example, an application might want all zipcodes that are within 50 km of a point, but the application doesn’t need the actual shapes that define each zip code.
But, assuming that the developer knows what they are doing and has a legitimate reason to include a spatial type in the results, then how can they use the Entity Framework to get the spatial data into their application? That was our quest.
Entity Framework Primitive Types
Admittedly, I know very little about EF. So, my approach to this problem spent a lot of time using .NET Reflector to try to understand what the EF designer was doing behind the scenes (this also proved to be a a good way to understand EF better!). The first thing that I wanted to figure out is how EF determines which primitive type to use for each SQL Server type.
I downloaded and imported the States data from the US Census Data for SQL Server 2008 project on Codeplex. Then, I used the Entity Data Model Designer in VS2010 to generate a model based on my database which resulted in an entity without the geometry property. Looking at the XML for the .edmx file, I saw the following:
<!--Errors Found During Generation:warning 6005: The data type 'geometry' is not supported; the column 'geom' in table 'Spatial.dbo.State' was excluded.--> <EntityTypeName="State"> <Key> <PropertyRef Name="StateID"/> </Key> <Property Name="StateID" Type="int" Nullable="false"/> <Property Name="StateName" Type="nvarchar" Nullable="false" MaxLength="50"/> </EntityType>
I don’t believe that EF is hating on “geometry” specifically (the 6005 warning). Rather, I think that if the SQL Server type cannot be mapped to a .NET type from the BCL, then it simply does not know how to handle it. Certainly, they don’t want to try to map to a type that is not included in the .NET Framework itself (as would be the case for the Spatial data types).
But, what is EF using to determine the mappings?
I looked long and hard, but couldn’t quite figure out the mechanism that gets invoked when the model is generated. But, I think the key might lie in the Microsoft.VisualStudio.Data.Providers.SqlServer.SqlMappedObjectConverter.GetFrameworkTypeFromNativeType() method:
// Disassembly by Reflector
protected override Type GetFrameworkTypeFromNativeType(string nativeType)
{
switch (this.GetProviderTypeFromNativeType(nativeType))
{
case 0:
return typeof(long);
case 1:
case 7:
case 0x13:
case 0x15:
return typeof(byte[]);
case 2:
return typeof(bool);
case 3:
case 10:
case 11:
case 12:
case 0x12:
case 0x16:
return typeof(string);
case 4:
case 15:
case 0x1f:
case 0x21:
return typeof(DateTime);
case 5:
case 9:
case 0x11:
return typeof(decimal);
case 6:
return typeof(double);
case 8:
return typeof(int);
case 13:
return typeof(float);
case 14:
return typeof(Guid);
case 0x10:
return typeof(short);
case 20:
return typeof(byte);
case 0x20:
return typeof(TimeSpan);
case 0x22:
return typeof(DateTimeOffset);
}
return typeof(object);
}
For SQL Server, the Native Types come from the System.Data.SqlDbType enumeration:
// Disassembly by Reflector
public enum SqlDbType
{
BigInt = 0,
Binary = 1,
Bit = 2,
Char = 3,
Date = 0x1f,
DateTime = 4,
DateTime2 = 0x21,
DateTimeOffset = 0x22,
Decimal = 5,
Float = 6,
Image = 7,
Int = 8,
Money = 9,
NChar = 10,
NText = 11,
NVarChar = 12,
Real = 13,
SmallDateTime = 15,
SmallInt = 0x10,
SmallMoney = 0x11,
Structured = 30,
Text = 0x12,
Time = 0x20,
Timestamp = 0x13,
TinyInt = 20,
Udt = 0x1d,
UniqueIdentifier = 14,
VarBinary = 0x15,
VarChar = 0x16,
Variant = 0x17,
Xml = 0x19
}
My conclusion here was that if the SQL Server type could only be mapped to System.Object in the BCL (using the GetFrameworkTypeFromNativeType() method), then EF will not support using that field as a property of the entity. This coincides with the fact that to ADO.NET, the Geometry (and Geography) type is a User Defined Type (0x1d).
UPDATE: After all of this, I discovered that in System.Data.Entity.dll, there is a method that is probably a better candidate for what is actually used: System.Data.SqlClient.SqlProviderManifest.GetEdmType(). This method contains a similar switch{} as the code listed above, only it is EDM-specific instead of returning BCL types. Feel free to examine it using Reflector if you're curious about its contents.
The Workaround
Having figured out that piece of the puzzle, I was left with trying to figure out a workaround. If ADO.NET was unable to map a Geometry to a type in the BCL, then could we cast the Geometry as something that would be mappable?
SQL Server serializes spatial objects to binary when it saves the data in a table (documented here: http://msdn.microsoft.com/en-us/library/ee320529.aspx):
This binary data can be used to deserialize (“rehydrate”) the object in .NET code, which is exactly what SQL Server does when it needs to use the spatial objects. So, we just need to find a way for EF to pull these down as a byte array.
Looking back at the GetFrameworkTypeFromNativeType function from above, it appears that EF will likely recognize Binary, Image, Timestamp, and Varbinary all as SQL Server types that need to map to byte arrays. Perfect!
So, by creating a view in SQL Server that casts the Geometry column as a Varbinary(MAX), EF would recognize it as a type that could be mapped as an entity’s property.
CREATE VIEW vStates
AS SELECT StateID
, StateName
, CAST(geom AS VARBINARY(MAX)) AS geom
FROM dbo.State
Note: Julie had come up with this same solution at the same time, as our emails crossed paths reporting to one another.
Regenerating the EF model (using this view instead of the table) proved my assumption: the “geom” column now appeared as a Binary property of the vStates entity.
However, we’re not quite done yet. The point of this exercise was to get an instance of the spatial type to use in our .NET application. To do that, the Read(BinaryReader) instance method on SqlGeometry (or SqlGeography) must be invoked (using a MemoryStream as the intermediate between the byte[] and the BinaryReader).
The entire logic to retrieve the contents of the table and instantiate one of the Spatial types is as follows:
var entities = new SpatialEntities();
var vStates = entities.vStates;
// pull one of the entities from the collection
var geo2 = vStates.ToArray()[16];
var sqlGeom = new Microsoft.SqlServer.Types.SqlGeometry();
// Deserialize the bytes to rehydrate this Geometry instance
using (var stream = new System.IO.MemoryStream(geo2.geom))
{
using (var rdr = new System.IO.BinaryReader(stream))
{
sqlGeom.Read(rdr);
}
}
// Now let's prove that we have it. Dump WKT to Debug.
System.Diagnostics.Debug.Write(sqlGeom.ToString());
Output:
GEOMETRYCOLLECTION (LINESTRING (-99.530670166015625 39.132522583007812, -99.530670166015625 39.13250732421875), LINESTRING (-99.791290283203125 39.131988525390625, -99.791290283203125 39.131973266601562), …
So it worked!
Finally, an extension method would make this code a bit more general purpose:
public static class Extension
{
public static Microsoft.SqlServer.Types.SqlGeometry AsSqlGeometry(thisbyte[] binary)
{
var ret = new Microsoft.SqlServer.Types.SqlGeometry();
using (var stream = new System.IO.MemoryStream(binary))
{
using (var rdr = new System.IO.BinaryReader(stream))
{
ret.Read(rdr);
}
}
return ret;
}
}
The test code above then becomes a bit more readable after the refactoring:
var entities = new SpatialEntities();
var vStates = entities.vStates;
// pull one of the entities from the collection
var geo2 = vStates.ToArray()[16];
var sqlGeom = geo2.geom.AsSqlGeometry();
// Now let's prove that we have it. Dump WKT to Debug.
System.Diagnostics.Debug.Write(sqlGeom.ToString());
Helpful information: