I've only played three Castlevania games, Dracula X chronicles (Rondo of blood remake), Lords of Shadow, and Circle of the Moon.
I would say Lords of shadow is my favourite out of those, followed very closely by Rondo of blood.
I only played Circle of the moon for five minutes, and I didn't find it all that good.
EDIT: I also remember playing vampire's kiss (Snes version of Rondo of blood) and Castlevania 64 on a emulator, and both of them are god awful.