How to work with spatial types

The LLBLGen Pro designer supports spatial types on SQL Server, using database first. You can use spatial types using model first, but the designer has to know about the spatial types before it can use them. The best way to do so is by creating a dummy table with two fields, one of type geometry and the other of type geography and retrieve the meta-data using database first features of the designer.

Spatial types in .NET are a bit problematic as there's no central definition of the two spatial types in the .NET framework which is used by all code needing spatial types: Entity Framework for example defines the types themselves, as well as SQL Server, which defines a different set of types which represent the same SQL Server spatial types as the ones defined by Entity Framework.

Other frameworks shipped by Microsoft which use the .NET framework also re-define their own spatial types. The LLBLGen Pro designer works with real .NET types to define types on fields, which is the reason working model-first with spatial types requires the types to be known before you can define field types with them. .NET 4.5 introduces System.Data.Spatial, however this is incompatible with the Spatial types on Nuget in the Microsoft.SqlServer.Typesassembly.

SQL Server .NET types library

When you retrieve the relational meta data of a SQL Server schema/catalog which contains spatial types, the designer will retrieve the actual .NET types which are used by SQL Server to implement the spatial types. These spatial types are located in the assembly Microsoft.SqlServer.Types.dll which is located in the folder <SQL Server installation folder>\<version>\SDK\Assemblies.

When this assembly is found when spatial types are retrieved from the SQL Server catalog / schema, the SqlGeometry and SqlGeography types are automatically added with type shortcuts to the project. If the assembly isn't found on the system, the spatial types won't be read as the .NET type couldn't be loaded.

Entity Framework re-map

The templates for Entity Framework will re-map the SqlGeography and SqlGeometry types to the Entity Framework internal types at code-generation.