Getting null values for geography column data using OrmLite to query SqlServer

ServiceStack.OrmLite SQL Server
1

Hello,

I’m having some issues querying a database table with geography column type using OrmLite and was hoping for some help!

Here’s my database table (in Sql Server):

CREATE TABLE [dbo].[james](
	[loc] [geography] NULL,
	[id] [int] IDENTITY(1,1) NOT NULL
)

Here’s my c# code:

public class James
{
    [AutoIncrement]
    public int id { get; set; }
    public SqlGeography loc { get; set; }
}

Insert a new row into table:

using (var db = ConnectionFactory.OpenDbConnection())
{
    var james = new James()
    {
        loc = SqlGeography.Point(40.6898329, -74.0452177, 4326)
    };
    db.Insert(james);
}

Then I verify that new insert succeeded via sql management studio:

SELECT loc.Lat, loc.Long FROM [james].[dbo].[james] WHERE id = 1

=> 40.6898329 -74.0452177

Then unfortunately my OrmLite query isn’t so successful:

using (var db = ConnectionFactory.OpenDbConnection())
{
    var result = db.SingleById<James>(1);
    // result.loc == null... wuh wuh
}

For some reason I’m not getting the ‘loc’ column value back (it’s null). I suspect that the OrmLite Sql Server converter isn’t behaving as I expect it to but unfortunately I haven’t found any documentation to enlighten me! :frowning:

Any help would be greatly appreciated!

Thanks,
James

2

I’ve just added a test for this that’s working as expected, i.e:

using (var db = OpenDbConnection())
{
    db.DropAndCreateTable<James>();

    var james = new James
    {
        loc = SqlGeography.Point(40.6898329, -74.0452177, 4326)
    };
    db.Insert(james);


    var result = db.SingleById<James>(1);

    result.PrintDump();

    Assert.That(result.id, Is.EqualTo(1));
    Assert.That(result.loc.Lat, Is.EqualTo(james.loc.Lat));
    Assert.That(result.loc.Long, Is.EqualTo(james.loc.Long));
    Assert.That(result.loc.STSrid, Is.EqualTo(james.loc.STSrid));
}

Not sure how you’re configuring your OrmLite DB Factory to support SQL Converters but this is the recommended usage:

var dialectProvider = SqlServerConverters.Configure(SqlServer2012Dialect.Provider);
var dbFactory = new OrmLiteConnectionFactory(connString, dialectProvider);

One potential issue that may cause this is that your DB Connection string needs to specify its using SQL Server 2012+, e.g we append this to the normal connection string in our tests:

ConnectionString = connectionString + ";Type System Version=SQL Server 2012;";
1 Like
3

Thanks mythz! It was indeed the case that I needed to append ;Type System Version = SQL Server 2012; to my connection string.

Much appeciated!