Going back to the prehistory of civ (and computer gaming too!), in the game Empire submarines were slow but only destroyers could see them. Since only one unit could be in a square, transports were vulnerable (as were carriers, to a lesser degree), requiring several destroyers to "escort" it by spotting the slow submarines before they reached the transports. Battleships, while unable to spot submarines, could kill them. The game was much simpler than civ, but the origins of civ's combat system are from it, and so the basic balance -- and ship types -- in civ are the same as in Empire.
There are other changes, and there is no reason that civ's naval combat can't offer more tactical and strategic options. No reason at all to stick with a 30+ year old combat model for the modern navy, not when the land combat has a lot more options.
Stuff which could help out submarines:
Defensive withdrawal. Unlike any other unit, submarines can evade their attackers. This is reflected OK on the attack -- a good withdrawal chance upgradeable by flanking -- but not on defense. A submarine, if attacked, may escape via withdrawal. If it can share the square with a hostile unit, leave it there. But I expect it would have to be moved one square, likely at random, in order to do this in the game.
This would make submarines much more survivable. Sure, enough attackers will eventually catch and kill them, but using them wouldn't be so much of a suicide mission.
Modern submarines -- missile and attack submarines for certain -- are especially deadly against fleets and convoys in formation. Several targets can be engaged at once. Either give them collateral damage outright, or let them take the barrage upgrades like armor can.
Doing this lets them weaken a defending stack, and makes it more likely that the next submarine in a "pack" will actually get kills.
Now, I understand that letting submarines have a priority attack vs. transports would make transport survival a lot harder. Maybe if the attack worked sort of like an airstrike, and if the submarine was intercepted by a destroyer it would be back to a normal attack, this would still be balanced? Having a lot of good destroyers defending would be a priority, and missile cruisers/battleships wouldn't be very good transport/carrier escorts.
Without something like this, the only way for submarines to sink transports is just like with any other naval unit: destroy or damage all other defenders, so the transports are the weakest targets. If you are trying to fight a "wolf pack" war, this just isn't going to be easy to do, not without beefing up submarines a whole lot. Letting them have some sort of specific-target or transport attack (even like cavalry's flanking attack vs. artillery might work) would give them a very special role in combat, and make defending against them most important.
Giving submarines a couple first strikes is probably a good, simple way to simulate the difficulty of detecting them. In a sub vs sub fight they would cancel out, but it would be a big help vs. all other ships. If they get an undefended transport to hit, they'd have a decent chance to kill it with little or no damage, and thus able to continue their mission.
If submarines get beefed up to realistic levels, then countermeasures need to be added too. Allowing units an anti-submarine promotion would help. First, it could increase detection range for them. Second, it would give a bonus vs. them. Both sea and air units could get this. Air units, even on recon, should only have a one or two square search radius, not their whole visible range.
I'm hoping that the issue of modern era combat is something fixable for the next patch. It won't happen for this one -- it is done already -- but I don't think that it is beyond a patch level change.