Literature

Peter F. Hamilton's books may fit the bill: Futuristic, not hopeless/dystopic, and the main characters tend to make reasonable decisions. Be wrned though that he favours deus ex machina conclusions. Most will suggest Pandora's Star as a starting point (with good reason, as the Commonwealth Saga is quite expansive), but it does not have to be. I personally read the Night's Dawn trilogy first. The Salvation trilogy also stands on its own, and for a completely standalone book Great North Road was a good read.

Adrian Tchaikovsky is another wonderful author! the Children of Time and Final Architecture series were quite enjoyable.

Redemption Space (Alastair Reynolds) is another series one that I like to recommend. Closer to The Expanse. House of Suns also is a great read by the same author, as are several of his other stories.

The White Space books by Elizabeth Bear should be on your reading list.

Vorkosigan Saga (Lois McMaster Bujold) is a bit dated but similar to Vatta's War in the earlier books. Later on the plot tends to be more along the lines of whodunnit mystery... in space.

And let's not forget another scifi favourite, Iain M. Banks! The Culture series are great of course, but I liked The Algebraist the best.

Asimov's Foundation series is pretty solid. Mostly the same main character for a few of them and then it's his relatives / friends for the rest. Lots of "space politics" very similar to Dune but with a little less war and more science adventuring. I especially liked the first book in the series. The prequels are good but not required reading. It's one trilogy with two prequels and two sequels released decades later.

edit: I also strongly recommend "The World at the End of Time," which has a couple narratives intertwined but is mostly about one man who gets cryogenically frozen a couple times. Very adventurous and pretty sad. Lots of speculative future visions in that one.

I've been plowing through Three Body Problem series by Cixin Liu recently and it's been really great. I'm on the home stretch of the last book and so excited to finish it.

All about space exploration, first contact, invasion, advancement and philosophy. A really great thinking book and leaves me with lots of uncomfortable feelings to ponder over. The pacing of the book took a little bit to get used to but it does well to help grapple with the time and space scale it is working with.

Not sure if it tickles your fancy, but if you're in the mood for a humorous space adventure with an elaborate story and serious undertones, give Ben Yahtzee Croshaw's Will Save The Galaxy For Food a try.

Also, I haven't seen Adrift and the Outer Earth Trilogy by Rob Boffard being mentioned here. Especially the former managed to instill a sense of dread in me while the story unfolded. The latter is a rather long read, though the first book (Tracer) is self-contained.

ETA: a somewhat different style, but if you like futuristic worlds / galaxies with tons of intricate details, try The Long Way to a Small, Angry Planet by Becky Chambers. Her world (universe, in fact) is incredibly rich in detail and diversity. Each alien race she introduces has its own complex backstory, values, language and culture.
The other books in the series didn't grip me like this one - they add a lot of background, and in that she's a genius, but for my taste the story was somewhat lacking.

Alright, we've got some overlap here, let's see...

• The Red Rising Saga. I'm working through book 6 right now as an audiobook and I'm sneaking in a few minutes wherever I can. Definitely expands its scope as the series goes on and while I feel like I'm losing context for some of the new/returning characters at this point, I can follow enough to go along with it. The main character is born into the lowest caste of the society and works to infiltrate the highest caste. It's a long ride and ebbs and flows from hopelessness to triumph throughout the course of it.
• The Combat Codes Saga. Probably closer to science-fantasy then fiction, but an interesting idea about nations using hand-to-hand combat to settle wars, territory, etc...I have only read the first book so far but I enjoyed it a lot.
• Alex Benedict - I would encourage this more as a filler or inbetween books series. Binging all of the books can make them feel very samey. The core idea is that all of the books except the first one are told from the perspective of a colleague/assistant/"jill of all trades" woman who works with one of the most famous artifact hunters in the far future. Each book is essentially chasing an archeological mystery of some sort.
• The Jubilee Cycle - I found the first book a long time ago at random in one of those discount bookstores and picked it up based on the cover alone. It's about a future where everything you do costs you money, to the point where political parties debate whether or not autonomic functions like breathing should cost money. The prose is a little dry and the author works as a translator, but I enjoyed the world that he built up as the main character peels back the layers of this society after he gets essentially bankrupted by a mysterious and unknown transaction.
• Teixcalaan - Can't link the series for some reason. The main character is an ambassador to the ruling empire of the galaxy, trying to figure out who killed her predecessor and a conspiracy surrounding him. It felt very dense when I started it but I enjoyed it a lot!

Have you tried CJ Cherryh, particularly the Alliance-Union universe? If you'd like to start with a longer novel, I'd suggest Downbelow Station or perhaps Cyteen (though DS is one of my favorites) if you'd like a faster and less deep introduction, I'd suggest Merchanter's Luck or The Pride of Chanur.

You've got some moderately highbrow and transhumanist stuff in there; have you tried Greg Egan? The two starting places I like to recommend are the Clockwork Rocket books (natives of a universe with alternate physics explore it and figure out what's going on, kind of Flatland turned up to 11.. and then up to 121..), and Permutation City which I think will meet your "some very interesting ideas" and then keep accelerating.

Frank Herberts 'WorShip' saga is one of my personal favorites after Dune. Although the first book in the series, 'Destination: Void' is a hard slow burn.

All the 4 books can be read as individuel stories, but just give a "bigger picture" if read as a series.

There's also something to be said about some of the more 'Classic' Sci-Fi books, like: 'Starship Troopers', Do Androids Dream...' and '2001'.

'Annihilation' is another really good book. I haven't read the sequels yet, so I can't speak on those.

'The Void' trilogy by Peter F. Hamilton is another series that might interest you. It's sci-fi with a hard 'S' if remember correctly.

You should check out Arkady Martine's A Memory Called Empire and Ann Leckie's Ancillary trilogy.

I see a number of things on your list I also enjoy (and some I haven't seen so thanks for the recs!).

I've got a pretty long list, but I think only the first three are scifi in the space faring long run series sense. Including some others in the genre more broadly, in case any look interesting too:

• The Final Architecture, Adrian Tchaikovsky
• Altered Carbon, Richard K Morgan
• A deepness in the sky, Vernor Vinge
• The Acts of Caine, Matthew Stover
• Roadside Picnic, Arkady Strugatsky
• Broken Earth Trilogy, NK Jemison
• Nexus Trilogy, Ramez Naam
• Old man's War, John Scalzi

Iain M Banks's Culture series; Consider Phlebas, for instance, or The Player of Games, seem to be pretty much what you're asking for.

You might enjoy Larry Niven's "Known Space" series? It's less one long story than many, many stories (some more interconnected than others) set in the same universe, but there's a lot of it. Some stories are darker than others, but overall the tone is optimistic and the characters have their flaws but don't suffer from the "stumbling from one flawed decision to the next one" problem.

I suspect some of the stories won't have aged well, given some of them were written over 50 years ago, but Niven plays with a lot of interesting ideas, and I have never encountered a sci-fi author that writes genuinely alien aliens the way he does. There's also lots of exploring space, futuristic cities, and alien landscapes.

Terra Ignota series by Ada Palmer. Interesting world where people choose their nationality and legal structure independent of physical borders.

C.J. Cherryh's Foreigner series spans 22 volumes (currently). I enjoyed it immensely.

While not sci-fi, Patrick O'Brian's Aubrey-Maturin series is great for "on a journey" across the seas of the Napoleonic era.

I haven't read a lot of science fiction recently, but if you like adventure and world building, then March Upcountry is a great read. The books are short, but it's a series, so all together it makes up a thick book. :)

Red Rising by Pierce Brown. You seem to like a lot of the same books I do. I just found this series and I loved it.

Another vote for Cherryh - pretty much anything by Cherryh. And in the "journey" department, perhaps also look at John Varley's Gaia trilogy (Titan, Wizard, Demon)? (Probably falls into your "excessive violence and some smut" category)

You might also try the "far future/dying Earth" genre as a way of getting the exploration without necessarily being tied to the space/hard sf milieu. I think the most awarded member of this subgenre (and I liked it quite a bit) is Gene Wolfe's three Sun series (Book of the New Sun, Book of the Long Sun, Book of the Short Sun).

You've already had a recommondation for most of what I would suggest to you, but I will happily second the suggestions for the Revelation Space series by Alastair Reynolds, the Teixcalaan series by Arkady Martine, and the Imperial Raadch/Ancillary series by Ann Leckie. All have excellent worldbuilding and tell stories that depend heavily upon how their characters interface with the worlds they inhabit.

A little pulpier in tone, but still very well put together, I'd suggest as well the Wayfarers series by Becky Chambers, and especially the Locked Tomb series by Tamsyn Muir. The latter is a bit more fantastic space opera as opposed to some of the harder sci-fi you've mentioned, but Muir knows how to write a setting that is absolutely dripping in gothic horror, and still take you on an emotional roller coaster fully of highs, lows, and humor as you read it. It seems to be a bit of a love-it-or-hate-it series from the other conversations I've had about it, but I love it and I'd be remiss not to suggest it.

I'd also suggest, if you're not averse, dipping your toe into the fantasy genre as well. There's a broad range of authors there who have done excellent work building fantasy worlds that are structurally deep and compelling, and have many science-fictional qualities. Along these lines I'd suggest Robert Jackson Bennett's Founders trilogy, or N. K. Jemison's Broken Earth trilogy -- though, fair warning, both of these broke me in the end emotionally. Worth it, though!