I can really only second a bunch of what's been said...
Shooters: BioShock is fantastic. Incredible story, good control scheme, excellent balance and it has one of the best atmospheres ever created in a video game. Halo 3 was good, but I like 1 the best...
RPGs: Mass Effect is great. BioWare always put out the best in RPG and story. Fable 2 was also lots of fun with plenty of wacky humor (then again, I loved #1. I'm a Lionhead fan
). Oblivion is fun, but can get real old, real fast (especially with a set of clothes that gives you constant 100% Chameleon
).
Horror: BioShock certainly falls into this category also, and with some freaky stuff to back it up. Your first 10 minutes in the hellhole that is Rapture will prove it. Dead Space is also supposed to be really good ( I haven't played it yet).
I'm not sure what category Assassin's Creed would fall under... It's a good game, though rather repetitive (and the ending is junk). But, there is something to be said for a game that lets you walk up to a city guard, stab them in broad daylight, and walk away with no one the wiser. And the rooftop chases can be lots of fun.
If you have a good enough PC, Spore is fun for anyone, though it can be a bit annoying, especially when you make other races mad in Space Age. NWN2 is a good PC RPG if you don't mind dealing with a number of bugs...
For XBox, KOTOR 1 & 2 are great RPGs, along with Jade Empire. I liked KOTOR 1's story better than 2 (But it seems to me that Obsidian just can't end a game...). Armed and Dangerous is a great game, not for its gameplay but for its script - Funniest game I've ever played. Psychonauts also was a big hit (received wonderfully, but poor sales; I haven't played it yet).