search

dotnetcore / sharding-core

high performance lightweight solution for efcore sharding table and sharding database support read-write-separation .一款ef-core下高性能、轻量级针对分表分库读写分离的解决方案，具有零依赖、零学习成本、零业务代码入侵
https://xuejmnet.github.io/sharding-core-doc/
Apache License 2.0
1.16k stars 170 forks source link

分表分页查询时, 使用关联表中的字段排序分页的情况下会报错 #277

Closed wanghucheng66 closed 1 month ago

wanghucheng66 commented 1 month ago

分片表为DistributorSalesFlowMonthly 报错中的字段[MaterialGroup.Name] 在关联表MaterialGroup , 该字段是包含在IQueryable.Select()中, 但是select后新的实体类型中的字段名称是MaterialGroupName. 如果最终查询只包含一个分片表的实体表, 可以正常查询. 但是如果查询命中多个分片表时, 就会报错.

ShardingCore.Exceptions.ShardingCoreException: property:[MaterialGroup.Name] not in type:[Merck.CDMS.Core.Model.ViewModel.DistributorSalesFlowMonthlyModel] at ShardingCore.Extensions.ExpressionExtension.GetValueByExpression(Object obj, String propertyExpression) at ShardingCore.Sharding.Enumerators.OrderStreamMergeAsyncEnumerator1.GetCurrentOrderValues() at ShardingCore.Sharding.Enumerators.OrderStreamMergeAsyncEnumerator1.SetOrderValues() at ShardingCore.Sharding.Enumerators.OrderStreamMergeAsyncEnumerator1..ctor(StreamMergeContext mergeContext, IStreamMergeAsyncEnumerator1 enumerator) at ShardingCore.Sharding.Enumerators.StreamMergeAsync.MultiOrderStreamMergeAsyncEnumerator1.SetOrderEnumerator() at ShardingCore.Sharding.Enumerators.StreamMergeAsync.MultiOrderStreamMergeAsyncEnumerator1..ctor(StreamMergeContext mergeContext, IEnumerable1 enumerators) at ShardingCore.Sharding.Enumerators.StreamMergeAsync.PaginationStreamMergeAsyncEnumerator1..ctor(StreamMergeContext mergeContext, IEnumerable1 sources, Nullable1 skip, Nullable1 take) at ShardingCore.Sharding.Enumerators.StreamMergeAsync.PaginationStreamMergeAsyncEnumerator1..ctor(StreamMergeContext mergeContext, IEnumerable1 sources) at ShardingCore.Sharding.MergeEngines.Executors.ShardingMergers.AbstractEnumerableShardingMerger1.StreamMerge(List1 parallelResults) at ShardingCore.Sharding.MergeEngines.ShardingExecutors.ShardingExecutor.ExecuteAsync[TResult](StreamMergeContext streamMergeContext, IExecutor1 executor, Boolean async, IEnumerable1 sqlRouteUnits, CancellationToken cancellationToken) at ShardingCore.Extensions.TaskExtension.WaitAndUnwrapException[TResult](Task1 task, Boolean continueOnCapturedContext) at ShardingCore.Sharding.MergeEngines.ShardingExecutors.ShardingExecutor.Execute[TResult](StreamMergeContext streamMergeContext, IExecutor1 executor, Boolean async, IEnumerable1 sqlRouteUnits, CancellationToken cancellationToken) at ShardingCore.Sharding.MergeEngines.ShardingMergeEngines.Abstractions.StreamMerge.AbstractStreamEnumerable1.GetStreamMergeAsyncEnumerator(Boolean async, CancellationToken cancellationToken) at ShardingCore.Sharding.MergeEngines.ShardingMergeEngines.Abstractions.StreamMerge.AbstractStreamEnumerable1.GetAsyncEnumerator(CancellationToken cancellationToken) at ShardingCore.Sharding.MergeEngines.EnumeratorStreamMergeEngines.AsyncEnumeratorStreamMergeEngine1.GetAsyncEnumerator(CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable1.GetAsyncEnumerator(CancellationToken cancellationToken) at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable1.GetAsyncEnumerator() at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken) at Merck.CDMS.Core.Business.SalesFlowService.QueryDistributorSalesFlowDetail(DistributorSalesFlowMonthlyParameter parameter) at Merck.CDMS.Core.WebAPI.Controllers.SalesFlowController.QueryDistributorSalesFlowDetail(DistributorSalesFlowMonthlyParameter parameter) in C:\GitLabRepository\CDMS\CDMSCore\Merck.CDMS.Core.WebAPI\Controllers\SalesFlow\SalesFlowController.cs:line 563 at lambda_method5411(Closure, Object) at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask1 actionResultValueTask) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.gAwaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.gAwaited|26_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)`

可以正常生成EFCore的SQL语句,如下:

DECLARE @p_4 Int = '0'; DECLARE @__p5 Int = '10'; SELECT [t].[ID], [t].[EnumTradeType], [t].[QueryCode], [t].[SalesFlowCycleID], [t].[DistributorID], [t].[ReceiverID], [t].[ReceiverAnotherID], [t].[ProductID], [t].[MaterialGroupID], [t].[MaterialID], [t].[BatchNumberID], [t].[IsDefaultProductBatch], [t].[SaleDate], [t].[Quantity], [t].[IsApproval], [t].[IsCalculated], [t].[IsProvideSale], [t].[EnumDeficiencyType], [t].[EnumDistributorWarningType], [t].[EnumImportType], [t].[SubmitTime], [t].[DopingFeedback], [t].[InvoiceCompany], [t].[EnumSalesFlowType], [t].[Remark], [t].[IsChannelAbnormal], [t].[IsRelease], [t].[ChannelAbnormalID], [t].[EnumEntityStatus], [t].[Creator], [t].[CreateTime], [t].[FinalEditor], [t].[FinalEditTime], [t].[CustomerID], [t].[DeliveryAddress], [t].[DeliveryReceiverID], [t].[PrescriptionSourceID], [t].[PartnerID], [t].[IsSplitReceiver], [t].[SalesFlowIssueDateTypeID], [t].[SalesFlowSourceID], [t].[EnumHandleType], [t].[ProvinceID0], [t].[CityID0], [t].[PHCode0], [t].[Name] FROM ( SELECT [d].[ID], [d].[BatchNumberID], [d].[ChannelAbnormalID], [d].[CreateTime], [d].[Creator], [d].[CustomerID], [d].[DeliveryAddress], [d].[DeliveryReceiverID], [d].[DistributorID], [d].[DopingFeedback], [d].[EnumDeficiencyType], [d].[EnumDistributorWarningType], [d].[EnumEntityStatus], [d].[EnumHandleType], [d].[EnumImportType], [d].[EnumSalesFlowType], [d].[EnumTradeType], [d].[FinalEditTime], [d].[FinalEditor], [d].[InvoiceCompany], [d].[IsApproval], [d].[IsCalculated], [d].[IsChannelAbnormal], [d].[IsDefaultProductBatch], [d].[IsProvideSale], [d].[IsRelease], [d].[IsSplitReceiver], [d].[MaterialGroupID], [d].[MaterialID], [d].[PartnerID], [d].[PrescriptionSourceID], [d].[ProductID], [d].[Quantity], [d].[QueryCode], [d].[ReceiverAnotherID], [d].[ReceiverID], [d].[Remark], [d].[SaleDate], [d].[SalesFlowCycleID], [d].[SalesFlowIssueDateTypeID], [d].[SalesFlowSourceID], [d].[SubmitTime], [m].[ID] AS [ID0], [m].[Name] FROM [dbo].[DistributorSalesFlowMonthly]_ AS [d] INNER JOIN [dbo].[MaterialGroup] AS [m] ON [d].[MaterialGroupID] = [m].[ID] WHERE [d].[SalesFlowCycleID] IN (202401, 202406) ORDER BY [m].[Name] OFFSET @p_4 ROWS FETCH NEXT @__p_5 ROWS ONLY ) AS [t] ORDER BY [t].[Name]

DECLARE @p_4 Int = '0'; DECLARE @__p5 Int = '10'; SELECT [t].[ID], [t].[EnumTradeType], [t].[QueryCode], [t].[SalesFlowCycleID], [t].[DistributorID], [t].[ReceiverID], [t].[ReceiverAnotherID], [t].[ProductID], [t].[MaterialGroupID], [t].[MaterialID], [t].[BatchNumberID], [t].[IsDefaultProductBatch], [t].[SaleDate], [t].[Quantity], [t].[IsApproval], [t].[IsCalculated], [t].[IsProvideSale], [t].[EnumDeficiencyType], [t].[EnumDistributorWarningType], [t].[EnumImportType], [t].[SubmitTime], [t].[DopingFeedback], [t].[InvoiceCompany], [t].[EnumSalesFlowType], [t].[Remark], [t].[IsChannelAbnormal], [t].[IsRelease], [t].[ChannelAbnormalID], [t].[EnumEntityStatus], [t].[Creator], [t].[CreateTime], [t].[FinalEditor], [t].[FinalEditTime], [t].[CustomerID], [t].[DeliveryAddress], [t].[DeliveryReceiverID], [t].[PrescriptionSourceID], [t].[PartnerID], [t].[IsSplitReceiver], [t].[SalesFlowIssueDateTypeID], [t].[SalesFlowSourceID], [t].[EnumHandleType], [t].[ProvinceID0], [t].[CityID0], [t].[PHCode0], [t].[Name] FROM ( SELECT [d].[ID], [d].[BatchNumberID], [d].[ChannelAbnormalID], [d].[CreateTime], [d].[Creator], [d].[CustomerID], [d].[DeliveryAddress], [d].[DeliveryReceiverID], [d].[DistributorID], [d].[DopingFeedback], [d].[EnumDeficiencyType], [d].[EnumDistributorWarningType], [d].[EnumEntityStatus], [d].[EnumHandleType], [d].[EnumImportType], [d].[EnumSalesFlowType], [d].[EnumTradeType], [d].[FinalEditTime], [d].[FinalEditor], [d].[InvoiceCompany], [d].[IsApproval], [d].[IsCalculated], [d].[IsChannelAbnormal], [d].[IsDefaultProductBatch], [d].[IsProvideSale], [d].[IsRelease], [d].[IsSplitReceiver], [d].[MaterialGroupID], [d].[MaterialID], [d].[PartnerID], [d].[PrescriptionSourceID], [d].[ProductID], [d].[Quantity], [d].[QueryCode], [d].[ReceiverAnotherID], [d].[ReceiverID], [d].[Remark], [d].[SaleDate], [d].[SalesFlowCycleID], [d].[SalesFlowIssueDateTypeID], [d].[SalesFlowSourceID], [d].[SubmitTime], [m].[ID] AS [ID0], [m].[Name] FROM [dbo].[DistributorSalesFlowMonthly2024]_ AS [d] INNER JOIN [dbo].[MaterialGroup] AS [m] ON [d].[MaterialGroupID] = [m].[ID] WHERE [d].[SalesFlowCycleID] IN (202401, 202406) ORDER BY [m].[Name] OFFSET @p_4 ROWS FETCH NEXT @__p_5 ROWS ONLY ) AS [t] ORDER BY [t].[Name]

wanghucheng66 commented 1 month ago

问题解决了, 使用IQueryable.Select()之后对Select之后的字段进行排序就可以正常查询数据了.

xuejmnet commented 1 month ago

@wanghucheng66 排序字段需要存在于结果集中所以最好orderby在select后面,或者使用相同属性

xuejmnet commented 1 month ago

@wanghucheng66 建议不要使用分表的导航属性

wanghucheng66 commented 1 month ago

好的, 非常感谢!