SQL Server GeoSpatial types
- June 11, 2020
Recently, started working with SQL Server GeoSpatial types. Specifically I am using column type geography. All was working fine when using on localhost web site however after deployment to a test environment, I go the following exception.
Initially, I thought something was not installed on the server (could still be) however the following is the exception and the resolution. Exception:
System.InvalidOperationException: Spatial types and functions are not available for this provider because the assembly 'Microsoft.SqlServer.Types' version 10 or higher could not be found.
at System.Data.Entity.SqlServer.SqlTypesAssemblyLoader.GetSqlTypesAssembly() at System.Data.Entity.SqlServer.SqlSpatialDataReader.<.cctor>b__0() at System.Lazy`1.CreateValue() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Lazy`1.get_Value() at System.Data.Entity.SqlServer.SqlSpatialDataReader.GetGeography(Int32 ordinal) at System.Data.Entity.Core.Common.Internal.Materialization.Shaper.ErrorHandlingValueReader`1.GetValue(DbDataReader reader, Int32 ordinal) at System.Data.Entity.Core.Common.Internal.Materialization.Shaper.GetSpatialPropertyValueWithErrorHandling[TProperty](Int32 ordinal, String propertyName, String typeName, PrimitiveTypeKind spatialTypeKind) at lambda_method(Closure , Shaper ) Resolution: To include in your project for deployment purposes, use nuget to get Microsoft.SqlServer.Types package.
After the package is installed, the following readme will give you details for both ASP.NET and Desktop applications.
To deploy an application that uses spatial data types to a machine that does not have 'System CLR Types for SQL Server' installed you also need to deploy the native assembly SqlServerSpatial110.dll.Both x86 (32 bit) and x64 (64 bit) versions of this assembly have been added to your project under the SqlServerTypes\x86 and SqlServerTypes\x64 subdirectories.
The native assembly msvcr100.dll is also included in case the C++runtime is not installed. Adding the following to the Global.asax.cs Application_Start()