[Question] When requesting the database, a statement that I have not operated will always be executed.

[lycoris-xmin]

Steps to reproduce

            services.AddPooledDbContextFactory<MySqlContext>(opt =>
            {
                opt.UseMySql(AppSettings.Sql.ConnectionString, new MySqlServerVersion(ServerVersion.Parse(AppSettings.Sql.Version)), builder =>
                {
                    builder.MinBatchSize(4);
                    builder.CommandTimeout(60);
                    builder.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery);
                    builder.UseNewtonsoftJson(MySqlCommonJsonChangeTrackingOptions.FullHierarchyOptimizedSemantically);
                });

                opt.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);

                if (AppSettings.IsDebugger)
                {
                    opt.LogTo(x =>
                    {
                        Console.ForegroundColor = ConsoleColor.DarkYellow;
                        Console.WriteLine(x);
                        Console.ResetColor();
                    }, LogLevel.Information);

                    opt.EnableSensitiveDataLogging();
                    opt.EnableDetailedErrors();
                }

                // 读写分离 （还未测试）
                // opt.AddInterceptors(new MasterSlaveCommandInterceptor());
            });

Query code

            var filter = _thought.GetAll()
                                 .Where(x => x.Id < input.Cursor)
                                 .Where(x => x.Status == ThoughtStatusEnum.Defalut)
                                 .WhereIf(!input.Keyword.IsNullOrEmpty(), x => x.Content != "" && EF.Functions.Like(x.Content, $"%{input.Keyword}%"))
                                 .OrderByDescending(x => x.Id)
                                 .ThenByDescending(x => x.CreateTime)
                                 .PageBy(input);

            var query = GetSearchQueryable(filter);

            var list = await query.ToListAsync();

Repository Code

public virtual IQueryable<T> GetAll() => _context.Set<T>().AsNoTrackingIf(NoTracking)

The issue

No matter what database operation I perform, this statement is always executed.

Further technical details

MySQL version: 8.0.23 Operating system: win10 Pomelo.EntityFrameworkCore.MySql version:7.0.0 Microsoft.AspNetCore.App version:6.0

The versions used in the past do not seem to have this problem. I don’t know if it is intentional or if my configuration is wrong.

[lycoris-xmin]

info: 2023/10/24 11:34:12.352 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (35ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      SELECT CASE WHEN COUNT(*) = 0 THEN FALSE ELSE TRUE END
      FROM information_schema.tables
      WHERE table_type = 'BASE TABLE' AND table_schema = 'galaxy'
info: 2023/10/24 11:34:12.577 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (19ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      SELECT CASE WHEN COUNT(*) = 0 THEN FALSE ELSE TRUE END
      FROM information_schema.tables
      WHERE table_type = 'BASE TABLE' AND table_schema = 'galaxy'
info: 2023/10/24 11:34:13.536 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (43ms) [Parameters=[@__input_Cursor_0='?' (DbType = Int64), @__Format_2='?' (Size = 255), @__filter_Cursor_3='?' (DbType = Int64), @__p_4='?' (DbType = Int32)], CommandType='Text', CommandTimeout='60']
      SELECT `t0`.`Id`, `t0`.`Content`, `t0`.`Type`, `t0`.`Like`, `t0`.`Collect`, `t0`.`Comment`, `t0`.`Share`, `t1`.`Id`, `t1`.`NickName`, `t1`.`Avatar`
      FROM (
          SELECT `t`.`Id`, `t`.`Collect`, `t`.`Comment`, `t`.`Content`, `t`.`CreateTime`, `t`.`CreateUserId`, `t`.`Like`, `t`.`Share`, `t`.`Type`
          FROM `T_Thought` AS `t`
          WHERE (((`t`.`Id` < @__input_Cursor_0) AND (`t`.`Status` = 0)) AND ((`t`.`Content` <> '') AND (`t`.`Content` LIKE @__Format_2))) AND (`t`.`Id` > @__filter_Cursor_3)
          ORDER BY `t`.`Id` DESC, `t`.`CreateTime` DESC
          LIMIT @__p_4
      ) AS `t0`
      LEFT JOIN `T_User` AS `t1` ON `t0`.`CreateUserId` = `t1`.`Id`
      ORDER BY `t0`.`Id` DESC, `t0`.`CreateTime` DESC

The pictures I uploaded here cannot be loaded. In case you can’t see the screenshot, here is what the console prints.

where SELECT CASE WHEN COUNT(*) = 0 THEN FALSE ELSE TRUE END FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema = 'galaxy' is the sql statement that will be executed every time a database operation is performed

[lauxjpn]

The pictures I uploaded here cannot be loaded. In case you can’t see the screenshot, here is what the console prints.

Thanks! Always copy & paste code (or log output). Never use screenshots, unless there is no other way. We cannot copy code from screenshots.

---

The statements you highlighted are executed if we check whether your database already exists or not. We don't do this on our own, but only if your code triggers us to do so.

The MySqlDatabaseCreator class would be used if your code calls DbContext.Database.EnsureCreated().

https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/blob/0f6d4d6d6b2237c423c55b48d611a6c337f88a0e/src/EFCore.MySql/Storage/Internal/MySqlDatabaseCreator.cs#L113-L134

[lauxjpn]

@lycoris-xmin Is there anything that needs more clarifying or can we close this issue?

[lycoris-xmin]

Sorry, I have been busy with work recently and forgot to deal with it. I will close it immediately.