dotmorten/microsoft.sqlserver.types

a .NET Standard implementation of the spatial types in `Microsoft.SqlServer.Types`

Notice

This project will slowly start to sunset, as the official Microsoft.SqlServer.Types NuGet package is starting to add .NET 6 support, and bring cross-platform support online. While it isn't quite there just yet, it does mean that efforts in this library will start to diminish, now that we are finally getting the support the types library always needed, and the need for this custom implementation is getting less and less important.

Microsoft.SqlServer.Types

a .NET Standard implementation of the spatial types in Microsoft.SqlServer.Types

Difference between v1.x and v2.x

The v1.x and v2.x versions are identical and kept in sync. The only difference is the dependency:

• v1.x : Depends on the legacy System.Data.SqlClient package.
• v2.x : Depends on updated Microsoft.Data.SqlClient package.

Sponsoring

If you like this library and use it a lot, consider sponsoring me. Anything helps and encourages me to keep going.

See here for details: https://github.com/sponsors/dotMorten

NuGet:

Install the package dotMorten.Microsoft.SqlServer.Types from NuGet.

Examples

Input parameter

Assigning SqlGeometry or SqlGeography to a command parameter:

   command.Parameters.AddWithValue("@GeographyColumn", mySqlGeography);
   command.Parameters["@GeometryColumn"].UdtTypeName = "Geography";

   command.Parameters.AddWithValue("@GeographyColumn", mySqlGeometry);
   command.Parameters["@GeometryColumn"].UdtTypeName = "Geometry" 

The geometry will automatically be correctly serialized.

Reading geometry and geography

Use the common methods for getting fields of specific types:

   var geom1 = reader.GetValue(geomColumn) as SqlGeometry;
   var geom2 = reader.GetFieldValue<SqlGeometry>(geomColumn);
   var geom3 = SqlGeometry.Deserialize(reader.GetSqlBytes(geomColumn)); //Avoids any potential assembly-redirect issue. See https://docs.microsoft.com/en-us/previous-versions/sql/2014/sql-server/install/warning-about-client-side-usage-of-geometry-geography-and-hierarchyid?view=sql-server-2014#corrective-action

Notes:

The spatial operations like intersection, area etc are not included here. You can perform these as part of your query instead and get them returned in a column.

microsoft.sqlserver.types's Issues

Some missing APIs on SqlGeography

I've tried to enable some .net framework tests we have to run under .net core too and found out that there are some missing APIs:

• SqlGeography.GeomFromGml()
• SqlGeography.AsGml()
• SqlGeography.STEquals()

Not sure how it is important, as we don't really need those methods - it's just a part of our test suite

SqlHierarchyId Write/Read roundtrip causes data corruption

Having some trouble dealing with a serialized binary data stream that contains SqlHierarchyId values. They were written to the stream with the Write(BinaryWriter) method, but when read back out with the Read(BinaryReader) method at the other end, the value does not match what was written, and it appears to consume the entire rest of the stream.

Looking at the code, it appears that the Write/Read protocol, and in particular the BitReader class used by the reader is fatally flawed. Just look at the way its constructor assumes the entirety of the stream belongs to it. (And heaven help you if your BinaryReader is attached to a stream for which the Length property throws, such as NetworkStream!)

Simply put, this binary protocol "does not play nice with others" to an absolutely insane degree: what kind of stream only contains one single value?!? Maybe there's some rationale for this I'm missing, but I don't see how this could possibly have ever worked in any binary serialization scenario whatsoever.

Looking at SqlGeometry and SqlGeography, it appears that they're both written with some sort of length prefix, as is appropriate for serializing variable-length data. Why is SqlHierarchyId not done the same way? And how difficult would it be to fix that?

assembly load error

Could not load file or assembly 'Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91

can you modify *.csproj file: add a line:
10.0.0.0

EF Queries are inefficient

A query such as:
from o in _ctx.Organization where (myorg.HierarchyId.GetAncestor(1) == o.HierarchyId).IsTrue select o
results in this SQL:
SELECT [o].[OrganizationId], [o].[HierarchyId], [o].[Name] FROM [Organization] AS [o] ORDER BY [o].[HierarchyId]
instead of:
SELECT [o].[OrganizationId], [o].[HierarchyId], [o].[Name] FROM Organization WHERE @HierarchyId.GetAncestor(1) = [HierarchyId]
In fact, any queries involving the HierarchyId functions result in a table dump.
This is definitely inefficient for a large table.

HierarchyId inconsistency

Hello! Thanks for your hard work! 😄
In real data set I have encountered an hierarchyid like this:

/9138844059576.194933736431247.745612732/136587127227772.29968291099783.2405269301/194815533346310.190518957122630.1754824175/131180557026026.166347272232468.2634227923/112680214461405.155342927909666.4090640326/38488193629220.193847278467647.3890935971/

And I was not able to parse it with SqlHierarchyId implementation in this library

Encountered two blockers:

1. This string cannot be parsed by SqlHierarchyId, because underlying node type is int (PR #33)
2. Cannot parse binary representation because there are no patterns for this case

The real problem is the second blocker.
It seams that current patterns don't implement all cases.

DataReader.GetFieldType returned null

This is basically a copy of #32 . I can provide more info in my case:

.net core 6 project using Microsoft.Data.SqlClient,
Method is:

      public virtual DataTable ExecuteProcedure(string cnnString, string theProcedure, IEnumerable<IDataParameter> theParameters)
       {
           DataTable dt = null;
           using (DataSet ds = new DataSet())
           {
               ds.Locale = System.Globalization.CultureInfo.InvariantCulture;
               using (SqlConnection cnn = new SqlConnection(cnnString))
               {
                   try
                   {
                       cnn.Open();
                       using (SqlCommand cmd = new SqlCommand(theProcedure, cnn))
                       {
                           // cmd.Prepare()	'use for large databases?
                           using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                           {
                               cmd.CommandType = CommandType.StoredProcedure;
                               if (theParameters != null && theParameters.Any())
                               {
                                   foreach (IDataParameter theParam in theParameters)
                                   {
                                           cmd.Parameters.Add(theParam.FormatParamValue());
                                   }
                               }
                               da.Fill(ds);
                           }
                       }
                   }
                   finally
                   {
                       if (cnn != null && cnn.State != ConnectionState.Closed)
                       {
                           cnn.Close();
                       }
                   }
               }
               if (!(ds == null) && !(ds.Tables == null) && (ds.Tables.Count > 0))
               {
                   dt = ds.Tables[0];
               }
           }
           return dt;
       }

if i run this against a stored procedure that's just

create PROCEDURE [dbo].[Organization_List] 
AS
BEGIN
	SET NOCOUNT ON;
SELECT
OrganizationGUID,
OrganizationName,
OrgGeometry
FROM dbo.Organization

END

with a table

CREATE TABLE [dbo].[Organization](
	[OrganizationName] [nvarchar](100) NOT NULL,
	[OrgGeometry] [geometry] NULL,
	[OrganizationGUID] [uniqueidentifier] NOT NULL
GO

and add some fake data

declare @g geometry = geometry::STGeomFromText(
    'POINT (22.9901232886963 87.5953903123242)'
    , 4326);
INSERT INTO Organization (OrganizationName,OrgGeometry,OrganizationGUID) VALUES('Test',@g,newid())

Then you get the error above, with or without installing your package to the application.

NaN Z values fails when not running on .NET Framework

NaN values has for some reason been disallowed outside NETFX, so proper NaN values for Z can't be returned for geometries that otherwise store Z values.

Logged CoreFX issue: https://github.com/dotnet/corefx/issues/29150

NRE in SqlGeography.Null.ToString()

SqlGeography.Null.ToString() throws NRE due to ShapeData._shapes being null:

Result StackTrace:	
at Microsoft.SqlServer.Types.ShapeData.get_Type() in c:\GitHub\Microsoft.SqlServer.Types\src\Microsoft.SqlServer.Types\ShapeData.cs:line 110
   at Microsoft.SqlServer.Types.WktWriter.WriteGeometry(ShapeData geometry, StringBuilder sb, Boolean includeZ, Boolean includeM) in c:\GitHub\Microsoft.SqlServer.Types\src\Microsoft.SqlServer.Types\Wkt\WktWriter.cs:line 41
   at Microsoft.SqlServer.Types.WktWriter.Write(ShapeData g, Boolean includeZ, Boolean includeM) in c:\GitHub\Microsoft.SqlServer.Types\src\Microsoft.SqlServer.Types\Wkt\WktWriter.cs:line 35
   at Microsoft.SqlServer.Types.WktWriter.Write(ShapeData g) in c:\GitHub\Microsoft.SqlServer.Types\src\Microsoft.SqlServer.Types\Wkt\WktWriter.cs:line 22
   at Microsoft.SqlServer.Types.SqlGeography.ToString() in c:\GitHub\Microsoft.SqlServer.Types\src\Microsoft.SqlServer.Types\SqlGeography.cs:line 462
   at Tests.DataProvider.SqlServerTests.TestGeography(String context) in c:\GitHub\linq2db\Tests\Linq\DataProvider\SqlServerTests.cs:line 660
Result Message:	System.NullReferenceException : Object reference not set to an instance of an object.

Could not load file or assembly Microsoft.SqlServer.Types

I am using EF Core 2.2.4. One of the entities contains SqlHierarchyId property. When I perform write operations to the database - adding new entities - it works fine. When I perform read operation, I get the following exception:

Could not load file or assembly 'Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. The system cannot find the file specified.

My guess is when EF attempts to map records to entities containing sqlhierarchyid, it looks for a classic library instead of this one.

Error with GetDescendent

This causes an IndexOutOfRange Exception:

var newSibling = SqlHierarchyId.Parse("/1/").GetDescendant(
		SqlHierarchyId.Parse("/1/1/"),
		SqlHierarchyId.Parse("/1/1.1/")
	)

Throw HierarchyIdException instead of ArgumentException in GetDescendant method

To be compatible with .net framework version, the library should throw HierarchyIdException.

Issues with specific values

There is an issue with specific values. Using the following code in linqpad:

var parsed = HierarchyId.Parse("/1.3.2/").Dump("parsed");
var ms = new MemoryStream();
parsed.Write(new BinaryWriter(ms));
ms.Position = 0;
var roundTrip = new Microsoft.SqlServer.Types.SqlHierarchyId();
roundTrip.Read(new BinaryReader(ms));
roundTrip.Dump("roundTrip");

Input	Value of parsed	Value of roundTrip
/1.0.2/	/1.0.2/	/1.0.2/
/1.1.2/	/1.1.2/	/1.1.2/
/1.2.2/	/1.2.2/	/1.2.2/
/1.3.2/	/1.3.2/	InvalidCastException: No pattern found for: 11010
/3.0/	/3.0/	/

Using Microsoft.SqlServer.Types.SqlHierarchyId (Microsoft.SqlServer.Types, Version=1.1.0.0, Culture=neutral, PublicKeyToken=null) directly in netcore31 (linqpad6) produces the same bad result.

Using Microsoft.SqlServer.Types.SqlHierarchyId (Microsoft.SqlServer.Types, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91) directly in net46+ (linqpad5) works with all tested values as expected.

(via efcore/EFCore.SqlServer.HierarchyId#7)

Loading DataTable

Hi @dotMorten ,
is there any way to load correctly DataTable or DataSet? I tried loading through SqlDataAdapter as well as DataTable.Load() but both fails with DataReader.GetFieldType returned null Exception. I am unaware of any way of setting correct field type before filling the table.

Deserialize to SqlGeometry or SqlGeography

Hi im new to SqlGeography and would like to implement this future to my library
EntityWorker.Core
im having problem saving those value to sqllite and postgress and mysql so i decided to use nvarchar(max)
as column datatype is this good ?
ok i would like to know if there is a way to extract those value from the db as nvarchar and Deserialize to SqlGeometry or SqlGeography

how should i think could you please help

Some Geography cannot be deserialized.

This Geography:

WKBF:
0xE610000002241B0000001C812C5F17D24740C5F8D8708E885EC05C5D8C9918D24740C57CD7938E885EC08462D48918D24740C5406ACA88885EC0BCB3D0DA06D24740C6EAC19B88885EC05C8A60BB06D24740C418796C87885EC04011C2EC09D24740C49C778F87885EC0E82B0ADD09D24740C5348AC885885EC058CB0AD40BD24740C5348AC885885EC0B0C3E2A40BD24740C6B037897D885EC034BDDEC803D24740C47AE44E7D885EC0347926B903D24740C5DAD4CF7E885EC080D9732900D24740C4067FDB7E885EC078ECE34800D24740C4DEB78B81885EC0D4395FF1F3D14740C5580D8081885EC0F8AEF448F2D14740C668A62F83885EC038009C7CF1D14740C4E4D11A87885EC0BCD115DD02D24740C4E4D11A87885EC0DCC1A5BD02D24740C626663389885EC0381B7C9700D24740C5AA645689885EC064323B1EFFD14740C5D255B48A885EC0E4CC5ADFFED14740C4CE653891885EC0AC8514E600D24740C5F8027F92885EC0E8DD4FDF05D24740C4B0A8F392885EC09032301E06D24740C6CD0F4491885EC0E40952CD09D24740C47F645B91885EC014E4E1AD09D24740C5F8D8708E885EC01C812C5F17D24740C5F8D8708E885EC001000000010000000001000000FFFFFFFF0000000003

WKTF:
POLYGON ((-122.13369389713905 47.641338249903328, -122.13370224040689 47.6413757262587, -122.1333490407588 47.64137385244706, -122.13333791672912 47.640834190278355, -122.13326560808042 47.640830442561054, -122.13327395134826 47.640927881965126, -122.13316548821156 47.640926008192594, -122.13316548821156 47.640985970755253, -122.13266210974243 47.640980349278266, -122.13264820396859 47.640740498313249, -122.13273998056964 47.640738624368538, -122.1327427616589 47.64062994152755, -122.13290684658119 47.640633689204094, -122.13290406516451 47.640257045266168, -122.13300696612268 47.640206450914718, -122.13324614045547 47.640182090923588, -122.13324614045547 47.640712390569632, -122.13337407088974 47.640708642898716, -122.13338241415757 47.640643058394232, -122.13346584683593 47.640598086266237, -122.13386354391247 47.64059059079753, -122.13394141473971 47.64065242769297, -122.13396922563248 47.640804208753082, -122.13386632483807 47.640811704191151, -122.13387188734401 47.6409241343097, -122.13369389713905 47.640920386598879, -122.13369389713905 47.641338249903328))

will generate an error

[System.FormatException] One of the identified items was in an invalid format.

HierarchyId serialized in an invalid format which can't be deserialized

Hi,

We've been using this great class for our project and it's saved us a lot of time.

We seem to have found a problem serialising it to a SQL column. We have two values that have been written which fail to deserialize both in the HierarchyId and real HierarchyId (in SQL server and the help class).

The exception thrown by the each of the HierarchyId is when attempting to deserialize is:

$exception | {"No pattern found for: 0000010110000"} | System.InvalidCastException
"No pattern found for: 000001011010110000000"

We're investigating what the hierarchy identifiers were which generated these values but all we currently have is the hierarchy identifier binary information which is: 0x61D500B0 and 0x61D500B580. We'd expect the string value to be something like '/1.-6/-2' or '/1.-6/0/' which does appear to generate the correct binary data and works with both HierarchyId and the real HierarchyId.

I'll update this thread as I find more information. The problem was created by the test team so I'm afraid I don't have much to go on at present.

Thanks

• Andy

Fill DataTable throws DataReader.GetFieldType null

At first I tried referencing your project through nuget but whatever I did, calling Fill on a DataTable always returned a null on the Geometry column, same for a DataReader with GetFieldType. I saw both of these issues here but you couldn't reproduce them, so I thought I would clone your whole repo to see what was going on. The bizarre thing is that it works perfectly in a Test Project, so I stripped the included test project all back to a single test with my code and it still worked, the identical code in my Console app fails as above. I assumed there was something funky with the way your test project was set up so I created a whole new test project, yet again I can debug into the test and it works, but not in the Console app. So, if I take your original solution and create 2 net6.0 projects, one a test and the other a console app, then simply add the project reference, I have 2 different behaviors. I can't explain it. Create a Console Application and add a Project reference to the main Microsoft.SqlServer.Types project and add the following code:

SqlConnection connection = new SqlConnection(".......<your connection string>...");
connection.Open();

string sql = "SELECT * FROM [schema].[table_with_geometry_column]";
SqlCommand command = new SqlCommand(sql, connection);

SqlDataAdapter adapter = new SqlDataAdapter(command);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
connection.Close();

It will throw a "System.InvalidOperationException: 'DataReader.GetFieldType(3) returned null."

Error on the call to Fill, where 3 is the ordinal of my geometry column.

Run this exact same code in a TestMethod and it works as expected. Cannot for the life of me figure out what's happening???

SqlHierarchyId.Null seems to have changed value

Microsoft.SqlServer.Types v12.0.0.0:

SqlHierarchyId.Null = {NULL}

dotMorten/Microsoft.SqlServer.Types:

SqlHierarchyId.Null = {/}

Which in turn means that

SqlHierarchyId.GetRoot().Equals(SqlHierarchyId.Null) == true

which doesn't seem right. It also leads to changes in GetAncestor(n) behaviour for n > GetLevel() because ti doesn't distinguish between this case and the case n=GetLevel() (i.e. root).

GetDescendant for a simple range fails with a HierarchyIdException

The following unit test fails with the current code:

    [TestMethod]
    public void InsertTopLevelNodeBetweenNodes()
    {
        var id = SqlHierarchyId.GetRoot();
        var idChild = id.GetDescendant(SqlHierarchyId.Parse("/0/"), SqlHierarchyId.Parse("/1/"));
        Assert.AreEqual("/0.1/", idChild.ToString());
    }

Add ShortestLineTo and STDistance method to library.

Hi.

You did the great work.
Are you planning to add others methods, for example ShortestLineTo, STDistance?

No automatic deserialization

When trying to read a geometry or geography column, the following exception is thrown:

Instead you have to manually deserialize the object :

var binvalue = reader.GetSqlBytes(rowid);
var g = SqlGeometry.Deserialize(binvalue);

Logged the following CoreFX issue since this seems to be a limitation on UDT types: https://github.com/dotnet/corefx/issues/29139

DataReader.GetFieldType returned null

hi,

Regardless whether I use Microsoft.SQLServer.Types or your implementation, it always give me the "DataReader.GetFieldType returned null" message whenever I query a table with a column as geography type, even there is NO data in the result.

Could you please shed some light on this?

Thanks,
Wilson

MultiPoint issue?

The WKT's aren't equal for this example, MultiPoint // double parenthesis replaced with single

Not shown in the Test, we use SQLGeometry.Write function to get the a binary rep to write to SQL Server... which errors with the below screenshot invalid format.

Both representations seem valid in SQL Server via SSMS so not ready sure if i'm on to the right problem yet.

[TestMethod] public void TestMultipoint() { string wkt = "MULTIPOINT((-78.21 43.869))"; var k = SqlGeometry.Parse(wkt); var valueWKT = k.STAsText().ToSqlString(); Assert.AreEqual(wkt, valueWKT); }

Unable to load DLL 'SqlServerSpatial140.dll' or one of its dependencies - on linux server - docker container

I am using the 14.0.1016.290 version of the package on .NET core and I have fixed the problem locally (on a windows machine) by copying SqlServerSpatial140.dll from the temporary package folder to the root of my project, and set the property 'Copy to Output Directory' to 'Copy always'.

However, now I am trying to deploy the build to a linux alpine server running a docker container and I am getting the error 'Unable to load DLL 'SqlServerSpatial140.dll' or one of its dependencies' . The dll is on the same location on the server as it is on the local machine build folder.

Is the package compatible with linux? Do I have to move it in a different/new folder?

Missing API: SqlGeography.Parse() and SqlGeometry.Parse()

During migration of our tests to support this project, I discovered that SqlGeography.Parse() and SqlGeometry.Parse() APIs are missing. Any plans to add them?

SqlGeography support for FULLGLOBE?

Are there plans to support creating a SqlGeography of "FULLGLOBE"?

A sample test method

[TestMethod]
public void FullGlobe() {
    var wkt = "FULLGLOBE";
    var value = SqlGeography.STGeomFromText(new SqlChars(new SqlString(wkt)), 4326);
    Assert.IsNotNull(value);
}

this passes in the RefTests but fails with an "invalid well-known text" when using this library.

Add support for Curve types / v2 support

v2 adds an optional segments part:

Segments (optional) (1 * Number of Segments bytes) (variable): In version 2 of the serialization format, a sequence of segment structures

Could not load file or assembly Microsoft.SqlServer.Types

I'm running into a problem when trying to use this library. Hopefully it's just something simple I'm overlooking.

As the simplest possible example, here is a .NET 5.0 console app:

SqlTest.csproj

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net5.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Dapper" Version="2.0.90" />
    <PackageReference Include="dotMorten.Microsoft.SqlServer.Types" Version="1.3.0" />
    <PackageReference Include="Microsoft.Data.SqlClient" Version="2.1.3" />
  </ItemGroup>

</Project>

Program.cs

using System;
using Dapper;
using Microsoft.Data.SqlClient;

namespace SqlTest
{
    class Program
    {
        static void Main(string[] args)
        {
            var conn = new SqlConnection("Server=localhost;Database=AdventureWorks;Trusted_Connection=True;");

            var addresses = conn.Query("SELECT TOP 10 * FROM Person.Address");

            foreach (var address in addresses)
            {
                Console.WriteLine(address);
            }

            conn.Dispose();
        }
    }
}

I'm querying AdventureWorks's Person.Address, since the SpatialLocation field in it is of type "geography". When trying this on a different table without geography (e.g. AddressType), the program runs without exception. The exception that I'm getting is:

Unhandled exception. System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. The system cannot find the file specified.
File name: 'Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'
at System.Reflection.RuntimeAssembly.InternalLoad(ObjectHandleOnStack assemblyName, ObjectHandleOnStack requestingAssembly, StackCrawlMarkHandle stackMark, Boolean throwOnFileNotFound, ObjectHandleOnStack assemblyLoadContext, ObjectHandleOnStack retAssembly)
at System.Reflection.RuntimeAssembly.InternalLoad(AssemblyName assemblyName, RuntimeAssembly requestingAssembly, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, AssemblyLoadContext assemblyLoadContext)
at System.Reflection.Assembly.Load(AssemblyName assemblyRef)
at Microsoft.Data.SqlClient.SqlConnection.ResolveTypeAssembly(AssemblyName asmRef, Boolean throwOnError)
at Microsoft.Data.SqlClient.SqlConnection.<>c__DisplayClass179_0.b__0(AssemblyName asmRef)
at System.TypeNameParser.ResolveAssembly(String asmName, Func2 assemblyResolver, Boolean throwOnError, StackCrawlMark& stackMark) at System.TypeNameParser.ConstructType(Func2 assemblyResolver, Func4 typeResolver, Boolean throwOnError, Boolean ignoreCase, StackCrawlMark& stackMark) at System.TypeNameParser.GetType(String typeName, Func2 assemblyResolver, Func4 typeResolver, Boolean throwOnError, Boolean ignoreCase, StackCrawlMark& stackMark) at System.Type.GetType(String typeName, Func2 assemblyResolver, Func4 typeResolver, Boolean throwOnError) at Microsoft.Data.SqlClient.SqlConnection.CheckGetExtendedUDTInfo(SqlMetaDataPriv metaData, Boolean fThrow) at Microsoft.Data.SqlClient.SqlDataReader.GetValueFromSqlBufferInternal(SqlBuffer data, _SqlMetaData metaData) at Microsoft.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i) at Microsoft.Data.SqlClient.SqlDataReader.GetValue(Int32 i) at Dapper.SqlMapper.<>c__DisplayClass171_0.<GetDapperRowDeserializer>b__0(IDataReader r) in /_/Dapper/SqlMapper.cs:line 1877 at Dapper.SqlMapper.QueryImpl[T](IDbConnection cnn, CommandDefinition command, Type effectiveType)+MoveNext() in /_/Dapper/SqlMapper.cs:line 1102 at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source)
at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable1 commandTimeout, Nullable1 commandType) in //Dapper/SqlMapper.cs:line 725
at Dapper.SqlMapper.Query(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable1 commandTimeout, Nullable1 commandType) in //Dapper/SqlMapper.cs:line 652
at SqlTest.Program.Main(String[] args) in c:\zproj\SqlTest\SqlTest\Program.cs:line 13

c:\zproj\SqlTest\SqlTest\bin\Debug\net5.0\SqlTest.exe (process 4288) exited with code -532462766.
Press any key to close this window . . .

When I instead use System.Data.SqlClient and Microsoft.SqlServer.Types:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net5.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Dapper" Version="2.0.90" />
    <PackageReference Include="Microsoft.SqlServer.Types" Version="14.0.1016.290" />
    <PackageReference Include="System.Data.SqlClient" Version="4.8.2" />
  </ItemGroup>

</Project>

I do not get an exception. Instead, I get the warning:

Warning NU1701 Package 'Microsoft.SqlServer.Types 14.0.1016.290' was restored using '.NETFramework,Version=v4.6.1, .NETFramework,Version=v4.6.2, .NETFramework,Version=v4.7, .NETFramework,Version=v4.7.1, .NETFramework,Version=v4.7.2, .NETFramework,Version=v4.8' instead of the project target framework 'net5.0'. This package may not be fully compatible with your project. SqlTest c:\zproj\SqlTest\SqlTest\SqlTest.csproj 1

...which is what lead me to seek out this project in the first place. (Although it seems to work fine on Windows, despite the warning, it may be causing problems on Apple computers--I can't 100% verify this yet).

The error message seems to indicate that the file can't be found, but it does appear to be there in the bin folder. Am I configuring something wrong?

ToString() of SqlHierarchyId does not return "NULL" when IsNull == true

SqlHierarchyId.Null.ToString() should return the string "NULL" and not "/".

SqlGeography.ToString() returns unexpected results

Value: SELECT Cast(geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326) as geography)

  Expected (official Microsoft.SqlServer.Types
 package): "LINESTRING (-122.36 47.656, -122.343 47.656)"
  But was:  "LINESTRING(47.656 -122.36,47.656 -122.343)"

Similar issue for SqlGeometry (slightly different formatting):

  Expected: "LINESTRING (100 100, 20 180, 180 180)"
  But was:  "LINESTRING(100 100,20 180,180 180)"

When using Microsoft.Data.SqlClient an InvalidCastException will be thrown

When using Microsoft.Data.SqlClient 2.0.1 the following exception will be generated

[System.InvalidCastException] Unable to cast object of type 'Microsoft.SqlServer.Types.SqlHierarchyId' to type 'Microsoft.Data.SqlClient.Server.IBinarySerialize'.
once proper assembly redirection is in place

 if (assemblyName.FullName == "Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
            {
                var a = typeof(SqlGeography).Assembly;
                _logger.Info("Rebinding to " + a.FullName);
                return a;
            }

SqlHierarchyId support?

Hi!
Do you plan to support SqlHierarchyId, too?
Thanks,
Karlo

Doc reference: https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.types.sqlhierarchyid.aspx

[SqlUserDefinedTypeAttribute(Format.UserDefined, IsByteOrdered = true, 
	MaxByteSize = 892, Name = "SqlHierarchyId")]
public struct SqlHierarchyId : IBinarySerialize, INullable, IComparable

This type provides the following benefits for storing hierarchical information:

• Very compact data storage.
• Depth-first comparison. Indexes on this type are in depth-first order, and nodes close to each other in a depth-first traversal are stored near each other.
• Support for arbitrary insertions and deletions.
• A limitation of this type is that a single instance of the hierarchy data type can be no larger than 892 bytes. Hierarchies that possess too many levels to fit within this limitation must use a different data type.

Example

SELECT @employee = OrgNode FROM HumanResources.EmployeeDemo
  WHERE LoginID = 'adventure-works\dylan0'

SELECT * FROM HumanResources.EmployeeDemo
WHERE @employee.IsDescendantOf(OrgNode) = 1

Example for creating hierarchy table: https://technet.microsoft.com/en-us/library/bb630263(v=sql.110).aspx

SqlGeography.ToString() produce incorrect value

Thanks for your fixes! Our tests work now except one case below:

  Expected: "LINESTRING (-122.36 47.656, -122.343 47.656)"
  But was:  "LINESTRING (47.656 -122.36, 47.656 -122.343)"

[Test, IncludeDataContextSource(ProviderName.SqlServer2008, ProviderName.SqlServer2012, ProviderName.SqlServer2014, TestProvName.SqlAzure)]
		public void Test4(string context)
		{
			using (var conn = new DataConnection(context))
			{
				using (var cmd = conn.CreateCommand())
				{
					cmd.CommandText = "SELECT Cast(geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326) as geography)";

					Assert.That(cmd.ExecuteScalar().ToString(), Is.EqualTo("LINESTRING (-122.36 47.656, -122.343 47.656)"));
				}
			}
		}

HierarchyId >= operator typo

The underlying operator for >= is using <=

Microsoft.SqlServer.Types/src/Microsoft.SqlServer.Types/SqlHierarchy/HierarhyId.cs

Line 368 in 6411231

public static bool operator >=(HierarchyId hid1, HierarchyId hid2) => Compare(hid1, hid2) <= 0;

it should be
public static bool operator >=(HierarchyId hid1, HierarchyId hid2) => Compare(hid1, hid2) >= 0;

Implement all *FromText methods

Implement the geometry-type-specific FromText methods:

static STPointFromText(SqlChars pointTaggedText, int srid)
static STLineFromText(SqlChars lineStringTaggedText, int srid)
static STPolyFromText(SqlChars polygonTaggedText, int srid)
static STGeomCollFromText(SqlChars geometryCollectionTaggedText, int srid)

static STMPointFromText(SqlChars multiPointTaggedText, int srid)
static STMLineFromText(SqlChars multiLineStringTaggedText, int srid)
static STMPolyFromText(SqlChars multiPolygonTaggedText, int srid)

Works like STGeomFromText but throws format exception if the geometry type doesn't match. STM methods parses the 3rd coordinate as an M value instead of Z.

Potential updated release?

I've been porting my application to .NET Core 3.1 and am using this package in lieu of the official SqlServer.Types package that is not .NET Standard compliant. I was running into a problem where I would get a "FormatException" error saying "Invalid Well-known Text" when trying to create a SqlGeometry like this:

var wkt = "POINT(29.41824 45.99194)";
var value = SqlGeometry.STGeomFromText(new SqlChars(new SqlString(wkt)), 4326);

After various troubleshooting and digging around, I ended up pulling down the source code, including the project directly in my solution instead of using the nuget package, and my code worked. Looking through the commit history it looks like there were some changes (and other improvements?) since the 1.1 release in 2018.

Are there any plans to provide an updated release, possibly even compiled to .NET Standard 2.1? Or, given my example above, what should the correct syntax be to create a SqlGeometry from a WKT string?

Thanks,

SqlGeography.ReorientObject() and .STArea()

Hi!
I have a project that works locally with SqlGeography. I'd like to try port it to this lib, but the methods SqlGeography.ReorientObject() and .STArea() aren't implemented. Do you plan to add support for all public members present in the original SqlGeography class, or extend it as needed?

Thanks

GetAncestor() fails when querying top-level node

I believe there is a problem with the implementation of HierarchyId/SqlHierarchyId.
On this line, the condition result is too severe, resulting in thrown exceptions during EF queries.
The exception, also, is not very intuitive and is hard to see. It just says:
"ArgumentException: n"

For example, a query that finds the children of a parent:
IQueryable<employee> children = from e in _ctx.employees where (e.HierarchyId.GetAncestor(1) == parent.HierarchyId).IsTrue select e;
it will except/throw and the query will not run.

I believe the correct solution is to return null (changing the signature of the function to return a Nullable.
The only consequence I see, is that this line needs to change to:
HierarchyId? retVal = _imp.GetAncestor(n); return retVal.HasValue ? new SqlHierarchyId(retVal.Value) : Null;
I believe this is correct because I don't know where this source code came from and whether there is a fundamental constraint in the library I am not understanding.

I don't have a unit test that is easy to give to you as you have no EF tests to begin with.
You may want to incorporate some EF tests and run a similar query as above.

Strong name?

I'm getting an error when trying to use this library:

FileLoadException: A strongly-named assembly is required. (Exception from HRESULT: 0x80131044)

The listing for the NuGet package indicates that the assembly is strong name signed, but when I pull out the assembly and look at it in ILSpy, it doesn't appear to be.

System.Data.SqlClient needs a version bumb from 4.5.1 to 4.8.3

Microsoft.SqlServer.Types/src/Microsoft.SqlServer.Types/Microsoft.SqlServer.Types.csproj

Line 25 in 02646fb

<Version>4.5.1</Version>

SqlHierarchy doesn't produce child ids properly

I think this line needs to change to:
public SqlHierarchyId GetDescendant(SqlHierarchyId child1, SqlHierarchyId child2) => new SqlHierarchyId(_imp.GetDescendant(child1.IsNull ? default(HierarchyId?) : child1._imp, child2.IsNull ? default(HierarchyId?) : child2._imp));
Otherwise, you can't create the first child. Unless I'm missing something...

Inconsistent usage of FigureAttributes

The current FigureAttributes is inconsistently used and mixes V1 and V2 values.
For example WktReader.ReadPolygon uses the V1 value FigureAttributes.ExteriorRing and WktReader.ReadPoint uses the V2 value FigureAttributes.Point.

This leads to incorrect byte representation in the Serialize function.

Referenced assembly 'Microsoft.SqlServer.Types, Version=2.5.0.0, Culture=neutral, PublicKeyToken=null' does not have a strong name

I have this error when use Microsoft.SqlServer.Types in a Nuget package dll and after use the Nuget Package in WPF app:

Could not load file or assembly Microsoft.SqlServer.Types, Version=2.5.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. Does not have a strong name. (Exception from HRESULT: 0x80131044)

My Nuget package dll and WPF app have a strong name key file (snk).

default(SqlHierarchyId) does not set IsNull property to true

I noticed that the default value of the dotMorten SqlHierarchyId struct differs from Microsoft's original SqlHierarchyId implementation in the point that the default(SqlHierarchyId) value of the drop-in is not NULL (e.g. IsNull == false) while the original implementation produces a NULL value as default-value.

@olmobrutall Maybe you can comment on this? In the original impl OrdPath (what is HierarchyId in this project) is a class and therefor can be null.

Separate build against Microsoft.Data.SqlClient?

Recently I tried to run Microsoft.SqlServer.Types against new SqlClient and found out that it is not possible due to fact that Microsoft re-declared attributes like SqlUserDefinedTypeAttribute in new provider. See more details here.

Don't know if anybody will want to use Microsoft.SqlServer.Types with new provider (we are not end-users), so I propose to use this issue to see if it is a wanted feature.

Nuget package?

I'm currently migrating https://github.com/signumsoftware/framework to .Net Core, this project uses SqlGeometryId and SqlHierarchyId (I made a pull request to add support), so I would like to take a dependency to this project.

Is there a nuget package?

If not, could you create one, or should I?

Add .STAsBinary method to library

I'm trying to port over a .NET Framework class library to .NET Standard. I'm using your package for Microsoft.SqlServer.Types, as the Microsoft package doesn't support .NET Standard. However, the STAsBinary method seems to be missing from your implementation:

value = ToHexString(((SqlGeography)parameter.Value).STAsBinary().Value);

Specified type is not registered on the target.

I tried to move from the the Microsoft.SqlServer.Types to this library. It successfully complied but when I trued to Insert with IDbCommand the value with SqlGeography instance I get:
System.ArgumentException : Specified type is not registered on the target server.Microsoft.SqlServer.Types.SqlGeography, Microsoft.SqlServer.Types, Version=2.5.0.0, Culture=neutral, PublicKeyToken=null.

How it could be fixed?

Usage with SqlBulkCopy

Thanks for your great work! I've managed to write into DB with SqlBulkCopy, using the following:

table.Columns.Add("Geocode", typeof(byte[]));

and then in Rows.Add:

SqlGeography.Point(location.Latitude.Value, location.Longitude.Value, 4326).Serialize().Value

is this how it's supposed to work? Seems a bit roundabout, but it works. If yes, then perhaps this would be useful to include in the Readme.md?

SqlHierarchyId Read() does not reset IsNull property to false

The following unit test fails:

SqlHierarchyId z = SqlHierarchyId.Null;
z.Read(new BinaryReader(new MemoryStream(Convert.FromBase64String("P6T6"))));
Assert.False(z.IsNull);  // IsNull property should now be false

I noticed this problem porting old deserialization code to dotnet core. I am pretty sure the original SqlHierarchyId implementation resets the IsNull value.