Implement SqlHierarchyId type for .NET Core

Ansssss commented 4 years ago

Microsoft SQL Server has a hierarchyid data type defined since SQL Server 2008. In the .NET Framework there is a nuget to support using the SqlHierarchyId struct: Microsoft.SqlServer.Types. Unfortunately that nuget is built for the .NET Framework and not .NET Core. If you add that nuget to a .NET Core project, the following compiler warning is produced:

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 '.NETStandard,Version=v2.0'. This package may not be fully compatible with your project.

There should be an implementation of SqlHierarchyId for .NET Core.

I tried using the Microsoft.Data.SqlClient nuget, but that does not have the SqlHierarchyId defined. My current workaround is to use the dotMarten.Microsoft.SqlServer.Types nuget.

Related issues (which I don't think cover this same problem):

https://github.com/dotnet/SqlClient/issues/30 - but that one only applies to the SqlGeography/spatial types (which are also currently defined in Microsoft.SqlServer.Types).
https://github.com/dotnet/SqlClient/issues/17 - requests more stuff in Microsoft.Data.SqlClient, but I don't see that it would add SqlHierarchyId.

cheenamalhotra commented 4 years ago

Thanks for filing a separate issue on this one. We'll consider implementing it sometime in future.

JohnyL commented 4 years ago

Are there any news on SqlHierarchyId?

cheenamalhotra commented 4 years ago

@JohnyL

This isn't in our primary targets right now. We'll get to this when we have priority support addressed and have some room.

brumlemann commented 4 years ago

Any update on this? It is sorely needed

yurart commented 4 years ago

Yeah, I also wait for this feature. Hope it will be implemented soon.

alexey-kachalov commented 4 years ago

Any plans to implement this?

cheenamalhotra commented 4 years ago

Hi guys!

Have you tried using https://www.nuget.org/packages/Microsoft.SqlServer.DACFx that now supports Microsoft.Data.SqlClient (starting from v150.4769.1) and implements SqlHierarchyId for .NET Core, .NET Framework and .NET Standard?

If you check the "Package" defined in the .NET API reference docs,, this type in Microsoft.SqlServer.Types namespace is documented from this DACFx NuGet package.

It also seems to implement SqlGeography and SqlGeometry for all .NET platforms. (as requested in #30)

ErikEJ commented 4 years ago

@cheenamalhotra I think it is not the cross platform package, but the old NetFX one.

JohnyL commented 4 years ago

@cheenamalhotra I guess, if it'd be cross-platform (as ErikEJ said), you wouldn't bother mention it.

cheenamalhotra commented 4 years ago

I think I missed out on internal APIs and only saw class reference. So even though these classes are available in .NET Core, further APIs aren't. But the fact that this NuGet is actively updated, makes me think if there's a plan ahead for DACFx to support this fully in future.

ErikEJ commented 4 years ago

Maybe the closed source DacFx team would be willing to share their plans in the open?

ppasieka commented 4 years ago

There are few variants of the Microsoft.SqlServer.DACFx package:

Microsoft.SqlServer.DACFx -> very lightweight. A lot of things are missing. After decompiling, you can find a few "Not implemented" statements
Microsoft.SqlServer.DACFx.x64 -> that one actually looks good, but after running it against a database with existing data we are getting a lot of InvalidOperations exceptions on SqlGeography type. The same data are working fine with classical .net framework implementation of the SqlGeography

Anyway I'm getting this:

[NU1701] Package 'Microsoft.SqlServer.DacFx.x64 150.4826.1' 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 '.NETCoreApp,Version=v3.1'. 
This package may not be fully compatible with your project.

so as @ErikEJ mentioned the packages does not seem to be cross-platform

vyrotek commented 3 years ago

I can't upvote this enough!

Not having .NET Core support for this and many other types from Microsoft.SqlServer.Types is a huge miss will most definitely impact .NET Core adoption.

The current state of things is disappointing and a really poor dev experience. Applications with these dependencies are limping along but only thanks to a handful of amazing OSS contributors.

codeputer commented 3 years ago

I'm confused as to why this isn't more of a priority under .Net 5? SQL Server is likely the most used data persistence service under .NET 5 - SQL Hierarchy Id is BUILD INTO FileTable, and FileStream functionality. To use those features means that we are limited to .net 4.8 ??? I would think that SQL types used in SQL server would be supported in SQL Server latest rev? I guess we could create a small micro service, where that service is 4.8 - but that would still require serialization to something that can be recognized in .NET 5.0. mmm... this just gets harder and harder doesn't it.

I could also advise the client that we drop support for SQL Server, as the language no longer supports the features.. but that seems like a huge hit for what I believe an effort by the team to support this type in .net core.

David-Engel commented 3 years ago

@codeputer The problem is that SqlHierarchyId isn't really owned by SqlClient. It's owned by Microsoft.SqlServer.Types. We (the SqlClient team) are just kind of the proxy representing desire for support since the other library doesn't have a public repo and we do feed that info to the appropriate people. I also know that it is a priority in MS and that work has started to create a .NET Core/.NET 5 compatible Microsoft.SqlServer.Types package. I don't know any timelines, though.

codeputer commented 3 years ago

I get it and totally understand – but my customer doesn’t care I’m afraid.

They are using FileStream and FileTable in their newly designed system, and they have decided to use .Net Core 5.0. What am I to tell them, that SQL Client isn’t owned by SQL Server?

I face the customer, not the technology I’m afraid.

Regards, Richard

From: David Engel @.> Sent: March 30, 2021 1:22 PM To: dotnet/SqlClient @.> Cc: Richard Reukema @.>; Mention @.> Subject: Re: [dotnet/SqlClient] Implement SqlHierarchyId type for .NET Core (#322)

@codeputerhttps://github.com/codeputer The problem is that SqlHierarchyId isn't really owned by SqlClient. It's owned by Microsoft.SqlServer.Typeshttps://www.nuget.org/packages/Microsoft.SqlServer.Types. We (the SqlClient team) are just kind of the proxy representing desire for support since the other library doesn't have a public repo and we do feed that info to the appropriate people. I also know that it is a priority in MS and that work has started to create a .NET Core/.NET 5 compatible Microsoft.SqlServer.Types package. I don't know any timelines, though.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/dotnet/SqlClient/issues/322#issuecomment-810516964, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AAEVJVKTKYXKZPKLLWAPVKTTGIQF5ANCNFSM4JPXRFOQ.

vyrotek commented 3 years ago

I also know that it is a priority in MS and that work has started to create a .NET Core/.NET 5 compatible Microsoft.SqlServer.Types package. I don't know any timelines, though.

That's really great to hear @David-Engel!

The lack of .NET Core support has impacted us in many places we didn't anticipate and we suspect it's holding back many other efforts. Most recently with Azure DevOps and not being able to build VS Database Projects on Linux pipeline instances. Some work was done in VS Code for DB Projects which appears to be cross-plat compatible but there appears to be hidden internals fetching the dlls required to make that work and no clear instructions on how to automate this.

David-Engel commented 3 years ago

What am I to tell them, that SQL Client isn’t owned by SQL Server?

@codeputer I didn't say that nor did I mean to imply it. SqlClient is owned by the Azure Data organization, which encompasses many things, including SQL Server, Azure SQL offerings, and a lot of SQL-related tools and libraries.

I was responding to your comment in the context of this repo, which only hosts the SqlClient library. There are many repos for "SQL"-related things and this repo is not the forum for all SQL Server feedback. (That's aka.ms/sqlfeedback, btw.) This issue only remains open because we (the SqlClient team) feel like it impacts us and our users so we wanted to track it closely as a way to let our users know when it is available. I was just trying to clarify that SqlHierarchyId support for .NET 5 / .NET Core is out of our team's control, while also sharing the information we know - that it's being actively worked on.

As for what to tell your customers, I can sympathize. This is a public forum. The current state of things are what they are. We are trying to improve things and share what we can. They will have to make the best decisions for their business given the information at hand.

If they are looking for options, they can wait, stick with .NET Framework for a little while longer, or they can investigate dotMorten.Microsoft.SqlServer.Types, which is a .NET Standard, 3rd party implementation that includes SqlHierarchyId. (Note: I know nothing about that implementation but if it is API-equivalent and good-enough-for-them-now in terms of the specific functionality it supports, it could probably be swapped out for a Microsoft version later.)

ErikEJ commented 3 years ago

@vyrotek For building .dacpacs with Linux, have a look at this project: https://github.com/rr-wfm/MSBuild.Sdk.SqlProj

https://erikej.github.io/efcore/2020/05/11/ssdt-dacpac-netcore.html

codeputer commented 3 years ago

I apologize for what I feel is my response to you and this forum. It'swas a frustrating day for me, of which I should have not expressed here in any way.

That said, why this type was not included is very concerning to me. The client is following my recommendation in term of. Net 5 and SQL Server, and that enhanced my anxiety to solve this issue.

We are leveraging FILETABLE, and that uses this type in its design.

Thanks, and again I apologize.

Richard Reukema

Get Outlook for Androidhttps://aka.ms/AAb9ysg

From: David Engel @.> Sent: Tuesday, March 30, 2021 3:05:51 PM To: dotnet/SqlClient @.> Cc: Richard Reukema @.>; Mention @.> Subject: Re: [dotnet/SqlClient] Implement SqlHierarchyId type for .NET Core (#322)

What am I to tell them, that SQL Client isn’t owned by SQL Server?

@codeputerhttps://github.com/codeputer I didn't say that nor did I mean to imply it. SqlClient is owned by the Azure Data organization, which encompasses many things, including SQL Server, Azure SQL offerings, and a lot of SQL-related tools and libraries.

I was responding to your comment in the context of this repo, which only hosts the SqlClient library. There are many repos for "SQL"-related things and this repo is not the forum for all SQL Server feedback. (That's aka.ms/sqlfeedbackhttps://aka.ms/sqlfeedback, btw.) This issue only remains open because we (the SqlClient team) feel like it impacts us and our users so we wanted to track it closely as a way to let our users know when it is available. I was just trying to clarify that SqlHierarchyId support for .NET 5 / .NET Core is out of our team's control, while also sharing the information we know - that it's being actively worked on.

As for what to tell your customers, I can sympathize. This is a public forum. The current state of things are what they are. We are trying to improve things and share what we can. They will have to make the best decisions for their business given the information at hand.

If they are looking for options, they can wait, stick with .NET Framework for a little while longer, or they can investigate dotMorten.Microsoft.SqlServer.Typeshttps://www.nuget.org/packages/dotMorten.Microsoft.SqlServer.Types/, which is a .NET Standard, 3rd party implementation that includes SqlHierarchyId. (Note: I know nothing about that implementation but if it is API-equivalent and good-enough-for-them-now in terms of the specific functionality it supports, it could probably be swapped out for a Microsoft version later.)

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/dotnet/SqlClient/issues/322#issuecomment-810577349, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AAEVJVOWTL7MRUJLYZ5PTWDTGI4K7ANCNFSM4JPXRFOQ.

JohnyL commented 3 years ago

@codeputer We're all frustrated. I think that such fundamental thing must be realized in the early days of .NET Core. The priorities have changed, and this was not on the top of the drawer.

Wraith2 commented 3 years ago

If you just want hierarchyid it's specified in https://docs.microsoft.com/en-us/openspecs/sql_server_protocols/MS-SSCLRT/77460aa9-8c2f-4449-a65e-1d649ebd77fa and it's only a page so it shouldn't be that hard to implement a basic form. I'd attempt it but I've never seen or used the type and I wouldn't be able to write conformance tests even if I managed to deconstruct it correctly.

ststeiger commented 3 years ago

2021 approaching 3/4 and still ... just speechless. Well, sql-server geography&geometry support has always been shaky to phoney. Try to make a polygon that spans across the equator, for example. That whole thing as a .NET extension type. If you need geometry&geography, you're better of with PostgreSQL and PostGis anyway. OpenStreetMap has noticed that a long time ago. Sad for FileStream, but the same could be said about it. You can use PostgreSQL's 3rd party bfile implementation to replace FileStream.

Wraith2 commented 3 years ago

I don't use hierarchy id's so I don't have a need to add them but I could because the code is right here. If you do need them what's your reason for not adding them?

klemmchr commented 3 years ago

If you do need them what's your reason for not adding them?

Because it is expected that the official SqlClient has the same feature set support as Sql Server. I could accept that the support for Hierarchy Ids would be shady in some Ruby package or some other third party language. However, we're talking about the ecosystem that Microsoft promotes. How can it be that such features are missing, then are being added by some humble open source dev that are being picked up by the ef core team afterwards? This reads like a joke.

Wraith2 commented 3 years ago

Because it is expected that the official SqlClient has the same feature set support as Sql Server.

It doesn't. It seems best not to expect that. As I found recently you can't even get the collation of a column through this driver without writing the SQL query to do it and I'd have thought was possible. I checked, it's not possible, I can add that functionality.

How can it be that such features are missing

The effort required to port the feature from the native implementation to a managed implementation that can be used cross platform doesn't seem to have been judged to be worth the time investment it requires. So even though the feature is important to you it doesn't seem to be important to the overall ecosystem. Having read through the spec I can see why no-one would want to implement it.

This reads like a joke.

Not really. It reads like an under-resourced ream not having time or developers to spare to to implement a relatively niche feature. At 53 upvotes at the time of writing it's pretty well liked for an issue in this quite low traffic repo so it might see some work eventually, we'll see.

dotnet / SqlClient

Implement SqlHierarchyId type for .NET Core #322