If you like H.P. Lovecraft you have got to play Eternal Darkness for the Nintendo Gamecube. It is in the bargain bin now and I picked up my copy for ~$15. A very creepy game with a unique spell system. I am about 11 hours into the game I have three chapters left.
First/Third person horror games are few and far between. The last great one for the PC was Clive Barker's Undying. It seems these types of games are mostly the fair of consoles.
You can't mention horror game without talking about the Silent Hill series - of which I think Silent Hill 2 was the best. Silent Hill 3 and Silent Hill 4 seem all to eager too gross you out and lost the spooky atmosphere.
I am also very excited for Call of Cthulhu: Dark Corners of the Earth to be released at the end of the month. This is supposed a very hard game. For instance if you want to know how much ammunition you have left you actually have to unload the gun and check - can't wait!