The biggest reason I didn't like that turn of events was because the character in question, Levin, was one of my favorite characters in the game up to that point. He was an excellent unit commander and had a very funny and entertaining personality that played well off of the other characters in the game. Since he was one of the characters who joined early in the game and never really left the group of characters who were present for every scene, he was always around too. Worst of all, there was no sign of his evil agenda leading up to his betrayal. While his explanation of the truth did tie up some loose plot threads, there was never any hint in those scenes that Levin was the one responsible for those evil deeds.
The point of this turn of events is probably to get the player to want to beat the crap of Levin, but that wasn't what I felt when I played through this part of the game. For a little while, I thought I had been kicked onto a bad ending track or something. All I really wanted was to get Levin back. Levin's betrayal actually crossed the line from "telling a good story" to outright fan abuse. It almost turned me off from playing the game, if it wasn't for all of the momentum I had already built up by playing the game so much.
There are other examples of this kind of betrayal in other games. One example I can think of is in Mega Man Command Mission. The first character you recruit in the game is Spider, a new character original to that game and a fairly fun and interesting one at that. However, later in the game, Spider nobly sacrifices his life to save the rest of the cast. Near the end of the game, Colonel Redips, the person who was giving orders to the heroes the entire game, turns around and betrays them by attacking them and killing one of the supporting cast. Then Redips goes on to reveal that he was actually Spider (or rather that Spider was really Redips the whole time). Once again, there was very little lead up to this revelation, and in this case, very little reason for it. It just bugged me. Redips's betrayal was sufficient motivation for the player to not like him without needing to undermine Spider's sacrifice.
However, there are ways to use a Mole plot effectively, without turning the plot into an exercise in fan abuse. One game that pulled it of well was Super Robot Taisen: Original Generation with the character of Ingram. Like Levin, Ingram was a major character from early in the game. However, his betrayal was much more enjoyable. The biggest difference is that I saw Ingram's betrayal coming from very early in the game. Ingram used his power to manipulate people, kept secrets from the main heroes, and obviously had various schemes going on behind the scenes. By the time he betrayed the other characters, I had already seen it coming, and I actually was looking forward to it. Ingram's betrayal meant that I now had my chance to beat his face in. It helped make the stage where he betrays the party a fun and exciting one.
The big difference between Levin and Ingram is in how the developers led up to the betrayal. Levin's felt tacked on, forced and out of character, and he was likable. Ingram's betrayal felt a lot more like a natural outcome of his personality, and he was set up to be a villain from early on in the game. If you try and pull off a Mole plot without proper development and lead in, it will just fall flat, or just make the players hate the plot of the game.