Post has spoilers (for DLC too), though this is a bad topic to be in if you are trying to avoid those.
Including DLC? Walking with Spirits from Point Lookout. Very cool, especially when you actually understand the visions, and I loved the twist after waking up. I was wearing Power Armor + Helmet when I woke up and Nadine mentioned the scar on my head and I was like "What?" so she said I should look in a mirror. I was surprised and pissed about being bald! Luckily the hair came back after the month long(?) ride back to the main game so it all worked out, plus I got a piece of me for my Megaton house
Just main game though...a tie between The Replicated Man and Tenpenny Tower. The Replicated Man was intriguing even if it was short, finding out that Androids can and do evolve beyond their programming and learning that he's right under Zimmer's nose. Tenpenny Tower was enjoyable because of the politics involved throughout, though I was peeved when I worked so hard at a neutral solution only to have the @#$%ing Ghouls take over anyways -.-