Could not load file or assembly Microsoft.SqlServer.Types

[max-yevdokimov]

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.

[RussNZ]

I'm hitting the same problem, EF Core 2.2.0 - it's got me stumped?

[dotMorten]

You'd need to serialize it yourself.
Try something like

var binvalue = reader.GetSqlBytes(rowid);
var is = SqlHierchyId.Deserialize(binvalue);

See the unit tests for examples.
This is a known limitation. See https://github.com/dotnet/corefx/issues/29139

[RussNZ]

@dotMorten Thanks, that got me going.

I was able to select the id as a string and then parse it, I don't think there is a Deserialize extension for SqlHierarchyId.

private SqlHierarchyId GetHierarchyId(Guid id)
{
	using (var command = db.Database.GetDbConnection().CreateCommand())
	{
		command.Parameters.Add(new SqlParameter("param", id));
		command.CommandText = "select [HierarchyId].ToString() as 'hid' from MyTable where Id = @param";
		db.Database.OpenConnection();
		using (var reader = command.ExecuteReader())
		{
			if (reader.Read())
			{
				if (!reader.IsDBNull(0))
					return SqlHierarchyId.Parse(reader.GetString(0));
			}
		}
	}
	return SqlHierarchyId.Null;
}

[MaceWindu]

You can also fix it using custom assembly resolver. Check my answer here https://stackoverflow.com/a/57373622/1641529

[andreaskoepf]

@MaceWindu Your resolver can be simplified by returning null in all default cases, e.g. you do not need to unregister and rebind the event handler...

private static Assembly Default_Resolving(AssemblyLoadContext context, AssemblyName requestedAssembly)
{
    // SqlServer.Types assembly redirection
    if (requestedAssembly.FullName == "Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
    {
        return typeof(Microsoft.SqlServer.Types.SqlHierarchyId).Assembly;
    }
    return null;
}

From official doc AssemblyLoadContext.Resolving Event: "If more than one event handler is registered for this event, the event handlers are called in order until an event handler returns a value that isn't null."

[andreaskoepf]

@dotMorten For a SqlParameter hodling a UDT (e.g. a stored procedure output value of type SqlHierarchyId) I have not found any other way than using the assembly redirect with AssemblyLoadContext. I hope MSFT will fix this one day (dotnet/SqlClient#322).

[bkarakaya01]

@MaceWindu Your resolver can be simplified by returning null in all default cases, e.g. you do not need to unregister and rebind the event handler...

private static Assembly Default_Resolving(AssemblyLoadContext context, AssemblyName requestedAssembly)
{
    // SqlServer.Types assembly redirection
    if (requestedAssembly.FullName == "Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
    {
        return typeof(Microsoft.SqlServer.Types.SqlHierarchyId).Assembly;
    }
    return null;
}

From official doc AssemblyLoadContext.Resolving Event: "If more than one event handler is registered for this event, the event handlers are called in order until an event handler returns a value that isn't null."

Thank you for your answers, but now I have a different error which is :

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

I'm new, so I'll be so happy if you could help me. :)

[MaceWindu]

You are trying to use it with Microsoft.Data.SqlClient. For this new client you need to build dotMorten.Microsoft.SqlServer.Types against new client instead of System.Data.SqlClient

[bkarakaya01]

I thought, I was doing what you were saying...

Entities.csproj
<ItemGroup> <PackageReference Include="dotMorten.Microsoft.SqlServer.Types" Version="1.1.0" /> <PackageReference Include="Microsoft.Data.SqlClient" Version="2.0.0" /> <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.6" /> <PackageReference Include="Microsoft.Extensions.Identity.Stores" Version="3.1.6" /> </ItemGroup>

Web.csproj
<ItemGroup> <PackageReference Include="Microsoft.Data.SqlClient" Version="2.0.0" /> <PackageReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Design" Version="3.1.3" /> </ItemGroup>

[MaceWindu]

You also need to change usings/namespaces in code to reference types from new provider