You mentioned that you have played Resident Evil but how much of the series have you specifically gone through? Certainly, your millage within it will vary as it gradually evolves into a more action-orientated genre, but some of the more recent entries like Revelations and VII bring a lot of the old survival horror elements back (especially the tension angle that you mentioned you particularly liked) whilst keeping an engaging combat scenario in place. The downside is that though the enemies feel more weighted and threatening, they are boring, which is a damn shame.
Beyond that I've found a lot of hit and misses. The Last Of Us is a must. It is a fantastic game that deserves all the praise heaped onto it. The combat, tension and emotions at play are all on point. I'd avoid The Evil Within. It was a mess, lots of confusing story aspects, and very comic-book esque in it's staged direction and execution. I've heard that the sequel is marginally better, but beyond picking it up cheap, I don't think it's worth a try.
Until Dawn is a unique beast. It's like those old choose your own adventure type games based on those cheap horror films you see on the Horror channel at 3 am in the morning with that all-too-similar cast of young models on their summer break ... but it works. Your choices define the flow of who survives, and that alone does ramp up the tension as the story unfolds and you try to keep everyone alive and work out just what is going on. I was surprised at just how much I liked it, because it is light on the combat / action (i.e, practically none) but the individual elements just work really well.
Beyond that, I've had some fun in a lot of the open-world games like Dead Island and Dying Light, but that's not really what you're looking for.
If you do find some new games in this area, do let us know, as I'm always on the look out for them.
