Edmx and Microsoft.Data.SqlClient result in HierarchyId invalid error

[cveld]

Describe the bug

In order to integrate the model first edmx entity framework 6 experience with Microsoft.Data.SqlClient we changed the value of the Provider attribute on the Schema element to Microsoft.Data.SqlClient. As per the instructions on https://github.com/ErikEJ/EntityFramework6PowerTools/blob/community/src/ErikEJ.EntityFramework.SqlServer/readme.md

But now the designer reports the following error:

<File Unknown>(167,34) : error 0005: The 'PrimitiveTypeKind' attribute is invalid - The value 'HierarchyId' is invalid according to its datatype 'http://schemas.microsoft.com/ado/2006/04/edm/providermanifest:TPrimitiveTypeKind' - The Enumeration constraint failed.

<File Unknown>(167,10) : error 0122: The value 'HierarchyId' is not a valid PrimitiveTypeKind.    ConsoleApp1    C:\work\git\ConsoleApp1\Model.edmx    7    

Tested with .NET Framework 4.8.
Package version 5.1.1.

This error isn't blocking the designer, but whenever we do any database operations through the user experience, this error is blocking the process.

To reproduce

You can clone the following solution: https://github.com/cveld/sqlclient-hierarchyid-edmx-designer-error
Open up the solution file in Visual Studio and then double click the edmx file in order to kick off the designer. You will see the reported error message in the error list dialog.

Expected behavior

The error shouldn't be present and the database features should be working as designed.

Further technical details

Microsoft.Data.SqlClient version: 5.1.1
.NET target: Framework 4.8
SQL Server version: Azure Sql Database
Operating system: Windows 11
Visual Studio: 17.5.4

Additional information

We initially reported the issue to ErikEJ/EntityFramework6PowerTools#124 but @ErikEJ does not have any visibility or responsibility towards the Visual Studio side of things.

[ErikEJ]

@cveld I think you need to report this here: https://developercommunity.visualstudio.com/home - this is not something the SqlClient team can fix.

[cveld]

@ErikEJ Thanks! I did so: https://developercommunity.visualstudio.com/t/Edmx-and-MicrosoftDataSqlClient-errors/10347962

So if I understand correctly, there is some internal implementation within Visual Studio that triggers this error? Why is it only happening in combination with the provider Microsoft.Data.SqlClient? Why is there no error when having selected System.Data.SqlClient?
Is there any file on this disk either xsd or .NET assembly I could patch to unblock myself from this error?

[ErikEJ]

No idea

[dirkymelon]

@cveld

Windows 10E Version 21H2 Build 19044.2846
Visual Studio 2019 Enterprise 16.11.26
.NET Framework 4.8.09037
EntityFramework 6.4.4
ErikEJ.EntityFramework.SqlServer 6.6.5
Microsoft.Data.SqlClient 5.1.1

*You will need to modify the pathing/insertion points as needed for VS2017 and your subscription level.

1. Close all VS instances.

2. Launch CLI with elevated permissions and do the following:

cd C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\Common7\IDE
:: dlls are 6.2.0.  Disassembly reveals that the embedded ProviderManifest xsd does not support the HierarchyId datatype. 
rename EntityFramework.dll EntityFramework.dll.og
rename EntityFramework.SqlServer.dll EntityFramework.SqlServer.dll.og
copy "C:\Program Files (x86)\Microsoft Visual Studio\Shared\Packages\EntityFramework.6.4.4\lib\net45\EntityFramework.dll"
copy "C:\Program Files (x86)\Microsoft Visual Studio\Shared\Packages\EntityFramework.6.4.4\lib\net45\EntityFramework.SqlServer.dll"
:: copy Microsoft.Data.SqlClient.dll from your project's packages\Microsoft.Data.SqlClient.5.1.1\lib\net462\ folder to the current folder

3. Launch an editor with elevated permissions, e.g. Notepad++, and do the following:

   • Open C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\Xml\Schemas\System.Data.Resources.ProviderServices.ProviderManifest.xsd
   • Insert the following at line 125 (under <xs:simpleType name="TPrimitiveTypeKind"><xs:restriction base="xs:string">):
     <xs:enumeration value="HierarchyId" />
   • Save and then close the editor unless performing the next set of steps.

4. (Optional) Prior to these steps, we were seeing this error when opening the EDM Designer and when rebuilding the solution: "Error 175: The ADO.NET provider with invariant name 'Microsoft.Data.SqlClient' is either not registered in the machine or application config file, or could not be loaded. See the inner exception for details." Curious that you are not.
   Using your editor, do the following:

   • Open C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\Common7\IDE\devenv.exe.config
   • Insert the following at line 1397 (under <system.data>):
     <remove invariant="Microsoft.Data.SqlClient" />
<add name="SqlClient Data Provider" invariant="Microsoft.Data.SqlClient" description=".NET Framework Data Provider for SqlServer" type="Microsoft.Data.SqlClient.SqlClientFactory, Microsoft.Data.SqlClient" />
   • Save

5. Open your solution in VS. Rebuild all. Open an edmx file.

Please tell us about your experience including any deviations or anomalies. If this solves your issue, please re-post the revised solution to Issue 10347962.

[ErikEJ]

@dirkymelon Interesting hack.

Curious that you are not.

This is because VS 17.5 and later includes Microsoft.Data.SqlClient as a DDEX provider

[jarofkla]

Greetings everyone. I am also in the same situation as you all here. Trying to get an EF6 project into Azure. I followed the steps outlined above and got past the HierarchyId error, but now I have a new error.

I get the following error when I attempt to create a new EDMX (ADO.NET Entity Data Model),

Please review my specs and message below.

Microsoft Visual Studio Enterprise 2022
Version 17.5.0
VisualStudio.17.Release/17.5.0+33414.496
Microsoft .NET Framework
Version 4.8.09037
Installed Version: Enterprise
SQL Server Data Tools 17.2.40118.0
Microsoft SQL Server Data Tools

EntityFramework 6.4.4
Microsoft.Data.SqlClient 5.1.1

Any help getting this to work would be greatly appreciated.

@ErikEJ I am ultimately attempting to implement your solution provider to deploy my app into Azure and leverage Azure Active Directory connection with my database.

Thanks all for the walk through. Hoping to cross the finish line soon. Also wish Microsoft would come up with an official solution to this challenge.

[ErikEJ]

@jarofkla Just use the System.data.SqlClient provider while creating the EDMX and manually change later. Also consider to stop using EDMX, you can easily create a code based model with the EF6 tooling

[jarofkla]

@ErikEJ Understood. Do you have a link I could use for reference in stepping away from EDMX? I have a tight deadline and don't have time to manually rewrite my entire data layer.

[ErikEJ]

@jarofkla Sure, it is called "Code First from Database" - https://learn.microsoft.com/en-us/ef/ef6/modeling/code-first/workflows/existing-database

[jarofkla]

@ErikEJ here is another wrinkle in my project. We are using stored procedures to interface with the data layer in the app. This Code First approach doesn't appear to give me the option of interfacing with them. Is there another way to leverage the complex types that EDMX generate from the procedure signatures?

[ErikEJ]

@jarofkla no

[jarofkla]

@ErikEJ Good to know. So my immediate option is to stick with EDMX and eventually transition away after we migrate to Azure. You suggested connecting via System.Data.SqlClient initially and then substituting Microsoft.Data.SqlClient when deploying to Azure, right? Should I be following your project's read me?

[ErikEJ]

Yes, all required info should be in the readme, let me know if that is not the case.