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 https://github.com/ErikEJ/EntityFramework6PowerTools/issues/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: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 ): <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.

[jarofkla]

I am close, I think. Trying to run the app with your provider on my local VM, but I am getting this error:

[ArgumentException: Unable to find the requested .Net Framework Data Provider.  It may not be installed.]
   System.Data.Common.DbProviderFactories.GetFactory(String providerInvariantName) +209
   System.Data.Entity.Infrastructure.DependencyResolution.DefaultProviderFactoryResolver.GetService(Type type, Object key, Func`3 handleFailedLookup) +93

[ArgumentException: 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.]

Here is my App.config

<configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <connectionStrings>

  <add name="PIMSContext" connectionString="metadata=res://*/PIMS.csdl|res://*/PIMS.ssdl|res://*/PIMS.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=localhost\cloud_22;initial catalog=PIMS;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" /></connectionStrings>
  <entityFramework>
    <!--<defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="mssqllocaldb" />
      </parameters>
    </defaultConnectionFactory>-->
    <providers>
      <provider invariantName="Microsoft.Data.SqlClient" type="System.Data.Entity.SqlServer.MicrosoftSqlProviderServices, ErikEJ.EntityFramework.SqlServer" />
      <!--<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />-->
    </providers>
  </entityFramework>
  <system.data>
        <DbProviderFactories>
            <add name="SqlClient Data Provider" invariant="Microsoft.Data.SqlClient" description=".NET Framework Data Provider for SqlServer" type="Microsoft.Data.SqlClient.SqlClientFactory, Microsoft.Data.SqlClient" />
        </DbProviderFactories>
  </system.data>

and my EDMX file

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="3.0" xmlns:edmx="http://schemas.microsoft.com/ado/2009/11/edmx">
  <!-- EF Runtime content -->
  <edmx:Runtime>
    <!-- SSDL content -->
    <edmx:StorageModels>
      <Schema Namespace="PIMSModel.Store" Provider="Microsoft.Data.SqlClient">

Am I missing a step?

[jarofkla]

I also added the following attribute to my Context.cs file that was autogenerated from the EDMX creation.

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated from a template.
//
//     Manual changes to this file may cause unexpected behavior in your application.
//     Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace PIMS3.DataAccess
{
    using System;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;
    using System.Data.Entity.Core.Objects;
    using System.Linq;
    using System.Data.Entity.SqlServer;

    [DbConfigurationType(typeof(MicrosoftSqlDbConfiguration))]
    public partial class PIMSContext : DbContext
    {
        public PIMSContext()
            : base("name=PIMSContext")
        {
        }
.
.
.

If I do that, I get a different error.

[MetadataException: Unable to load the specified metadata resource.]
   System.Data.Entity.Core.Metadata.Edm.MetadataArtifactLoaderCompositeResource.LoadResources(String assemblyName, String resourceName, ICollection`1 uriRegistry, MetadataArtifactAssemblyResolver resolver) +418
   System.Data.Entity.Core.Metadata.Edm.MetadataArtifactLoaderCompositeResource.CreateResourceLoader(String path, ExtensionCheck extensionCheck, String validExtension, ICollection`1 uriRegistry, MetadataArtifactAssemblyResolver resolver) +239
   System.Data.Entity.Core.Metadata.Edm.MetadataCache.SplitPaths(String paths) +815
   System.Data.Entity.Core.Common.Utils.Result.GetValue() +127
   System.Data.Entity.Core.Common.Utils.Memoizer`2.Evaluate(TArg arg) +398
   System.Data.Entity.Core.Metadata.Edm.MetadataCache.GetArtifactLoader(DbConnectionOptions effectiveConnectionOptions) +74
   System.Data.Entity.Core.Metadata.Edm.MetadataCache.GetMetadataWorkspace(DbConnectionOptions effectiveConnectionOptions) +37
   System.Data.Entity.Core.EntityClient.EntityConnection.GetMetadataWorkspace() +67
   System.Data.Entity.Core.Objects.ObjectContext..ctor(EntityConnection connection, Boolean isConnectionConstructor, ObjectQueryExecutionPlanFactory objectQueryExecutionPlanFactory, Translator translator, ColumnMapFactory columnMapFactory) +535
   System.Data.Entity.Internal.InternalConnection.CreateObjectContextFromConnectionModel() +91
   System.Data.Entity.Internal.LazyInternalContext.InitializeContext() +719
   System.Data.Entity.Internal.InternalContext.Initialize() +26
   System.Data.Entity.Internal.InternalContext.ForceOSpaceLoadingForKnownEntityTypes() +42
   System.Data.Entity.DbContext.System.Data.Entity.Infrastructure.IObjectContextAdapter.get_ObjectContext() +31
   PIMS3.DataAccess.PIMSContext.GetActiveOrgs() in E:\source\navsea\usff_ucam_webforms\PIMS3\cloud_migration\PIMS3\PIMS3.DataAccess\PIMS.Context.cs:2907
   PIMS3.WebApp.Utilities.Cache.UpdateCache() in E:\source\navsea\usff_ucam_webforms\PIMS3\cloud_migration\PIMS3\PIMS3.WebApp\Utilities\Cache.cs:94
   PIMS3.WebApp.Global.Application_Start(Object sender, EventArgs e) in E:\source\navsea\usff_ucam_webforms\PIMS3\cloud_migration\PIMS3\PIMS3.WebApp\Global.asax.cs:12

[HttpException (0x80004005): Unable to load the specified metadata resource.]
   System.Web.HttpApplicationFactory.EnsureAppStartCalledForIntegratedMode(HttpContext context, HttpApplication app) +517
   System.Web.HttpApplication.RegisterEventSubscriptionsWithIIS(IntPtr appContext, HttpContext context, MethodInfo[] handlers) +185
   System.Web.HttpApplication.InitSpecial(HttpApplicationState state, MethodInfo[] handlers, IntPtr appContext, HttpContext context) +168
   System.Web.HttpApplicationFactory.GetSpecialApplicationInstance(IntPtr appContext, HttpContext context) +277
   System.Web.Hosting.PipelineRuntime.InitializeApplication(IntPtr appContext) +369

[ErikEJ]

Please share a proper repro in my repository, this has nothing to do with SqlClient. And look at the sample app xonfig in the docs.

[jarofkla]

My code lives in a private ADO server and is under TFVC. What's the best way to share?

[ErikEJ]

Create a simple repro console app and upload as .zip or github repo. Maybe you will learn something in the process 😉

[jarofkla]

I built a console app and am getting the same error that the web app is generating. Additionally I was reminded that the code I am working on belongs to the government so I cannot share it. Is there any other way you could help me? Your solution would save me a serious amount of manhours having to rewrite my data layer.

[ErikEJ]

@ErikEJ Feel free to contact me privately and I will let you know my hourly rate.

[cveld]

@jarofkla you could write a small data model in a console app and get that to work and build out from there to mimic your project situation as close as possible. Maybe there is some assembly mismatch?

On Thu, Jun 8, 2023 at 5:53 PM Erik Ejlskov Jensen @.***> wrote:

@ErikEJ https://github.com/ErikEJ Feel free to contact me privately and I will let you know my hourly rate.

— Reply to this email directly, view it on GitHub https://github.com/dotnet/SqlClient/issues/2020#issuecomment-1582884518, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABPIYJB7JEFD533JMNAWABTXKHYQNANCNFSM6AAAAAAXK5K354 . You are receiving this because you were mentioned.Message ID: @.***>

[ErikEJ]

@JRahnama May I suggest you close this as of topic, pls.

[JRahnama]

Closing this as the issue is not SqlClient issue.

[ahmedHashwa]

@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: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 ): <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.

It worked for me in Visual Studio 2022 with slight modifications to fit for my version of course. It will be different for Community Edition as well. Thanks mate 🙂.