But also regarding everyday usage, doesn't Dropbox have the limitation of only syncing photos when the app is open or your location changes?
For several years now Dropbox has given the user the choice of updating images when you open the app, or updating images whenever new photos are added to the camera roll. You can also choose to limit which albums Dropbox uploads from. It's a setting in the app.
To have images upload automatically, you need to have Background App Refresh on.
I believe Photos/iCloud has more background syncing scenarios, due to having more system access. And I think in addition to uploading to iCloud, it can downsize local photos and automatically download/remove full res as needed--something I don't think third party apps have access to do. I could be misinformed about this, but if it does have these other forms of integration that Dropbox doesn't have (and maybe more?) on top of the integration that Dropbox does have, then Photos/iCloud is not just differently integrated, it objectively has a superior level of integration. Whether that matters to the individual is the subjective part.
I would have to investigate this option with Dropbox. I have never bothered with that because for three reasons I just don't need to. Any time I upgrade my iPhone I get the maximum capacity that Apple offers for that model. In the case of my 11 Pro Max, that's 512GB. Before that was 128GB with my 6s Plus/6 Plus and 64GB with my iPhone 5.
Second, I pay Dropbox for 4.1TB of storage each month. And right now that's only about 32 percent used. The majority of that space is for weekly backups of all my computers to the Dropbox folder on my MacPro (which sits on a 6TB hard drive). But my Camera Uploads folder benefits. Periodically, I will empty that folder out by drag/drop to a backup folder on my NAS. THAT folder is backed up nightly and periodically I manually back it up to Dropbox.
Third, about a year and a half ago I got a 2TB subscription with Apple. Photos are backed up at full size/resolution. I did this not for more storage space (see para above about Dropbox) but for convenience. I had a device replacement and I wanted all my photos restored to the new device without any hassle. Well, that didn't happen. However, here I'll grant you that because of iCloud being able to redownload images it has better integration. However, if I kept ALL my images in the Camera Uploads folder on Dropbox, they'd all be there the moment I logged into the Dropbox app. But I wanted them all in the Photos app and that takes iCloud when dealing with iCloud backups.
Nevertheless, I was forced to grab all my images from my backups and restore them to my phone by drag dropping them into the Photos app on my MacPro. Because iCloud missed quite a lot of them. After that purpose for the subscription was met, I just kept it because I thought it'd be nice for my two kids to have additional space as their phones have less capacity than mine.
Since I'm thinking about it, I'll also mention music. I have a large library of music, but even though I have 512GB on my iPhone that library is on my NAS. There are third party apps that can access my NAS over the home WiFi and play the music. One particular app I use can also keep track of additions/subtractions from the library. And that's also one reason I do not use iTunes or the Music app on my Macs (or PCs). Songbird for PowerPC, Nightingale for Intel and Swinsian for later versions of MacOS can all access the same library on my NAS and keep themselves updated based on if I add tracks or delete tracks. iTunes and the later Music app can't do that. Or it's convoluted.
So, I guess it just all comes down to how you use things. For me, while Apple integrates well, there are better apps.