I'm pretty sure I've only read "Dune" and nothing else in the series. I didn't know there were even more than 1 book until the last movie was being hyped up.
Are they as good? Better? Weirder? What ends up being the actual main plot of the story as a whole?
God Emperor is essentially a fever dream. I was like WTF while reading it but I love how off the rails it went.
The last couple are kind of meandering and forgettable.
The immediate prequels (house books) are a fun read and plotted well, but his kid isn't as good of a writer as poppa Herbert
The legends books are garbage
The last 2 books that tie up the original series (hunters and sandworms) are not good and are infuriating in that elements from the legends books are reintroduced as deus ex machina bullshit. I saw the twist coming halfway through the last book.
I never read any of the other gap filler books that have been written in that universe. It got tiresome
Everything up until God Emperor is worthwhile with the prequels being a fun appetizer. Read the synopses of the others if you need to know what happens
As with most sci-fi the author gets loopier in the later books. That being said:
Dune: masterpiece of philosophy, one of the best books ever put to print
Dune Messiah: a worthy sequel and must read after the first book; completes Paul's arc
Children of Dune: more plot driven than the first, but still thematically rich and entertaining.
God Emperor of Dune: the most divisive of the books: you love it or you hate it. I am in the love it camp, the book is unhinged and the themes are marvelous. This is where I'd stop a read of the series.
Chapterhouse and the other (Heretics?): forgettable in my opinion, simply because I've forgotten them. Later book fan opinions welcome.
anything Brian Herbert: not terrible but not awfully good either. Makes for decent light reading I guess, and there's good lore building in some of the books despite some unforgivable retcons (Agemmemnon, sigh)
Heretics and Chapterhouse are also weirdly horny. Sure, sex was always a big part of how the Bene Gesserit operated, but when they were made the hero faction it got... weird...
Those two books also fully raised Duncan to be the main character, though, and they introduced Miles Teg, so they have that going for them.
Consider the first three books one trilogy that completes a story. It ends with a cliffhanger that picks up millenia later with the next three. The two written by Frank's son "finish" the main story.
I enjoyed all of them and will reread them at some point. Of all of them, chapterhouse was the weirdest of them.
Children of Dune is the only one i have to grin and bear. I love Good Emperor through Chapterhouse better than Messiah and only slightly less than the original.