One series I haven’t seen recommended yet is Alastair Reynolds novels. Revelation Space is a wonderful series, and if you want to start with a standalone story House of Suns and Diamond Dogs are great choices.
For lighter reading there’s also the Murderbot Diaries by Martha Wells.
There’s other older series that may appeal to you: Vatta’s War and Vorkosigan Saga conf to mind.
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.