When I'm up north, I'll often drink water straight from the lake, especially if I'm crossing lakes on the SUP which can amount to a full day of paddling. It tastes quite good!
I won't drink water from Lake Ontario, but I have gulped down my fair share of Lake Huron water.
My general rule of thumb these days, is that I won't eat a fish from freshwater if I wouldn't drink the water, and vice versa.