Filtering by Contains with HierarchyId and AsSplitQuery is not working

[IT-CASADO]

File a bug

Using a contains filter with HierarchyId in a spilt query scenario is throwing following exception:

Microsoft.Data.SqlClient.SqlException (0x80131904): CLR types cannot be used as column types in OPENJSON function with explicit schema. CLR types are not supported in WITH clause.

I found this related issue: #31930

Include your code

   HierarchyId[] nodeIdFilter = [HierarchyId.GetRoot()];
      context
          .Blogs
          .Include(b => b.Posts)
          .Where(b => nodeIdFilter.Contains(b.NodeId))
          .AsSplitQuery()
          .ToList();

You can find a full working repro here: https://github.com/IT-CASADO/EfCoreBugs
Please run the unit test: SplitQueryWithHierarchyIdFilter

Include stack traces

Message: 
Did not expect any exception, but found Microsoft.Data.SqlClient.SqlException (0x80131904): CLR types cannot be used as column types in OPENJSON function with explicit schema. CLR types are not supported in WITH clause.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.<PopulateSplitIncludeCollection>g__InitializeReader|25_1[TIncludingEntity,TIncludedEntity](RelationalQueryContext queryContext, RelationalCommandCache relationalCommandCache, IReadOnlyList`1 readerColumns, Boolean detailedErrorsEnabled)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.<>c__25`2.<PopulateSplitIncludeCollection>b__25_0(ValueTuple`4 tup)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.<>c__DisplayClass12_0`2.<Execute>b__0(DbContext _, TState s)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, TState state, Func`2 operation, Func`2 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.PopulateSplitIncludeCollection[TIncludingEntity,TIncludedEntity](Int32 collectionId, RelationalQueryContext queryContext, IExecutionStrategy executionStrategy, RelationalCommandCache relationalCommandCache, IReadOnlyList`1 readerColumns, Boolean detailedErrorsEnabled, SplitQueryResultCoordinator resultCoordinator, Func`3 childIdentifier, IReadOnlyList`1 identifierValueComparers, Func`5 innerShaper, Action`3 relatedDataLoaders, INavigationBase inverseNavigation, Action`2 fixup, Boolean trackingQuery)
   at lambda_method234(Closure, QueryContext, IExecutionStrategy, SplitQueryResultCoordinator)
   at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at EfCoreBugsTest.UnitTest1.<>c__DisplayClass0_0.<SplitQueryWithHierarchyIdFilter>b__0() in C:\Code\GITHUB-MY\EfCoreBugs\EfCoreBugsTest\UnitTest1.cs:line 30
   at FluentAssertions.Specialized.ActionAssertions.InvokeSubject()
   at FluentAssertions.Specialized.DelegateAssertions`2.InvokeSubjectWithInterception()
ClientConnectionId:bfe6ca19-29d4-4bdc-813e-556de380a77c
Error Number:13616,State:1,Class:16.

  Stack Trace: 
XUnit2TestFramework.Throw(String message)
TestFrameworkProvider.Throw(String message)
DefaultAssertionStrategy.HandleFailure(String message)
AssertionScope.FailWith(Func`1 failReasonFunc)
AssertionScope.FailWith(Func`1 failReasonFunc)
AssertionScope.FailWith(String message, Object[] args)
DelegateAssertionsBase`2.NotThrowInternal(Exception exception, String because, Object[] becauseArgs)
DelegateAssertions`2.NotThrow(String because, Object[] becauseArgs)
UnitTest1.SplitQueryWithHierarchyIdFilter() line 39
RuntimeMethodHandle.InvokeMethod(Object target, Void** arguments, Signature sig, Boolean isConstructor)
MethodBaseInvoker.InvokeWithNoArgs(Object obj, BindingFlags invokeAttr)

Include provider and version information

EF Core version: 8.0.1
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 8.0
Operating system: Windows
IDE: Visual Studio 2022 17.8.5

[roji]

Confirmed bug in main. The scenario above itself isn't a regression since hierarchyid support was introduced in 8.0, but the underlying issue is any transformation from OPENJSON with WITH to without WITH; this affects other scenarios as well (i.e. when the OPENJSON collection order needs to be preserved).

Simplified repro

await using var ctx = new BlogContext();
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();

ctx.Blogs.Add(new Blog());
await ctx.SaveChangesAsync();

HierarchyId[] nodeIdFilter = [HierarchyId.GetRoot()];
_ = await ctx
    .Blogs
    .Include(b => b.Posts)
    .Where(b => nodeIdFilter.Contains(b.NodeId))
    .AsSplitQuery()
    .ToListAsync();

public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(
                "Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false",
                o => o.UseHierarchyId())
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
}

public class Blog
{
    public int Id { get; set; }

    public List<Post> Posts { get; set; }
    public HierarchyId NodeId { get; set; } = HierarchyId.GetRoot();
}

public class Post
{
    public int Id { get; set; }
    public required Blog Blog { get; set; }
}