It's interesting, because in this whole thing I've noticed that there's a reason that I am ok with Steam on my PC when I'm not ok with digital downloads for a console.
Primarily, it's because of the kinds of games that are released and played on them. On PC, I have games that are inherently ALL about the online experience, games like TF2, DOTA 2, League of Legends, Starcraft 2, Counter Strike, ARMA, and many more. These games (from what I've seen) make up the biggest contingency of PC games, and they all "fit" within Steam and it's business model very well.
However, on console you have an entirely different type of game: the game as a story. These kinds of games make up the bulk of the console market, games like Uncharted (1, 2, and 3), the Bioshock series, the Mass Effect trilogy, the Far Cry series, the God of War games, etc. Even just the way you label them (series, trilogy, etc) shows the difference: these are static creations.
Ultimately, on a platform that most people use to consume STATIC pieces of content, why do we need to be chained to some other service? Why must I have an internet connection to enjoy a great single-player experience?
That might be correct for you, but it's not really for most gamers.
Each year the annual COD release on consoles is huge on consoles (bigger than on PC). FIFA is similarly huge in Europe. Halo, one of the biggest console exclusives, is praised at least 50/50 for it's single/multiplayer content. Online gaming is a big part of console games, even if it isn't for you.
At the same time, most of the games you mentioned as single player experiences are available on PC. Plus many (I'd say most, even) PC-exclusive titles are heavily single-player focused.
Obviously, this doesn't affect your personal reasoning, but I don't think it's shared by many people.
Oblivion, Skyrim, Fallout, Just Cause 2, and Civilization are just a handful of mostly single player games I saw scrolling through my library that I and I would guess most gamers would prefer to play on PC even though they're also on consoles.