.Net Core - SQL Server Geography data type exception

Question

I am trying to use the new Microsoft.Data.SqlClient in my .Net Core application. Whenever I select a column that has a datatype of Geography, I get the following error. Unable to cast object of type 'Microsoft.SqlServer.Types.SqlGeography' to type 'Microsoft.Data.SqlClient.Server.IBinarySerialize'.'

To reproduce, I created a database on my computer (SQL2019) with one table:

DROP TABLE IF EXISTS geotest
GO

CREATE TABLE geotest (g GEOGRAPHY)
GO

INSERT INTO geotest (g)
VALUES
( 0xe6100000010cec51b81e854b4040ec51b81e852b58c0 ), 
( 0xe6100000010c6666666666a64740713d0ad7a3905ec0 )

I create a console application project (Visual Studio 2019, .Net Core 3.1) and add the NUGET package: Microsoft.Data.SqlClient

Here is my sample app:

using System;
using Microsoft.Data.SqlClient;


namespace TestWithOtherSQL
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=joe;Trusted_Connection=True;");
            conn.Open();

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = @"SELECT g FROM geotest";

            SqlDataReader sr = cmd.ExecuteReader();
            while (sr.Read())
            {
                var a = sr[0];
            }

            conn.Close();
            Console.WriteLine("Hello World!");
        }
    }
}

It throws the error on this line:

               var a = sr[0];

Unable to cast object of type 'Microsoft.SqlServer.Types.SqlGeography' to type 'Microsoft.Data.SqlClient.Server.IBinarySerialize'.'

Troubleshooting:

If I change out the Nuget package to System.Data.SqlClient and include package Microsoft.SqlServer.Types (change the using to System.Data.SqlClient) The same code works.

So why don't I just do that? Good question... My actual database is on Azure SQL DB and I need to use Microsoft.Data.SqlClient to support ActiveDirectory Password Authentication (that does not work with System.Data.SqlClient) - but that's a different issue....

Also Microsoft.Data.SqlClient is the "new" ".NetCore" way of doing things. Not a huge issue for me right now, but not sure if that would work if we ported to Linux are tried to run in an Azure function.

.Net Core - SQL Server Geography data type exception

0 Answers