As someone who has both read the visual novel and seen most of what came after, I can soundly say that no, the erotic content in the VN doesn't take away from the rest of the franchise's works.
Point the first: The sex scenes are in the minority of the game. In the three plot routes, only one of them has more than a single sex scene in it.
Point the second: The sex scenes are by far the worst-written parts of the VN. They're not as awful as the ones in Tsukihime, but they're still pretty bad. The rest of the VN is excellent, however.
Point the third: The sex scenes are, in fact, integral to the plot, in all cases tying to sex being an efficient way of transferring magical energy from one person to another in the Fate universe.
Point the fourth: For all that the presence of these scenes is integral to the story, their actual content is secondary. You can easily just skip over them, say in your head, "They have sex here," and you wouldn't lose anything. In fact, the PS2 and Vita ports/remakes of the VN replaced the sex scenes with alternate ones, and while the replacements really don't make much sense, even in-context, the fact that they were able to do that is rather telling.
I love the Fate/stay night visual novel (though I do not love its sex scenes, as they're rather poorly-done). The Fate/Zero anime is one of my favorite anime series. I'm looking forward to the eventual completion of the fan translation of Fate/hollow ataraxia. I enjoyed both Fate/Unlimited Codes and Fate/Extra. And while I think the old anime adaptation of the Fate route was terrible, and the old movie adaptation of Unlimited Blade Works was just mediocre, I am hyped to see the new Unlimited Blade Works anime.
Point the first: The sex scenes are in the minority of the game. In the three plot routes, only one of them has more than a single sex scene in it.
Point the second: The sex scenes are by far the worst-written parts of the VN. They're not as awful as the ones in Tsukihime, but they're still pretty bad. The rest of the VN is excellent, however.
Point the third: The sex scenes are, in fact, integral to the plot, in all cases tying to sex being an efficient way of transferring magical energy from one person to another in the Fate universe.
In the Fate route, Shiro and Saber do it so he can give her extra magical energy for an upcoming fight. In Unlimited Blade Works, Shiro and Rin do it so she can give him some extra for an upcoming fight. And in Heaven's Feel, Shiro and Sakura do it repeatedly over the course of the story because she needs a continuous supply of magic to keep from going insane. Heaven's Feel does also contain an erotic dream Shiro has about Rin, but the scene is intentionally more disturbing than erotic.
I love the Fate/stay night visual novel (though I do not love its sex scenes, as they're rather poorly-done). The Fate/Zero anime is one of my favorite anime series. I'm looking forward to the eventual completion of the fan translation of Fate/hollow ataraxia. I enjoyed both Fate/Unlimited Codes and Fate/Extra. And while I think the old anime adaptation of the Fate route was terrible, and the old movie adaptation of Unlimited Blade Works was just mediocre, I am hyped to see the new Unlimited Blade Works anime.