I got the feeling from the OP that he had played both Uncharted games, but if not, Uncharted 2 is everything he was looking for: great storytelling, medium length game, and incredible action gameplay.
Well, to me, the two best games of this console generation are Demon's Souls and Valkyria Chronicles. Demon's Souls has very little story to speak of, though, and Valkyria Chronicles is a strategy RPG. I would defnitely check them out regardless, though. For what you're looking for: Batman Arkham Asylum (just what you're looking for, I think) Elder Scrolls Oblivion (very very long, and open world, so the storytelling isn't very tight, but still very very good) Fallout 3 (same as Oblivion) Condemned 2 (best horror game I've played) I hear a lot of people liked Enslaved also, but I haven't played it yet.
Some good stuff mentioned. I finished the story mode for Catherine over the weekend, and it sort of fits your criteria. It is a puzzle game, not an action game, although I guess it has more action that most puzzle games. Also has some "adventure" elements, similar to the Persona series from Atlus. Very strong emphasis on story (more like a visual novel with some puzzle gameplay). Very unique game, from the puzzle gameplay to the storyline (not shooting up aliens, saving the world/universe, battling orcs, etc.). Basic setup for the story has probably been done before (more so in other forms of media, not so much in games), but Atlus has their own twist on it. Game length is probably around the middle. Longer than the short action/FPS games that flood the market, but not quite as long as some epic RPGs (though maybe it is that long if you do all the extra stuff). <iframe width="560" height="349" src="http://www.youtube.com/embed/cARACjcANX0" frameborder="0" allowfullscreen></iframe> Also, Shoji Meguro soundtrack.
Thanks everyone for your replies. I went to gamestop today and decided to do a 7 day rent on mafia 2 since I heard it was good but short. I also had a coupon for buy 2 get 1 free and I had a gift card so I basically got 3 other games for free because I traded in games to get 40 dollars credit: darksiders and mass effect 2 as others mentioned here as well as asassins creed brotherhood to complete the series so far. If I don't like these though, I will definitely return them and get something else recommended by clutchfans.
