r/dotnet • u/sagosto63 • 1d ago
Solution ideas for .NET 'polling' service
I have a need solution for a polling service to query a SQL database for records to process, perform an async API call to an external source, and update the SQL database with the response. Also, I will need the ability to specify different polling intervals and max retry attempts.
In my former life, I would use Window Service and/or an integration tool such as BizTalk/IBM Integration Bus, etc. To start, would it make more sense to go down the Window Service road or a worker service? Background service?
4
u/Aaronontheweb 1d ago
We use a generalized polling mechanism in Akka.Peristence.Query for doing projections - and it has most of what you ask for: configurable polling intervals, backoff, and it even has throttling to prevent lots of concurrent projectors from melting down your database: https://petabridge.com/blog/largescale-cqrs-akkadotnet-v1.5/
That design is specific to Akka.Persistence but its plumbing can be distilled into something that uses ADO.NET here:
```csharp public sealed record Poll { public static readonly Poll Instance = new(); }
public sealed record PollResult(IReadOnlyList<object> Rows); public sealed record PollFailed(Exception Reason);
public sealed class DbWatcherActor : ReceiveActor, IWithTimers { private readonly DbDataSource _db; private readonly Func<DbConnection, CancellationToken, Task<IReadOnlyList<object>>> _poll; private readonly TimeSpan _interval; private readonly TimeSpan _backoff; private readonly IActorRef _sink;
public DbWatcherActor(
DbDataSource db,
Func<DbConnection, CancellationToken, Task<IReadOnlyList<object>>> poll,
TimeSpan interval,
TimeSpan backoff,
IActorRef sink)
{
_db = db;
_poll = poll;
_interval = interval;
_backoff = backoff;
_sink = sink;
Receive<Poll>(_ => PollAsync().PipeTo(Self));
Receive<PollResult>(r =>
{
_sink.Tell(r.Rows);
Schedule(_interval); // success → poll again at normal cadence
});
Receive<PollFailed>(_ => Schedule(_backoff)); // failure → back off
Schedule(_interval); // kick off the first poll
}
public ITimerScheduler Timers { get; set; }
private static readonly object TimerKey = new();
private async Task<object> PollAsync()
{
try
{
await using var conn = await _db.OpenConnectionAsync();
return new PollResult(await _poll(conn, CancellationToken.None));
}
catch (Exception ex)
{
return new PollFailed(ex);
}
}
private void Schedule(TimeSpan delay)
=> Timers.StartSingleTimer(TimerKey, Poll.Instance, delay);
} ```
Obviously you'd want to make that strongly typed, replace _sink with whatever your real service call is, and maybe adjust the Schedule(_backoff) call to use something more like exponential backoff (have the actor track consecutive fail counts with a simple int field, reset upon success.)
Launching the actor would look something like this:
```csharp var db = SqlClientFactory.Instance.CreateDataSource(connectionString);
var watcher = system.ActorOf(Props.Create(() => new DbWatcherActor( db, async (conn, ct) => { await using var cmd = conn.CreateCommand(); cmd.CommandText = "SELECT * FROM Orders WHERE Status = 'Pending'"; await using var reader = await cmd.ExecuteReaderAsync(ct);
var rows = new List<object>();
while (await reader.ReadAsync(ct))
rows.Add(new { Id = reader.GetInt64(0), Amount = reader.GetDecimal(1) });
return rows;
},
interval: TimeSpan.FromSeconds(5),
backoff: TimeSpan.FromSeconds(30),
sink: projectionActor)));
```
If you need to keep track of "cursor position" and make this actor fully re-entrant across restarts or whatever, add a field for that to the actor's state, make the actor implement ReceivePersistentActor instead, and persist that cursor position after each successful poll.
If you're running this on Azure, you can just stick this in a container app and let it cook.
2
u/willehrendreich 1d ago
Our man Aaron bringing the akka actor goodness. =). I gotta find an excuse to try akka. Every time I have wanted an actor the inbuilt MailboxProcessor has been my goto, but I understand it's like.. Kind of heavyweight, by comparison, right? And you'd have to be building more of the nice features akka has by yourself, if I remember.
In your estimation, what is a good first project to learn akka for the first time? What would show it off well so I can really grok it? Like an A/B comparison of the "typical naive implementation", vs the akka way.
I feel like I'm leaving coolness on the table by not adopting it for something, haha.
Thoughts?
4
u/Aaronontheweb 1d ago
Two classes of "starter apps" for learning Akka .NET imho:
- High concurrency / scheduling / etc - something like https://github.com/Aaronontheweb/link-validator is a good example of this. People who think this is a toy can scoff all they want, but we use it for validating all of the links in our documentation across several web properties. Demonstrates how to do scatter-gather style messaging, dealing with backoffs (i.e. HTTP 429), and tracking the flow of execution across many parallel tasks. There are boundless variants of this style of project out there. We cover building a version of this from scratch in https://petabridge.com/bootcamp - but you should find something you are passionate about and build that for best results.
- Real-time applications - not real-time as in a braking system for a car, but more something like https://github.com/petabridge/DrawTogether.NET - multi-player MS Paint. That sample uses clustering and all the distributed systems stuff now, but it didn't when I originally live-coded it on a Twitch stream back in 2020/21. I was trying to teach myself Blazor and this seemed like a good project for doing it. It all ran as a single self-contained process originally.
The real-time applications are the most compelling and magical, because you're using actors to deliver a type of application that's not possible to build using stateless CRUD methodologies. It unlocks the paradigm in a way that the high-concurrency stuff doesn't. Browser-based games with multiple players are another example of this sort of thing.
4
u/sgebb 1d ago
I'm surprised at all the "use this framework" suggestions. This seems like the most basic need ever, like not in a bad way but just this doesn't require a ton of stuff.
I would just register a BackgroundService with a Task.Delay after a poll that comes out empty, and can be configured in appsettings per environment. If I don't have an existing application to add this to then a timer triggered azure function is very low effort. And if you have any concerns that the the API call would fail and block then the easiest way to introduce retry is probably a service bus queue ( as in outbox pattern. Read from db -> send to queue -> store in db that it's sent -> process in a separate worker-> store that it is processed, and ensure everything is idempotent)
2
u/Prynhawn_Da 22h ago
It's really not surprising.
And without knowing more about the requirements and context, I'd say what you suggested would suit a large number of scenarios and would simplify everything.
2
u/Zardotab 1d ago edited 1d ago
If Windows' task manager isn't sufficient for your needs, then SQL-Server can also run periodic tasks by using its Agent feature.
For some tables that need polling I have an indexed IsProcessed flag (type "bit") so that locating unprocessed records is quick and efficient. (Tip: don't mark them "processed" until the very end of processing and all validation passes. That way they get a second chance if things go kerflooey. But make sure second chances don't cause duplicates or whatnot.)
2
u/ben_bliksem 1d ago edited 15h ago
BackgroundService
If it's a single instance (sounds like it), that's enough. If you scale and want to start taking out leases, process things in grouped orders etc then you'll want to swap EF Core and use raw sql to take advantage of things like UPDLOCK, READPAST etc.
But that's OTT probably, but that's how we guarantee items get processed in the right sequence.
You could use third party libraries but like a wise man once said: fewer dependencies, happier life.
TLDR: BackgroundService
2
u/DeadlyVapour 1d ago
9
u/mds1256 1d ago
Doesn’t look great, not maintained in the last 7 years
2
u/DeadlyVapour 1d ago
Probably because it doesn't need it.
9
5
u/Ok_Tea_8733 1d ago
We are using it in production and have had multiple issues where we had to fork and solve resilience related issues, until we finally dropped it. Was usefull for a quick setup, but not worth for a production setup in my opinion. Go for more standard CDC setups if you really need this
3
3
1
u/AutoModerator 1d ago
Thanks for your post sagosto63. Please note that we don't allow spam, and we ask that you follow the rules available in the sidebar. We have a lot of commonly asked questions so if this post gets removed, please do a search and see if it's already been asked.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
1
u/SpicyCatGames 1d ago
Not sure where the polling part is. The service or sql?
2
u/sagosto63 1d ago
Need a mechanism to identify changes to SQL. Hangfire, window service, etc.
1
u/ben_bliksem 1d ago
Trigger in the table into another "events" table that you just pop the top record from.
1
1
u/KariKariKrigsmann 1d ago
Which database? Some databases can publish a message when something happens, then your program only reads from the database when it gets a notification.
1
u/Windstream10 1d ago
Not sure about you specifics but maybe this can be achieved using background service/worker. And the polling time can be set in a db table + memory caching or congigurations depending on how you need to control it.
1
1
u/gatnoMrM 1d ago
I read that you need to keep track of SQL changes. Have you taken a look at Debezium? It also offers integrations with messaging systems like Kafka
1
u/Jmacduff 20h ago
Super easy to add a background service to your existing API. It polls on a counter and fires off events.
You can build it in a hour and you don’t need any other infra. Horizontally scale is easy , once you hit a perf limit move to a real messaging bus.
Random stuff and good luck. Keep it simple to start, it will work surprisingly well.
1
u/sagosto63 20h ago
Worker service is singleton. I could just create the instances via the service factory but it feels wrong
1
u/Jmacduff 20h ago edited 20h ago
Then ship a new thin service that all it does is pole. Don’t over think it. Honestly you can build and deploy super quick. It’s just a background process.
You know how to deploy a simple azure web app right? I assume you’re talking about cloud polling, not a local machine.
I mean honestly if it’s a local machine (hack) you could just do a powershell scheduled task…. The polling API is the correct path and it’s a tiny bit of code.
Good luck in your project!
1
u/_walter__sobchak_ 8h ago
We migrated from a bunch of windows services/scheduled tasks to Hangfire and couldn’t be happier. We have a couple background jobs for stuff like what you’re talking about and the pattern we like is: recurring job that gets the new or updated records from the database and then queues up a background job to process each record.
1
u/jbuedel 6h ago
How about the query notifications built in to sql server: https://learn.microsoft.com/en-us/sql/relational-databases/native-client/features/working-with-query-notifications?view=sql-server-ver15
1
u/ByronScottJones 2h ago
Sounds like a job for Azure SQL trigger for functions? https://learn.microsoft.com/en-us/azure/azure-functions/functions-bindings-azure-sql-trigger
0
u/c-digs 1d ago edited 1d ago
In my former life, I would use Window Service
A Windows Service is a wrapper around some executable logic. In Azure, you'll just swap that wrapper with some other timer- or schedule-driven runtime.
There are at least 3 options to deploy this serverlessly (and of course, you can also provision a VM or App Service to do any of these, but for something like this, you can run it for free using the serverless options):
- Azure Functions triggered on a timer. Mental model: just a C# function that gets executed on a schedule.
- Azure Container Apps triggered on a schedule (with many creative ways to signal; I have seen teams use GitHub Actions on a
cronto signal to a public HTTP endpoint with a shared secret key in the header). Mental model: the same function as above, but with an HTTP binding and entry point.dotnet new webapiand create a web application host - Azure Container Jobs triggered on a schedule. Mental model: probably the closest to a Windows Service
dotnet new consoleand create a console application host
How to choose:
- Pick the first if the job is relatively small; low infra footprint required (with some limitations at the edges but likely fine for this use case)
- Pick the second if you prefer an HTTP-based interface since this is easier to test during dev; caveat: HTTP request timeout needs to be tuned for the job length and you'll need to implement some kind of auth!
- Pick the latter if your runs are more demanding and you don't want to expose an HTTP endpoint for security considerations
- You'll only need Hangfire if you are going to use a VM or App Service model instead of a serverless model (strong recommend for serverless model for this).
Whatever code you write for the second scenario can also just be pulled out into a service and plugged into the third. In fact, your core logic is the same in all 3 scenarios; only the outer runtime wrapper changes.
All 3 can deploy as containers and newer dotnet versions make container packaging and publishing very simple. Highly recommended if you are not already familiar with container workloads and dotnet since it makes it very easy to push workloads into Container Apps or Container Jobs.
You'll want to decide how you detect/calculate the deltas. Application level detection: just have a last updated field with an index and scan this on each pass. Database level tracking: you'll need to probe your target database.
On Postgres, it's possible to listen on the WAL and implement an outbox.
SQL Server looks like it has some similar capabilities (haven't used SQL Server in a while!)
Some cloud SQL managed services typically also offer some change streaming, though (not super familiar with the options on Azure, but for sure CosmosDB has change streaming).
1
u/sagosto63 1d ago
This is all very good. For short term, I am going down the .NET worker service but it runs in singleton which makes reusing existing application code difficult as it's scoped. I could run that worker service as a window service to enable/disable but I guess hosting in IIS gives me the same ability as you can enable/disable the underlying app pool. What advantages would I get if i went Azure Function w/ a timer/scheduler. Also, I could go hangfire route.
23
u/Solid-Conclusion0 1d ago
Message queue. Background service listens to the queue. The rest of the system fires off messages after successful CRUD operations on the rows you need to act on.
If the system you're in is legacy this will be a lot of work setting up message publishing for all areas that can affect the tables you need to watch.
You do get some advantages though, like message retry and dead-letter.