One of the games you mentioned in there that I definitely want to try is Bioshock! I've heard it's definitely an "Experience" like you talk about here

I usually shyed away from single-player games but someday I may go back to give some classics a whirl 

 Bioshock Infinite is indeed a well designed experience. It doesn't go very deep but they absolutely nailed the story's execution and immersion.