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.Types assembly.

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.

Errors due to missing types dll and how to solve them

It can however be the Designer can't load the Microsoft.SqlServer.Types.dll dll, simply because it's e.g. not added to the PC's GAC, or you don't have the SQL Server client tools installed locally. This will lead to errors in the log when you retrieve the relational meta data from SQL Server. The errors aren't fatal but can lead to you not being able to use geometry/geography types.

To solve this issue, make sure the Designer can load a Microsoft.SqlServer.Types.dll dll of the same version as being used by the SQL Server you're connecting to. One way to do this is by copying the Microsoft.SqlServer.Types.dll from the SQL Server installation from the folder mentioned above and placing it in the <LLBLGen Pro Designer Installation Folder>\Drivers\SQLServer folder. Another way to solve this is by obtaining the Microsoft.SqlServer.Types.dll from nuget, by downloading the nuget package. It can be you need to download an older package version.

At runtime, e.g. with the LLBLGen Pro Runtime Framework, you have to reference the same dll to make sure spatial types are persisted and loaded properly.

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.