Like some other people here, I'll go with Operation Raccoon City.
Yeah, I know. It's short. It has bugs. The AI can be... difficult at times, to say the least. There's not much of the old RE feeling left, even though the game is set in Raccoon City. The main cast consists of six Umbrella mooks that we'll never see again. Some parts of the original storyline have been twisted.
... But so what? It was clear right from the beginning that this wasn't going to be a canon classic survival horror title, so I don't understand what all the fuss about the shooter elements and the plot inconsistencies is about. If you take it for what it is, it can be quite enjoyable, and I'm saying this as a person who is usually not very interested in shooters.
Players who don't hate the game as much as others do, tend to say that the multiplayer is okay, but playing it on your own is nearly impossible. I can't agree on that. I've never played online so far, neither in campaign mode nor in the specific multiplayer modes, always on my own, yet I had much fun experimenting with the various characters, testing their abilities in the field, putting together different teams for different occasions, and killing monsters without having to think about it. Sometimes, especially after a long and exhausting day, this is all you need.
ORC is by far not among the best games I've ever played, and I'm glad I didn't pay the full price for it. But I still like it, even more than the canon titles RE4 and Code Veronica, and I think there are much worse games out there.