I tend to like free-roaming games that incorporate water, where you have to get an item in order to travel past or into the water, like Link to the Past or [Wonder Boy in Monster World]

While not in love with any other water experiences from other games, I really do not mind them.