Hierarchyid in a parameter collection does not work in 8.0-rc1 with MSSQL

[bachratyg]

Repro code:
https://gist.github.com/bachratyg/d3a985224d29c48f51df8f7b1e49a32d

The same query expression (obviously different sql) used to work with EFC7.0 and EntityFrameworkCore.SqlServer.HierarchyId 4.0.0.

This is the generated query:

DECLARE @__ids_0 nvarchar(4000) = N'["/1/","/2/"]';

SELECT [s].[Id], [s].[Name]
FROM [Stuffs] AS [s]
WHERE [s].[Id] IN (
    SELECT [i].[value]
    FROM OPENJSON(@__ids_0) WITH ([value] hierarchyid '$') AS [i]
)

SQL Server returns the following error:

CLR types cannot be used as column types in OPENJSON function with explicit schema.
CLR types are not supported in WITH clause.

If the type of [value] is changed from hierarchyid to nvarchar(max) on the last line implicit conversion kicks in and the command runs just fine with the new pattern. Query plan looks the same. Or simply just revert to the old behavior for types that are not supposed to work. I'm guessing spatial is in the same bucket.

Environment

EF Core version: 8.0.0-rc.1.23419.6
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 8.0-rc1
SQL Server: 16.0.1050.5

[ajcvickers]

@roji This looks like a limitation with OPENJSON. May also impact spatial types, since they are also "CLR types" as far as SQL Server is concerned.

[ajcvickers]

/cc @bricelam

[roji]

@ajcvickers that sounds right. The workaround here would be to not use the OPENJSON/WITH syntax, but rather OPENJSON without WITH, at which point a regular CAST from nvarchar would hopefully give us the correct representation.

Let's discuss next steps in the Monday meeting.

[roji]

Confirmed that there is no regression for spatial: Contains with geometry/geography wasn't supported before. In 7.0:

var points = new[] { new Point(3.5, 4.5), new Point(99, 99) };
_ = await ctx.Blogs.Where(b => points.Contains(b.Point)).ToListAsync();

... translated to:

SELECT [b].[Id], [b].[Name], [b].[Point]
FROM [Blogs] AS [b]
WHERE [b].[Point] IN (geometry::Parse('POINT (3.5 4.5)'), geometry::Parse('POINT (99 99)'))

... which errored with:

Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid operator for data type. Operator equals equal to, type equals geometry.

Note that SQL Server doesn't seem to even allow using the equality operator, which would be consistent with IN not being supported; the following errors in the same way:

SELECT [b].[Id], [b].[Name], [b].[Point]
FROM [Blogs] AS [b]
WHERE [b].[Point] = geometry::STGeomFromText('POINT (3.5 4.5)', 0);

I'm assuming OGC STEquals would need to be used instead.

Regardless, making geometry collections work in general is tracked via #30630.