Unless a game is truly godawful, I'll at the very least rush to the end to see how the story ends. Then I'll never ever play it again. An exception to this is Halo. I didn't hate it, but I got so tremendously frustrated with trying to learn how to dodge the Hunters that I just gave up. I'm a PC gamer at heart and I didn't have the patience for it.
Of course, give me something like Super Mario and I'll play it endlessly, frustrated or not, even if I'm not actually enjoying it.
As to the modding thing, some games require more modding than others. For me, some modding is fun, and enhances the enjoyment of the game. Simply being able to tweak things you don't like, or improve upon game mechanics, is huge. However, there's a point, as ddmuse stated, that modding becomes a chore just to make a game playable. For example, I made very minor modifications to Morrowind apart from the Code Patch, (which was just engine improvements, and stuff like turning off that annoying plastic-wrap look on enchanted items) and enjoyed it greatly. Fallout 1/2 I would have modded more extensively, but to this date nobody's figured out all the quirks of the system yet. So mostly I just tweaked the armor/ammo settings. I made more extensive modifications to Oblivion, BG2, and IWD2, but still enjoyed those tons, and played them endlessly. Fallout 3 reached the breaking point for me and my modding. I was spending half of my time digging through game files, fixing bugs, and tweaking settings instead of actually playing the game.