While I admit that a main character's death can be done well, I don't enjoy those endings.
It really has nothing to do with how well it's done or how meaningful it is. I have always associated the main character's death with the game over screen dating all the way back to Mario Bros. on the nes. It's so deeply ingrained into my gaming experience that any ending in which my character dies feels like I've failed. Regardless of saving the world, I've failed to preserve my main characters life as I have been doing throughout the game.
I don't find it 'cheap', I just find it depressing that I succeeded through failure.
I still love the fairytale cliche of a small-town-boy saving the world, rescuing the princess, marrying her and becoming king.