Any Linux OS with Steam installed, but yes most likely the same on SteamOS.

My experience with games on Steam:

• 75% of games: Click to Download, click Play, nothing more needed.
• 20% of games will work, might need to select a special Proton version or put in one command.
• 4% of games need tweaks specific to that game, to enable multiplayer, get around certain crashes etc., OR the game works fine but multiplayer doesn't work at all due to anticheat set to block Wine users.
• 1% of games don't work at all due to either anticheat, DRM or another problem.

Workshop works just fine, 3rd party mods will often work, but then you'll need to get into the weeds of Wine to properly set it up, since installers are separate to the game.