search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.79k stars 3.19k forks source link

Filtering by Contains with HierarchyId and AsSplitQuery is not working #32976

Closed IT-CASADO closed 8 months ago

IT-CASADO commented 9 months ago

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 commented 9 months ago

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 ```c# 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 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 Posts { get; set; } public HierarchyId NodeId { get; set; } = HierarchyId.GetRoot(); } public class Post { public int Id { get; set; } public required Blog Blog { get; set; } } ```