bug：跨表 Group By 结果未达到预期数据

[Developer-echo]

[Developer-echo]

版本：7.7.1.21

EF Core 7

[Developer-echo]

log

[18:38:49 DBG] compile parameter:query entity types :Xzkj.Hhcx.Carpool.Ass,is no tracking: ,is ignore filter :False,is not support :False,max query connections limit:,connection mode:,readonly:,as route:False,is sequence:,same with sharding comparer:
[18:38:49 DBG] queryable combine:ShardingCore.Sharding.ShardingExecutors.QueryableCombines.EnumerableQueryableCombine
[18:38:49 DBG] queryable combine before:DbSet<Ass>()
    .Where(ass => ass.CreateTime > <>c__DisplayClass3_0.time && ass.UserId == (Guid?)<>c__DisplayClass3_0.uid)
    .GroupBy(ass => new { 
        StartCity = ass.StartCity, 
        EndCity = ass.EndCity
     })
    .Select(g => new { 
        StartCity = g.Key.StartCity, 
        EndCity = g.Key.EndCity, 
        Total = g
            .Count()
     })
[18:38:49 DBG] queryable combine after:DbSet<Ass>()
    .Where(ass => ass.CreateTime > <>c__DisplayClass3_0.time && ass.UserId == (Guid?)<>c__DisplayClass3_0.uid)
    .GroupBy(ass => new { 
        StartCity = ass.StartCity, 
        EndCity = ass.EndCity
     })
    .Select(g => new { 
        StartCity = g.Key.StartCity, 
        EndCity = g.Key.EndCity, 
        Total = g
            .Count()
     })
[18:38:49 DBG] data source route result:4d14c2e882a94a4abbddb9d3c055f47a
[18:38:49 DBG] table route results:DataSourceName:4d14c2e882a94a4abbddb9d3c055f47a,TableRouteResult:(has different tail:False,current table:[4d14c2e882a94a4abbddb9d3c055f47a.2023.Xzkj.Hhcx.Carpool.Ass]),DataSourceName:4d14c2e882a94a4abbddb9d3c055f47a,TableRouteResult:(has different tail:False,current table:[4d14c2e882a94a4abbddb9d3c055f47a.2024.Xzkj.Hhcx.Carpool.Ass])
[18:38:49 DBG] rewrite queryable pagination context:[Skip: ,  Take: ]
[18:38:49 DBG] rewrite queryable order by context:[]
[18:38:49 DBG] rewrite queryable group by context:[ass => new { 
    StartCity = ass.StartCity, 
    EndCity = ass.EndCity
 }]
[18:38:49 DBG] rewrite queryable select context:[OwnerType: <>f__AnonymousType3`3[System.String,System.String,System.Int32], Property: System.String StartCity, PropertyName: StartCity,OwnerType: <>f__AnonymousType3`3[System.String,System.String,System.Int32], Property: System.String EndCity, PropertyName: EndCity,OwnerType: <>f__AnonymousType3`3[System.String,System.String,System.Int32], Property: Int32 Total, PropertyName: Total]
[18:38:49 INF] Executed DbCommand (24ms) [Parameters=[@__p_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT TOP(@__p_0) [a].[Id], [a].[CreationTime], [a].[EventData], [a].[EventName], [a].[ExtraProperties]
FROM [AbpEventOutbox] AS [a]
ORDER BY [a].[CreationTime]
[18:38:49 DBG] Server carpool-main:80034:0b310db7 heartbeat successfully sent
[18:38:49 INF] Executed DbCommand (61ms) [Parameters=[@__time_0='?' (DbType = DateTime), @__uid_1='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT [a].[StartCity], [a].[EndCity], COUNT(*) AS [Total]
FROM [Ass_2023] AS [a]
WHERE [a].[CreateTime] > @__time_0 AND [a].[UserId] = @__uid_1
GROUP BY [a].[StartCity], [a].[EndCity]
ORDER BY [a].[StartCity], [a].[EndCity]

[xuejmnet]

@Developer-echo 建议升级到7.x.1.22版本因为我没测试出来如果可以方便给一个code-first带初始化数据的demo吗最小案例即可

[xuejmnet]

经调试后发现是数据库db的排序和c#不一致,数据库用asc排序出来是先安阳后南阳，但是在c#里面是先南阳后安阳导致group异常

解决方案

新建一个比较器替换默认的

    public class SQLServerLanguageShardingComparer : IShardingComparer
    {
        private StringComparer stringComparer = StringComparer.Create(new CultureInfo("zh-CN", true), true);
        private readonly ConcurrentDictionary<Type, object> _comparers = new ();
        public virtual int Compare(IComparable x, IComparable y, bool asc)
        {
            if (x is Guid xg && y is Guid yg)
            {
                return new SqlGuid(xg).SafeCompareToWith(new SqlGuid(yg), asc);
            }

            if (x is string xs && y is string ys)
            {
                if(asc){
                    return stringComparer.Compare(xs, ys);
                }
                return stringComparer.Compare(ys,xs);
            }
            return x.SafeCompareToWith(y, asc);
        }

        public object CreateComparer(Type comparerType)
        {
            var comparer = _comparers.GetOrAdd(comparerType,
                key => Activator.CreateInstance(typeof(InMemoryShardingComparer<>).GetGenericType0(comparerType),
                    this));
            return comparer;
        }
    }

注册的时候替换

context.Services
            .AddShardingConfigure<HhcxDbContext>()
            .UseRouteConfig(c =>
            {
                c.AddShardingTableRoute<AssTableRoute>();
            })
            .UseConfig((sp, op) =>
            {
                op.UseShardingQuery((conn, builder) =>
                {
                    builder.UseSqlServer(conn);
                });

                op.UseShardingTransaction((conn, builder) =>
                {
                    builder.UseSqlServer(conn);
                });

                op.AddDefaultDataSource(dataSourceName: Guid.NewGuid().ToString("N")
                    , connectionString: configuration.GetConnectionString("Default"));

            }).ReplaceService<IShardingComparer,SQLServerLanguageShardingComparer>()
            .AddShardingCore();