ServiceStack OrmLite Not Retrieving SqlGeography Fields

Question

I've following the instructions here to use SqlGeography types with ServiceStack OrmLite v. 4.0.56: https://github.com/ServiceStack/ServiceStack.OrmLite/wiki/SQL-Server-Types

SqlServerTypes.Utilities.LoadNativeAssemblies(AppDomain.CurrentDomain.BaseDirectory);
SqlServerConverters.Configure(SqlServer2012Dialect.Provider);

(not a web application)

Here's my DataModel class:

[DataContract]
[Schema("dbo")]
public class BusinessEntity
{
    [DataMember, PrimaryKey, AutoIncrement]
    public Int16 BusinessEntityId { get; set; }
    ...
    [DataMember]
    public SqlGeography LatLong { get; set; }
}

When I retrieve these records using OrmLite, all of the LatLong are null, but there is data in the database:

My tests are simply retrieving all records:

return Db.Select<DataModel.dbo.BusinessEntity>();

It looks like there was an error when installing the nuget package, however, there are no runtime errors (I consistently get this error when installing the nuget package):

Attempting to gather dependency information for package 'ServiceStack.OrmLite.SqlServer.Converters.4.0.56' with respect to project 'Services\Web\SO.Services.Data.Tests', targeting '.NETFramework,Version=v4.6.1' Attempting to resolve dependencies for package 'ServiceStack.OrmLite.SqlServer.Converters.4.0.56' with DependencyBehavior 'Lowest' Resolving actions to install package 'ServiceStack.OrmLite.SqlServer.Converters.4.0.56' Resolved actions to install package 'ServiceStack.OrmLite.SqlServer.Converters.4.0.56' Package 'Microsoft.SqlServer.Types.11.0.2' already exists in folder 'C:\Users\cklepeis\documents\visual studio 2015\Projects\SO\packages' Added package 'Microsoft.SqlServer.Types.11.0.2' to 'packages.config' Executing script file 'C:\Users\cklepeis\documents\visual studio 2015\Projects\SO\packages\Microsoft.SqlServer.Types.11.0.2\tools\install.ps1'... Where-Object : Cannot bind parameter 'FilterScript'. Cannot convert the "Name" value of type "System.String" to type "S ystem.Management.Automation.ScriptBlock". At C:\Users\cklepeis\documents\visual studio 2015\Projects\SO\packages\Microsoft.SqlServer.Types.11.0.2\tools\install.p s1:11 char:50 + $folderx86 = $sqlServerTypes.ProjectItems | where <<<< Name -eq "x86" + CategoryInfo : InvalidArgument: (:) [Where-Object], ParameterBindingException + FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Microsoft.PowerShell.Commands.WhereObjectCommand Where-Object : Cannot bind parameter 'FilterScript'. Cannot convert the "Name" value of type "System.String" to type "S ystem.Management.Automation.ScriptBlock". At C:\Users\cklepeis\documents\visual studio 2015\Projects\SO\packages\Microsoft.SqlServer.Types.11.0.2\tools\install.p s1:17 char:50 + $folderx64 = $sqlServerTypes.ProjectItems | where <<<< Name -eq "x64" + CategoryInfo : InvalidArgument: (:) [Where-Object], ParameterBindingException + FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Microsoft.PowerShell.Commands.WhereObjectCommand Where-Object : Cannot bind parameter 'FilterScript'. Cannot convert the "Name" value of type "System.String" to type "S ystem.Management.Automation.ScriptBlock". At C:\Users\cklepeis\documents\visual studio 2015\Projects\SO\packages\Microsoft.SqlServer.Types.11.0.2\tools\install.p s1:23 char:46 + $cppLinkx86 = $folderx86.ProjectItems | where <<<< Name -eq "msvcr100.dll" + CategoryInfo : InvalidArgument: (:) [Where-Object], ParameterBindingException + FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Microsoft.PowerShell.Commands.WhereObjectCommand Where-Object : Cannot bind parameter 'FilterScript'. Cannot convert the "Name" value of type "System.String" to type "S ystem.Management.Automation.ScriptBlock". At C:\Users\cklepeis\documents\visual studio 2015\Projects\SO\packages\Microsoft.SqlServer.Types.11.0.2\tools\install.p s1:30 char:46 + $sqlLinkx86 = $folderx86.ProjectItems | where <<<< Name -eq "SqlServerSpatial110.dll" + CategoryInfo : InvalidArgument: (:) [Where-Object], ParameterBindingException + FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Microsoft.PowerShell.Commands.WhereObjectCommand Where-Object : Cannot bind parameter 'FilterScript'. Cannot convert the "Name" value of type "System.String" to type "S ystem.Management.Automation.ScriptBlock". At C:\Users\cklepeis\documents\visual studio 2015\Projects\SO\packages\Microsoft.SqlServer.Types.11.0.2\tools\install.p s1:37 char:46 + $cppLinkx64 = $folderx64.ProjectItems | where <<<< Name -eq "msvcr100.dll" + CategoryInfo : InvalidArgument: (:) [Where-Object], ParameterBindingException + FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Microsoft.PowerShell.Commands.WhereObjectCommand Where-Object : Cannot bind parameter 'FilterScript'. Cannot convert the "Name" value of type "System.String" to type "S ystem.Management.Automation.ScriptBlock". At C:\Users\cklepeis\documents\visual studio 2015\Projects\SO\packages\Microsoft.SqlServer.Types.11.0.2\tools\install.p s1:44 char:46 + $sqlLinkx64 = $folderx64.ProjectItems | where <<<< Name -eq "SqlServerSpatial110.dll" + CategoryInfo : InvalidArgument: (:) [Where-Object], ParameterBindingException + FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Microsoft.PowerShell.Commands.WhereObjectCommand

AutoHides : False Caption : Microsoft.SqlServer.Types Collection : {Microsoft.VisualStudio.Platform.WindowManagement.DTE.WindowBase, Microsoft.VisualStudio.Platfo rm.WindowManagement.DTE.WindowBase, Microsoft.VisualStudio.Platform.WindowManagement.DTE.Window Base, Microsoft.VisualStudio.Platform.WindowManagement.DTE.WindowBase...} CommandBars : {URL Toolbar, F1 Disambiguation Toolbar} ContextAttributes : System.__ComObject DTE : System.__ComObject Document : HWnd : 6425562 Height : 1671 IsFloating : False Kind : Tool Left : 1674 Linkable
: False LinkedWindowFrame : Microsoft.VisualStudio.Platform.WindowManagement.DTE.WindowBase LinkedWindows : Object : System.__ComObject ObjectKind : {E8B06F52-6D01-11D2-AA7D-00C04F990343} Project : ProjectItem : Selection : Top
: 283 Type : vsWindowTypeToolWindow Visible
: True Width : 1785 WindowState : vsWindowStateMaximize HasBeenDeleted : False Events
: Microsoft.VisualStudio.Platform.WindowManagement.DTE.WindowEvents VisibilityEvents : Microsoft.VisualStudio.Platform.WindowManagement.DTE.WindowVisibilityEvents Rect : 1674.0625,283.020833333333,1785,1671 OutstandingEventCount : 0

Successfully installed 'Microsoft.SqlServer.Types 11.0.2' to SO.Services.Data.Tests Package 'ServiceStack.OrmLite.SqlServer.Converters.4.0.56' already exists in folder 'C:\Users\cklepeis\documents\visual studio 2015\Projects\SO\packages' Added package 'ServiceStack.OrmLite.SqlServer.Converters.4.0.56' to 'packages.config' Successfully installed 'ServiceStack.OrmLite.SqlServer.Converters 4.0.56' to SO.Services.Data.Tests ========== Finished ==========

score 2 · Accepted Answer · answered Apr 27 '16 at 16:40

I'm not seeing any issues installing ServiceStack.OrmLite.SqlServer.Converters NuGet package:

Attempting to gather dependencies information for package 'ServiceStack.OrmLite.SqlServer.Converters.4.0.56' with respect to project 'ConsoleApplication4', targeting '.NETFramework,Version=v4.5'
Attempting to resolve dependencies for package 'ServiceStack.OrmLite.SqlServer.Converters.4.0.56' with DependencyBehavior 'Lowest'
Resolving actions to install package 'ServiceStack.OrmLite.SqlServer.Converters.4.0.56'
Resolved actions to install package 'ServiceStack.OrmLite.SqlServer.Converters.4.0.56'
Adding package 'Microsoft.SqlServer.Types.11.0.2' to folder 'C:\src\wip\ConsoleApplication4\packages'
Added package 'Microsoft.SqlServer.Types.11.0.2' to folder 'C:\src\wip\ConsoleApplication4\packages'
Added package 'Microsoft.SqlServer.Types.11.0.2' to 'packages.config'
Executing script file 'C:\src\wip\ConsoleApplication4\packages\Microsoft.SqlServer.Types.11.0.2\tools\install.ps1'...


AutoHides             : False
Caption               : Microsoft.SqlServer.Types
Collection            : {Microsoft.VisualStudio.Platform.WindowManagement.DTE.WindowBase, 
                        Microsoft.VisualStudio.Platform.WindowManagement.DTE.WindowBase, 
                        Microsoft.VisualStudio.Platform.WindowManagement.DTE.WindowBase, 
                        Microsoft.VisualStudio.Platform.WindowManagement.DTE.WindowBase...}
CommandBars           : {URL Toolbar, F1 Disambiguation Toolbar}
ContextAttributes     : System.__ComObject
DTE                   : System.__ComObject
Document              : 
HWnd                  : 32967448
Height                : 1060
IsFloating            : False
Kind                  : Tool
Left                  : 74
Linkable              : False
LinkedWindowFrame     : Microsoft.VisualStudio.Platform.WindowManagement.DTE.WindowBase
LinkedWindows         : 
Object                : System.__ComObject
ObjectKind            : {E8B06F52-6D01-11D2-AA7D-00C04F990343}
Project               : 
ProjectItem           : 
Selection             : 
Top                   : 254
Type                  : vsWindowTypeToolWindow
Visible               : True
Width                 : 2357
WindowState           : vsWindowStateMaximize
HasBeenDeleted        : False
Events                : Microsoft.VisualStudio.Platform.WindowManagement.DTE.WindowEvents
VisibilityEvents      : Microsoft.VisualStudio.Platform.WindowManagement.DTE.WindowVisibilityEvents
Rect                  : 73.5,253.5,2357,1060
OutstandingEventCount : 0



Successfully installed 'Microsoft.SqlServer.Types 11.0.2' to ConsoleApplication4
Adding package 'ServiceStack.Interfaces.4.0.56' to folder 'C:\src\wip\ConsoleApplication4\packages'
Added package 'ServiceStack.Interfaces.4.0.56' to folder 'C:\src\wip\ConsoleApplication4\packages'
Added package 'ServiceStack.Interfaces.4.0.56' to 'packages.config'
Successfully installed 'ServiceStack.Interfaces 4.0.56' to ConsoleApplication4
Adding package 'ServiceStack.Text.4.0.56' to folder 'C:\src\wip\ConsoleApplication4\packages'
Added package 'ServiceStack.Text.4.0.56' to folder 'C:\src\wip\ConsoleApplication4\packages'
Added package 'ServiceStack.Text.4.0.56' to 'packages.config'
Successfully installed 'ServiceStack.Text 4.0.56' to ConsoleApplication4
Adding package 'ServiceStack.Common.4.0.56' to folder 'C:\src\wip\ConsoleApplication4\packages'
Added package 'ServiceStack.Common.4.0.56' to folder 'C:\src\wip\ConsoleApplication4\packages'
Added package 'ServiceStack.Common.4.0.56' to 'packages.config'
Successfully installed 'ServiceStack.Common 4.0.56' to ConsoleApplication4
Adding package 'ServiceStack.OrmLite.4.0.56' to folder 'C:\src\wip\ConsoleApplication4\packages'
Added package 'ServiceStack.OrmLite.4.0.56' to folder 'C:\src\wip\ConsoleApplication4\packages'
Added package 'ServiceStack.OrmLite.4.0.56' to 'packages.config'
Successfully installed 'ServiceStack.OrmLite 4.0.56' to ConsoleApplication4
Adding package 'ServiceStack.OrmLite.SqlServer.4.0.56' to folder 'C:\src\wip\ConsoleApplication4\packages'
Added package 'ServiceStack.OrmLite.SqlServer.4.0.56' to folder 'C:\src\wip\ConsoleApplication4\packages'
Added package 'ServiceStack.OrmLite.SqlServer.4.0.56' to 'packages.config'
Successfully installed 'ServiceStack.OrmLite.SqlServer 4.0.56' to ConsoleApplication4
Adding package 'ServiceStack.OrmLite.SqlServer.Converters.4.0.56' to folder 'C:\src\wip\ConsoleApplication4\packages'
Added package 'ServiceStack.OrmLite.SqlServer.Converters.4.0.56' to folder 'C:\src\wip\ConsoleApplication4\packages'
Added package 'ServiceStack.OrmLite.SqlServer.Converters.4.0.56' to 'packages.config'
Successfully installed 'ServiceStack.OrmLite.SqlServer.Converters 4.0.56' to ConsoleApplication4
========== Finished ==========

If these installation issues persist you can try deleting your NuGet /packages folder and clearing your NuGet cache so the next time you install ServiceStack.OrmLite.SqlServer.Converters it will redownload all packages from NuGet again.

To use SQL Server specific types you'll need to use the SqlServer2012Dialect.Provider and also specify you're using SQL Server 2012 on the connection string, e.g:

SqlServerConverters.Configure(SqlServer2012Dialect.Provider);

var dbFactory = new OrmLiteConnectionFactory(
    "Server=localhost;Database=test;User Id=test;Password=test;Type System Version=SQL Server 2012",
    SqlServer2012Dialect.Provider);

You'll then be able to use the SQL Server specific types as normal, e.g. this example:

db.DropAndCreateTable<BusinessEntity>();

db.Insert(new BusinessEntity
{
    BusinessEntityId = 1,
    LatLong = SqlGeography.Point(40.6898329, -74.0452177, 4326)
});

db.Select<BusinessEntity>().PrintDump();

Prints out the populated SqlGeography record:

[
        {
                BusinessEntityId: 1,
                LatLong:
                {
                        IsNull: False,
                        STSrid: 4326,
                        Lat: 40.6898329,
                        Long: -74.0452177,
                        Z: Null,
                        M: Null,
                        HasZ: False,
                        HasM: False
                }
        }
]

Adding "Type System Version=SQL Server 2012" to the connection string did the trick. — Chris Klepeis, Apr 27 '16 at 17:52

ServiceStack OrmLite Not Retrieving SqlGeography Fields

1 Answers1