I hate to say this but Modern Warfare 2. I know the story is more than a bit screwy but I've played nearly all the Call of Dutys and I can't remember the story from any of them, so it's not that big a deal for me.
I thought the game had some fantastic missions and jaw dropping set pieces and I'm glad they didn't ruin it by tacking on a co-op to the main campaign. It's one of the quickest ways to spoil a single player game IMO.
I loved the spec-ops missions, though it was a shame that they were mostly based on existing levels, I enjoyed the challenge and the co-op didn't spoil the fun because there were no set pieces in them.
Hell, I even enjoyed the multiplayer, not something I ever spend much time in, in any game. It worked, it was fun and it gave me a lot of option to how I wanted to play it.