.net 6.0 EF and a long running process

Question

I have an ASP.NET Core 6.0 Web API that uses EF to interact with SQL Server.

I have a stored procedure that is responsible for rebuilding projection tables that takes about 10 minute to run.

I have a need to expose the rebuilding of these protection table via a Web API that exposes this data to other applications inside our company.

Can any tell me the best way to trigger a rebuild via the stored procedure but not have the call need to wait for it to end?

Thanks in advance.

Answer 1

As far as I understand correctly, a client calls your API, which, in turn, calls a sproc which runs for like 10 mins. I assume you don't need to monitor (via the API) the progress or completion. So to run that sproc in background you can use a .Net Core Worker Service either as part of your web app or as a WebJob (if you're hosting in Azure). You would need to use some sort of queue or other communtication approach between you web app and the backgound service (even if it's run in the same process). An absolutely different approach could be using Azure Functions with Durable Tasks (since AF don't support long running operations). They can be triggered by an HTTP request, so can be mapped into your Web API surface.

But possibly, a simpler approach would be, indeed, using SQL Server Agent (or similar, if you use other RDBMS), like the answer referenced in the comments suggests.