EFCore 7 using the sharing core 7.7.x version cross one schema missing issue

[KallerLong1]

we split tables in one custom schema, but we query data from split tables, throw one exception(relation "xxxxtable" is not exist),
the "xxxxtable" can't contains the schema.

[xuejmnet]

@KallerLong1 I need a runnable code sample ：）

[KallerLong1]

表结构：

C# 实体：

[Table("test", Schema = "test")]
public class Test
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    
    public DateTime UtcTime { get; set; }
   
    public class TableRoute : AbstractShardingQuarterKeyDateTimeVirtualTableRoute<Test>
    {
        protected override bool EnableHintRoute => true;

        public override DateTime GetBeginTime()
        {
            return new DateTime(2023, 1, 1);
        }

        public override void Configure(EntityMetadataTableBuilder<Test> builder)
        {
            builder.ShardingProperty(x => x.UtcTime);
        }
    }
}

DB Context 信息：

public class TestDbContext : AbstractShardingDbContext,IShardingTableDbContext
{
    public TestDbContext(DbContextOptions<TestDbContext> options) : base(options)
    {
    }

    public DbSet<Test> Tests => Set<Test>();
    public IRouteTail RouteTail { get; set; }
}

public abstract class AbstractShardingQuarterKeyDateTimeVirtualTableRoute<TEntity> : AbstractShardingTimeKeyDateTimeVirtualTableRoute<TEntity> where TEntity : class
    {
        public override bool AutoCreateTableByTime()
        {
            return false;
        }
        public abstract DateTime GetBeginTime();
        private static DateTime GetCurrentQuarterFirstDay([NotNull] DateTime time)
        {
            return time.Date.AddMonths(0 - (time.Month - 1) % 3).AddDays(1 - time.Day);
        }
        private static DateTime GetNextQuarterFirstDay([NotNull] DateTime time)
        {
            return GetCurrentQuarterFirstDay(time).AddMonths(3);
        }
        protected override List<string> CalcTailsOnStart()
        {
            var beginTime = GetCurrentQuarterFirstDay(GetBeginTime());

            var tails = new List<string>();
            //提前创建表
            var nowTimeStamp = GetCurrentQuarterFirstDay(DateTime.Now);
            if (beginTime > nowTimeStamp)
                throw new ArgumentException("begin time error");
            var currentTimeStamp = beginTime;
            while (currentTimeStamp <= nowTimeStamp)
            {
                var tail = ShardingKeyToTail(currentTimeStamp);
                tails.Add(tail);
                currentTimeStamp = GetNextQuarterFirstDay(currentTimeStamp);
            }
            return tails;
        }
        protected override string TimeFormatToTail(DateTime time)
        {
            var quarter = (time.Month - 1) / 3 + 1;
            return $"{time:yyyy}0{quarter}";
        }
        public override Func<string, bool> GetRouteToFilter(DateTime shardingKey, ShardingOperatorEnum shardingOperator)
        {
            var t = TimeFormatToTail(shardingKey);
            switch (shardingOperator)
            {
                case ShardingOperatorEnum.GreaterThan:
                case ShardingOperatorEnum.GreaterThanOrEqual:
                    return tail => string.Compare(tail, t, StringComparison.Ordinal) >= 0;
                case ShardingOperatorEnum.LessThan:
                    {
                        var currentQuarter = GetCurrentQuarterFirstDay(shardingKey);
                        //处于临界值 o=>o.time < [2021-01-01 00:00:00] 尾巴20210101不应该被返回
                        if (currentQuarter == shardingKey)
                            return tail => string.Compare(tail, t, StringComparison.Ordinal) < 0;
                        return tail => string.Compare(tail, t, StringComparison.Ordinal) <= 0;
                    }
                case ShardingOperatorEnum.LessThanOrEqual:
                    return tail => string.Compare(tail, t, StringComparison.Ordinal) <= 0;
                case ShardingOperatorEnum.Equal: return tail => tail == t;
                default:
                    Console.WriteLine($"shardingOperator is not equal scan all table tail");
                    return tail => true;
            }
        }
        public override string[] GetCronExpressions()
        {
            return new[]
            {
                "0 59 23 28,29,30,31 1,4,7,10 ?",
                "0 0 0 1 1,4,7,10 ?",
                "0 1 0 1 1,4,7,10 ?",
            };
        }
        public override string[] GetJobCronExpressions()
        {
            return base.GetJobCronExpressions().Concat(new[] { "0 0 0 1 1,4,7,10 ?" }).Distinct().ToArray();
        }
    }

Error Message:

Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for context type 'ABB.MOSE.SmartSolutions.Backend.Domain.MotionDbContext'.
      Npgsql.PostgresException (0x80004005): 42P01: relation "test_202301" does not exist

 

      POSITION: 34
         at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|232_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
         at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
         at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
        Exception data:
          Severity: ERROR
          SqlState: 42P01
          MessageText: relation "test_202301" does not exist
          Position: 34
          File: parse_relation.c
          Line: 1180
          Routine: parserOpenTable
      Npgsql.PostgresException (0x80004005): 42P01: relation "test_202301" does not exist

 

      POSITION: 34
         at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|232_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
         at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
         at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
        Exception data:
          Severity: ERROR
          SqlState: 42P01
          MessageText: relation "test_202301" does not exist
          Position: 34
          File: parse_relation.c
          Line: 1180
          Routine: parserOpenTable

[linzhary]

I also encountered this problem.
when i delete “AddShardingTableRoute”, it can works great.

[xuejmnet]

@KallerLong1 MessageText: relation "test_202301" does not exist this message mean sharding-core is route table 'test_202301' but db not exists,

try call

app.ApplicationServices.UseAutoTryCompensateTable();

sharding-core will create table

[xuejmnet]

u can try log sql

[linzhary]

@xuejmnet it seems the table "test_202301" is in schema "test". but the query without schema name

[xuejmnet]

@xuejmnet it seems the table "test_202301" is in schema "test". but the query without schema name

u r right ,I will try it later in mssql

[xuejmnet]

in mysql is ok,because mysql no schema

[KallerLong1]

I using the pgsql.

[xuejmnet]

@KallerLong1 ok,I will try it later

[xuejmnet]

@KallerLong1 @linzhary

sharding-core/src/ShardingCore/EFCores/ShardingModelCustomizer.cs

Line 90 in 8b7cb48

entity.ToTable($"{tableName}{tableSeparator}{tail}");

[xuejmnet]

i will fixed it later

[xuejmnet]

@KallerLong1 @linzhary im published 7.x.1.3 fixed