Gaming

• Bioshock Infinite
• Return of the Obra Dinn (You'll have to work to figure out the story, but that's the whole point of the game.)
• Undertale (I know it doesn't look so good on the surface, but it's brilliant once you get into it.)
• Guardians of the Galaxy (Dumb fun popcorn game with a decent story.)
• Hi-Fi Rush (A little bit lighter in the story department, but an absolute freakin' blast to play.)

How do you feel about classic adventure or walking sim type games? If you're willing to trade in shooting for talking and puzzles, there's some fantastic stories there.

• LucasArts' adventure games: (Older, more comedic, but some of the greats, and many have remakes available.)
  • Grim Fandango
  • Full Throttle
  • Monkey Island
  • The Dig
  • Fate of Atlantis
• Wadjet Eye adventure games: (Newer, but emulating older styles. Darker than LucasArts.)
  • Unavowed
  • Technobabylon
  • The Excavation of Hobbs Barrow
• Other excellent adventure games:
  • The Longest Journey
  • Life is Strange series
  • Broken Sword 1-5
• Walking sims: (All story and exploration, very little challenge.)
  • What Remains of Edith Finch
  • Firewatch

A lot of great games already mentioned here. If you want to try something a little different. Yakuza 0 has a bonkers story that is weird and wonderful, but very Japanese.

Tacoma and Gone Home are great stories and well worth a look.

I also rate the Max Payne series for some pulpy fun story with lots of shooty in between.

Prey 2017, Alpha Protocol, Deus Ex HR, Vampires the Masquerade Bloodlines, Mass Effect, Disco Elysium, FF7 Remake, Guardians of the Galaxy, Rage 1, Spec ops: the line, the witcher 1/2, all the Yakuza games

If you haven’t played it yet, the Mass Effect trilogy. The Legendary Edition runs well on Steam Deck ( one of my coworkers is playing it now on his), and the story is really good.

Since you liked Alan Wake, also look at Control - it’s even set in the same universe as Alan Wake is, too.

If you like horror, there’s also Doki Doki Literature Club.

Disco Elysium, Weird West and Trails in The Sky FC are great narrative driven games. They deal with a lot of different topics in mature ways, hoping you get some good recs out of the thread

You might enjoy Control.

It's by the same devs as Alan Wake and has an Alan Wake tie in DLC since they take place in the same universe. It's fairly linear with some optional side missions. The setting is similar to SCP if you're familiar with that. You're in an office building dedicated to an organization that contains extra-dimensional threats and there's been a breach that has to be contained.
Story is pretty straight forward but there are some humorous notes and videos you can find that help explain the primary story and some of the objects that are being contained in the bureau.

Metro Exodus!

Heaven's Vault was fantastic and can be played multiple times to get different versions of the story. It's also on sale right now. What I thought was especially well done is how they tied the narrative to replaying aspect.

Tears of the Kingdom on Ryujinx but you nainly focus on the Underground part?

So this is way off genre but I will throw it out because it is definitely a mystery that unfolds and is told to you, not atmosphere story telling.. persona 5 is great fun and checks those boxes.

A more horror one would be a plagues tale although I don't recall there being much of a story, I remember the experience feeling a lot like story driven games and light is a big factor much like Alan wake.

Maybe neither will be of interest to you, sharing just in case and for lurkers.

A couple that popped to mind from my library are Pillars of the Earth and the Banner Saga. I haven't played a lot of either because I prefer open games and get my narratives from books instead, but they were both spoken relatively well of when I looked at them.

Pyre kind of counts, too, though the story and gameplay are both kind of weird. It's decent, just unique.

Some of my favorite stories in games would be BioShock Infinite (mentioned elsewhere in this thread), Planescape: Torment (lots of reading, but very good), Spec Ops: The Line (stick with it past the first few hours...it picks up), and the Metal Gear Solid series (which, fortunately, are just becoming officially available on Steam very soon). And of course, for the type of game you're looking for, it would be odd if I left out the likes of Half-Life.

If you want a campy, thrilling, exciting story that knows it's dumb and doesn't take itself too seriously, I'd highly recommend the Mortal Kombat games. You'll have to pirate Mortal Kombat 9 (also known as "Mortal Kombat 2011" or just "Mortal Kombat") because it's no longer available for sale, but Mortal Kombat X and Mortal Kombat 11 continue off of it and lead into the upcoming Mortal Kombat 1. Likewise, from the same developers, the DC fighting games Injustice have really good story modes as well. I don't like much DC super hero stuff, but Injustice 1 (Gods Among Us) is maybe my favorite thing ever done with Superman. As for Batman, the Arkham series is held in high esteem, and my favorite of those is Batman: Arkham Asylum.

I'm also presently in the middle of Baldur's Gate: Enhanced Edition for the first time, and while the story isn't exactly blowing my mind, it's intriguing and keeping me interested leading up to the release of Baldur's Gate 3 in a few months.

I love some horror games

Amnesia series
Layers of Fear series
Blair Witch
Observer
Observation
The Medium
Stories Untold
Killer Frequency
Alien: Isolation
Daymare: 1998
Deadspace series
Detention
The Evil Within series
Infliction
Outlast series
Visage
Soma

Any of the shadowrun games on steam are great.
Disco elysium is also great.

Surprised no one mentioned Enderal yet. It's a total conversion mod for Skyrim (special edition) and it really fits the bill of mystery/horror. It's my favorite game from a narrative point of view; the soundtrack and voice acting is also excellent, and I have re-listened it long after finishing the game. The only issue would be that you mentioned you would prefer a more linear experience, rather than an open world. The main quest is fairly linear, but you can still go off the rails and explore - but the pull of the story will always set you back on track.

One of my favorite tracks, which summarizes quite well the atmosphere of the game - https://www.youtube.com/watch?v=JKLkOnvFG5w
Trailer - https://www.youtube.com/watch?v=_DHkmHBPs2Q

ProtonDB lists it as playable on SteamDeck; the game itself is free, although you will need Skyrim Special edition (which is around 10$ on Steam right now).

The first season of The Walking Dead was very good, if you like Until Dawn. Firewatch is also very good, its kind of short, but I personally got sucked into the story. If your looking for old school style RPGS ( OG Fallout/Baulder's Gate) Tyranny and Pillars of Eternity are good.