Not a great game, but not a terrible one either.
Most reviewers say the visuals are terrible. On the contrary; it's certainly not on the level of releases such as Arkham City or Gears of War, but neither does it look "bad" like Minecraft (which focuses on gameplay and is fun in it's own right, but that's beside the point). Not once while playing this game was I thinking "Man, this game looks like crap!", but perhaps I'm more forgiving than some - the people in the game, oddly enough, look like people, and that's good enough for me. Arguably, its' greatest asset(s) are the babes Duke meets, and they are still titillating, as they should be. The "dated and ugly visuals" that you've heard about are only a few years behind the curve, if that, and that opinion reeks of bias.
The gameplay itself is your typical first person shooter fare. It's not groundbreaking, unique, or even particularly well done, but it does what it set out to do; entertain. Could it have been better? Absolutely. I consider myself a fairly intelligent person, and I sincerely thought the game was amusing. Some of the quips from the women Duke meets and a few random encounters are actually really funny... but could it have been more funny? Sure. There are some genuinely fun "puzzle-type" games, as well, that involve being shrunk and having to find your way around an area, and even quite a few minigames that you can play (such as Pinball and Air Hockey). The random Duke quote also adds a little humor and flavor to the game. However, and aside from scripted sequences where he says something unique, he will eventually end up repeating himself.
On the flip side, there are a few things that could have been done better;
Limiting the player to two weapons is slightly annoying, but fortunately you'll be able to get through practically the whole game with your weapon(s) of choice (I'm a fan of the shotgun) - the only caveat being that bosses require explosives to kill, so you'll likely have to drop one and wait until you find another once the big-fight is over.
The only time I started getting bored was when I was forced to drive through several levels with a rather poorly modeled truck, stopping at random intervals to find gas, kill and blow up a bunch of junk, and then hop back in for more "joy-riding" on a pre-determined dirt path. I was slightly reminded of the Library level in Halo where I kept thinking "when will this torture end?!". Still, it wasn't as bad as that probably sounds. The environments were rather bland and didn't quite "fit" with the game at this particular point, either.
The game is short. I finished it in under 6 hours, maybe even less (give or take a few moments where I ogled some babes and/or tried to look up their skirts). It's linear, but I guess that should have been a given. At any rate, Duke is set on a path, and there's no room for exploration; get from point a to point b, murder a big alien, and then move on.
But with that being said; all in all, Duke came back with a decent shooter, with enough interactivity, and humor, to keep it interesting and entertaining. Of course it could have been better. If you were waiting for a golden, angel turd to fall from heaven, prepare to be disappointed. 12 years is a lot of time to wish and hope, and it's likely not going to deliver on what you wanted to the T if you've been following it through it's immaculate conception. But, that doesn't mean it's not worth the time to give it a try. You may be pleasently surprised... maybe not too much, but enough to enjoy it for what it has to offer.