I think when people are asked this question they often gravitate towards horror games but I appreciate any game that crafts a consistent, compelling, 'believable' environment that doesn't constantly signpost that you are actually playing a game. A recent failure in this regard for me was Assassin's Creed 4. I wanted to get more absorbed in its, admittedly interesting and compelling game world, but it seemed determined to pull me out every few minutes.
My votes, off the top of my head:
Metroid Prime (Trilogy)
Half Life 2
Deus Ex: Human Revolution
Resident Evil 4
OOT and Majora's Mask
Rare games generally (particularly Goldeneye, Perfect Dark and the Banjo Games)