ASP.NET Core Web Api with Dapper (SignalR-SqlTableDependency): unable to connect to web server https

I use Dapper and N-tier architecture in my ASP.NET Core 8.0 project. My project works with SignalR, but when I add SqlDependency, I get an error

Unable to connect to web server https

Hub side:

public class AppHub : Hub
{
    private readonly IExpenseService _expenseService;

    public AppHub(IExpenseService expenseService)
    {
        _expenseService = expenseService;
    }

    public async Task SendExpense()
    {
        var value = _expenseService.itemListExpense();
        await Clients.All.SendAsync("ReceiveExpense", value);
    }
}

Table dependency:

private readonly SqlTableDependency<ResultExpenseDto> _tableDependency;
private readonly AppHub _appHub;

public ExpenseDependency(AppHub appHub)
{
    string connectionString = "Data Source=ADAPC\\SQLEXPRESS;Initial Catalog=DB_WPMS;Integrated Security=True;TrustServerCertificate=True;";
    _appHub = appHub;
    _tableDependency = new SqlTableDependency<ResultExpenseDto>(connectionString);
    _tableDependency.OnChanged += _tableDependency_OnChanged;
    _tableDependency.OnError += _tableDependency_OnError;
}

public void Start()
{
    _tableDependency.Start();
}

private void _tableDependency_OnError(object sender, TableDependency.SqlClient.Base.EventArgs.ErrorEventArgs e)
{
    throw new NotImplementedException();
}

private void _tableDependency_OnChanged(object sender, TableDependency.SqlClient.Base.EventArgs.RecordChangedEventArgs<ResultExpenseDto> e)
{
    if (e.ChangeType != TableDependency.SqlClient.Base.Enums.ChangeType.None)
    {
        _appHub.SendExpense();
    }
}

ApplicationBuilder extensions:

public static class ApplicationBuilderExtensions
{
    public static void UseTableDependency(this IApplicationBuilder applicationBuilder)
    {
        var serviceProvider = applicationBuilder.ApplicationServices;
        var service = serviceProvider.GetService<ExpenseDependency>();
        service.Start();
    }
}

Program.cs file:

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddCors(options =>
{
    options.AddPolicy("CorsPolicy", builder =>
    {
        builder.AllowAnyHeader()
        .AllowAnyMethod()
        .SetIsOriginAllowed((host) => true)
        .AllowCredentials();
    });
});

builder.Services.AddHttpClient();
builder.Services.AddControllersWithViews();

builder.Services.AddSignalR();
builder.Services.AddSingleton<AppHub>();
builder.Services.AddSingleton<ExpenseDependency>();

builder.Services.AddDbContext<Context>();
builder.Services.AddIdentity<AppUser, AppRole>(options =>
{
    options.User.RequireUniqueEmail = true;
}).AddEntityFrameworkStores<Context>().AddErrorDescriber<CustomIdentityValidator>().AddDefaultTokenProviders();

builder.Services.ConfigureApplicationCookie(options =>
{
    options.LoginPath = "/Login/Index";
});

builder.Services.AddSession();
builder.Services.AddDistributedMemoryCache();

var app = builder.Build();

if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Home/Error");
    app.UseHsts();
}

app.UseSession();

app.UseCors("CorsPolicy");

app.UseHttpsRedirection();
app.UseStaticFiles();

app.UseRouting();

app.UseAuthentication();
app.UseAuthorization();

app.MapHub<AppHub>("/appHub");

app.MapControllerRoute(
    name: "default",
    pattern: "{controller=Home}/{action=Index}/{id?}");

app.UseTableDependency();

app.Run();

When I comment out the line app.UseTableDependency();, the project runs, but naturally table dependency is not used.

What is the error? I ask for your support.

Due to the lack of your customized SqlTableDependency code, it is difficult for us to reproduce.

But there is an obvious mistake in your ExpenseDependency class. Private readonly AppHub _appHub; should not be used, but private readonly IHubContext<AppHub> _appHub; should be used.

And you are using UseTableDependency to start the SqlDependency, it's a wrong way.

Here is the best practice for you. Implementing SignalR in ASP.NET Core Web App (MVC) for real-time database updates

And I have test it and it works well, and I move start method like below.

And we can use it like below. We don't need to use your custom middleware UseTableDependency.