It sucks, plain and simple. Single-player games should never require internet access, and if the game has a multiplayer component, it should be a separate mode that leaves the single-player mode working even when there is no internet connectivity.
It’s just basic fucking common sense… except that it conflicts with financial interests and greed.
Not so much any one game, as a genre: action adventure games. Games like Tomb Raider, God of War, stuff like that. I love playing through a really immersive, well-told story.