PPersonally I think the N64 titles captured the feel of ClassicVania in 3D near perfectly, and found the gameplay in LoD wonderful. If you disagree, which you are entitled to do, I see no reason to get so venomous and spiteful about it.
Amen. I third the notion that Legacy of Darkness (and the N64 era) delivered the best Castlevania in 3D by far.
N64 CV accomplishments:
*Multiple unique characters (up to four in LoD, two in CV64) with alternate levels/bosses
*Full 3D gameplay (ala Mario 64, so it's not "on-rails")
*Multiple endings based on performance
*Level Design features spatial depth (vertical and horizontal--not flat hallways)
*day-and-night cycles with time-sensitive events (like Simon's Quest)
*Weather effects (rain, lightning, moving clouds, and "fog"--the last one likely being a graphical shortcoming that actually helped)
*Dynamic, real-time lighting (next to candles, for instance)
*spot-on atmosphere
*death-defying platforming of all sorts (including ledge grabbing).
*environmental/enemy hazards (medusa heads, spikes, guillotines, buzz-saws, cannons).
*innovative survival-horror/suspense elements
*Vampires that pretend to be human and vampires as regular enemies besides bosses
*status changes, including poison and vampirism
*manageable questing with inventory items (meat, keys, cards, cure ampules, etc)
*interesting, involving plot (characters like Rosa, Vincent, Renon, Malice, Henry, etc)
*3D in-game cinemas
*Some voicing
*Unlockable alternate costumes
*Long and short-ranged attacks (IE: whip + sword)
*upgradable sub-weapons (in LoD)
*There is a useful slide and duck/crawl play mechanic
*Diverse mix of old and new enemies in 3D
Back to the topic at hand, it's hard to say which to play, as each caters to a slightly different type of player, and modern trends may affect one's experience. I would say Castlevania IV, III, Bloodlines, Belmont's Revenge, SotN, Adventure Rebirth, and Dracula X (SNES version) stand out, though. Honestly, you can't go 100% wrong with Castlevania games, so you'll find all sorts of things to like amidst a few painful quirks or awkward turns here and there. I had a serious problem with Nintendo Power dissing the N64 games. They can be rough around the edges, but they are great 3D Castlevania games if you take the time to learn their quirks and get over the technical limitations of the era they were made in. If you do dip into the N64 games, realize that Legacy of Darkness is an expanded reimagining/prequel of CV64. It's the more polished and complete game, with more scope, but it lacks a few of the charms and level designs that were interesting in CV64.
Ordering Castlevania is very, very hard. I'll give it my best shot, but a lot of this will be + or - as far as placement. (Keep in mind that I have not played Order of Ecclesia (DS) or Harmony of Despair (PS3/XBOX360), so I can't rank them). I am judging on an aggregate of overall atmosphere, fun, level design, and gameplay over a number of years, with an eye toward the old-school "core" of the franchise. (I will say that the "Metroid-esque" Castlevanias don't tend to hold up as well on replays as other titles, even if some of those other titles taken by themselves, like Legends, are really pretty poor). Others would rank them differently with different parameters. Currently, off the top of my head, I'd say:
1.) Castlevania III: Dracula's Curse
2.) Super Castlevania IV
3.) Castlevania: Dracula X
4.) Castlevania: Bloodlines
5.) Castlevania II (GB): Belmont's Revenge
6.) Castlevania II: Simon's Quest
7.) The Castlevania Adventure
8.) Castlevania (64)
9.) Castlevania: Legacy of Darkness
10.) Castlevania: The Adventure Rebirth
11.) Castlevania
12.) Castlevania: Circle of the Moon
13.) Castlevania: Symphony of the Night
14.) Castlevania: Rondo of Blood
15.) Castlevania: Chronicles
16.) Castlevania: Lament of Innocence
17.) Castlevania: Legends
18.) Castlevania: Aria of Sorrow
19.) Castlevania: Harmony of Dissonance
20.) Castlevania: Portrait of Ruin
21.) Castlevania Dawn of Sorrow
21.) Castlevania: Judgment
22.) Castlevania: Curse of Darkness
23.) Castlevania: Lords of Shadow