ASP.NET Core后台服务调用EF Core LINQ查询的安全性与问题排查

注：本问题与之前某问题相关，因原问题评论未解决问题，故提出新问题，非重复提问。

背景概述 #

在基于ASP.NET Core、SQL Server、EF Core的医疗类Web应用中，通过LINQ查询获取所有需通知客户的周期性体检提醒数据，查询代码如下：

_context.Tests
.Where(t => t.Visit.User.SMSAccount != null
       && t.Visit.User.SMSAccount.AutoReminder
       && t.Visit.Customer.Consent
       && (t.Visit.Customer.ContactBy == "Τηλέφωνο κινητό" || t.Visit.Customer.ContactBy == "Email και Κινητό")
       && !string.IsNullOrEmpty(t.Visit.Customer.Mobile)
       && !t.Visit.Pet.PassedAway
       && t.Reminder.HasValue
       && !t.ReminderSMSSent
       && DateTime.Now.Date.AddDays(t.Visit.User.SMSAccount.DaysBefore.Value) == t.Reminder)
.Include(t => t.Visit.User.SMSAccount)
.Include(t => t.Visit.Customer)
.Include(t => t.Visit.Pet)
.AsNoTracking();

该任务由每日正午运行的BackgroundService执行，客户会在提醒日期前DaysBefore天收到通知（例如：提醒日期为2024-01-01，DaysBefore设为7时，通知会在2023-12-25发送）。

后台服务实现 #

该BackgroundService作为ASP.NET Core Web应用的一部分部署在Windows Server 2012 R2的IIS上，通过创建作用域获取Scoped服务TestService执行上述查询，服务代码如下：

public class SmsSenderService : BackgroundService
{
  public SmsSenderService(IServiceProvider serviceProvider)
  {
    this.logger = logger;
    this.serviceProvider = serviceProvider;
  }

  protected override async Task ExecuteAsync(CancellationToken stoppingToken)
  {
    do
    {  
      // ... 等待至正午                
      if (timeToWait == 0)
      {
        using var scope = this.serviceProvider.CreateScope();
        var testService = scope.ServiceProvider.GetRequiredService<TestService>();
        var reminders = await testService.GetSmsRemindersAsync();

        numberOfHours = 24;  // 等待至下次执行的时长
      }

      await Task.Delay(TimeSpan.FromHours(numberOfHours), stoppingToken);            
    }
    while (!stoppingToken.IsCancellationRequested);       
  }  
}  

在Startup.cs的ConfigureServices中注册该后台服务：

services.AddHostedService<SmsSenderService>();

核心问题 #

1. 这种实现方式是否适合执行影响所有应用用户的全局任务？另外，该后台服务在Web服务器上的触发时机是什么？是否存在因IIS进程闲置等情况导致服务不运行的可能？
2. 当前设计是否会影响LINQ查询的行为？我发现查询偶尔无法返回全部数据，但在LinqPad中模拟时能正常获取所有数据（模拟代码及生成的SQL见下文）。部署到Web服务器后偶尔出现数据缺失，可能的原因是什么？

LinqPad模拟代码 #

void Main()
{
    var dt = new DateTime(2024,1,24);
    
    var data = Tests
    .Where(t => t.Visit.User.UserSMSAccounts != null 
            && t.Visit.User.UserSMSAccounts.FirstOrDefault().AutoReminder
            && t.Visit.Customer.Consent
            && (t.Visit.Customer.ContactBy == "Τηλέφωνο κινητό" || t.Visit.Customer.ContactBy == "Email και Κινητό")
            && !string.IsNullOrEmpty(t.Visit.Customer.Mobile)
            && !t.Visit.Pet.PassedAway
            && t.Reminder.HasValue
            && !t.ReminderSMSSent 
            && dt.Date.AddDays(t.Visit.User.UserSMSAccounts.FirstOrDefault().DaysBefore.Value) == t.Reminder.Value);    
    
    var results = ConvertToReminderSmsList(data);   
    results.Dump();
}

// 可在此定义其他方法、字段、类和命名空间

IEnumerable<ReminderSms> ConvertToReminderSmsList(IEnumerable<Tests> tests)
{
    List<ReminderSms> reminderSmsList = new List<ReminderSms>();
    foreach (Tests test in tests)
    {
        reminderSmsList.Add(ConvertToReminderSms(test));
    }
    return reminderSmsList;
}

ReminderSms ConvertToReminderSms(Tests test)
{
    var reminderSms = new ReminderSms
    {
        Id = test.Id,
        ApiKey = test.Visit.User.UserSMSAccounts.FirstOrDefault().APIKEY,
        SmsType = test.Visit.User.UserSMSAccounts.FirstOrDefault().SMSType,
        Sender = test.Visit.User.UserSMSAccounts.FirstOrDefault().Sender,
        Recipient = test.Visit.Customer.Mobile
    };

    StringBuilder smsMessage = new StringBuilder();
    smsMessage.Append(test.Visit.User.UserSMSAccounts.FirstOrDefault().Message);
    reminderSms.Message = smsMessage.ToString();
    return reminderSms;
}

class ReminderSms
{
    public int Id { get; set; }

    public string Sender { get; set; }

    public string Recipient { get; set; }

    public string Message { get; set; }

    public string ApiKey { get; set; }

    public string SmsType { get; set; }
}

生成的对应SQL #

-- Region Parameters
-- @__dt_Date_0='2023-10-22T00:00:00'
-- EndRegion
SELECT [t].[Id], [t].[Comments], [t].[LimitSet], [t].[Reminder], [t].[ReminderCustomerNotified], [t].[ReminderSMSSent], [t].[Results], [t].[Stamp], [t].[Status], [t].[Type], [t].[VisitId]
FROM [Tests] AS [t]
INNER JOIN [Visits] AS [v] ON [t].[VisitId] = [v].[Id]
INNER JOIN [AspNetUsers] AS [a] ON [v].[UserId] = [a].[Id]
INNER JOIN [Customers] AS [c] ON [v].[CustomerId] = [c].[Id]
LEFT JOIN [Pets] AS [p] ON [v].[PetId] = [p].[Id]
WHERE ((((((((
    SELECT TOP(1) [s].[AutoReminder]
    FROM [SMSAccounts] AS [s]
    WHERE [a].[Id] = [s].[UserId]) = CAST(1 AS bit)) AND ([c].[Consent] = CAST(1 AS bit))) AND (([c].[ContactBy] = N'Τηλέφωνο κινητό') OR ([c].[ContactBy] = N'Email και Κινητό'))) AND [c].[Mobile] IS NOT NULL) AND ([p].[PassedAway] <> CAST(1 AS bit))) AND [t].[Reminder] IS NOT NULL) AND ([t].[ReminderSMSSent] <> CAST(1 AS bit))) AND ((DATEADD(day, CAST(CAST((
    SELECT TOP(1) [s0].[DaysBefore]
    FROM [SMSAccounts] AS [s0]
    WHERE [a].[Id] = [s0].[UserId]) AS float) AS int), @__dt_Date_0) = [t].[Reminder]) OR (DATEADD(day, CAST(CAST((
    SELECT TOP(1) [s0].[DaysBefore]
    FROM [SMSAccounts] AS [s0]
    WHERE [a].[Id] = [s0].[UserId]) AS float) AS int), @__dt_Date_0) IS NULL AND [t].[Reminder] IS NULL))

更新信息：DbContext配置 #

后台服务使用应用的DbContext，其在Startup.cs的ConfigureServices中配置如下：

services.AddDbContextPool<AppDbContext>(options => options.UseSqlServer(
    Configuration.GetConnectionString("ConnectionString"),
    sqlServerOptions => sqlServerOptions.CommandTimeout(90))
);

通过构造函数依赖注入将DbContext传入仓储类。

流程序列图 #

内容的提问来源于stack exchange，提问作者dpant