As IO540 points out, it's all about the type of aircraft and mission you are planning to fly with it. The problem with Rotax - as I see it - they currently aren't capable or interested in playing with Lycoming in Continental with anything that requires more than 115hp - for ex. C172/PA28 or heavier. Agreed, you cannot put 150 kg engine into a 400kg aircraft, but that doesn't make Rotax all-in-all better than Lycoming or TCM engines - it's like comparing light and relatively powerful two-stroke engine of a lawnmower with Rotax and saying it's better, because it's lighter. As far as Mogas use goes, my position remains the same - it's not useful for longer flights to other countries, since it is rarely found at international airports - Jet A-1 seems to be the long-term winner here, not Mogas.
From my experience (with Thielert, just for clarification), water cooling in aviation engines isn't that great. In summer, you have to make cruise climbs to avoid coolant temperature to go over the limit and in the winter you have to make very shallow descents - again to stay within the limits, whereas with conventional engine you have the advantage of manual mixture control to keep CHTs and their rate of change on a normal level. Just as said before, water cooling isn't a weight problem if the engine volume (and thus cooling area) is small. As soon as you increase volume to 3 or 4 liters (sure, VW produces 1.4 TFSI with 175hp output, but would it last 2000 hours on 75% power?), the water cooling becomes very heavy in comparison to traditional air cooling. Of course, if you take reliability in account, loss of cooling liquid could limit your maximum power (to prevent overheat) significantly, while air cooling is at least partly efficient even when at speeds near Vs.
Concerning the fuel consumption during various mission profiles (training, touring, etc.), if argument against Lycontinentals is that most people run them fully rich (especially during training, after-PPL renting etc.) and thus fuel consumption is much higher: I personally think it isn't the engine's or manufacturer's fault, but the instructor should have a word with himself. Sadly, most people think (and is also written in some Piper's POHs) that the full rich mixture is the "best and safest" for the engine. I'm not going to get into discussion about how fast an aircraft with Rotax goes in cruise - most Rotax installation are very modern, thus being much more aerodynamic than average spamcan, not to mention few hundred kilos lighter. With oil consumption, it's the same story: engine with higher displacement that produces much greater power will always burn more oil - unfortunately the engineering hasn't yet developed to such an extent that it would be possible to completely seal the combustion chamber and at the same time reduce piston friction to zero.