I've had straight six petrol cars all my adult life , and one V8 petrol.
In the last year I got my first ever Diesel , a straight five E250 , then changed it for the C270 , another straight five .
I'd have to say , of the two diesels , the normally aspirated E250 was the better drive and sounded better - almost like a six cylinder petrol once on the move , but I sold it on due to a gearbox fault . The C270 , despite being a CDI turbo diesel , more powerful etc , just feels 'flat' and never has any feeling of excitement to it ; it is just a bore to drive and doesn't even make a nice noise , just a bland , subdued , constant drone in the background . It returns good mpg , but the downside has been big garage bills for diesel injectors which have wiped out the fuel economy savings .
Like you, I have driven 6 pots, straight and V, petrol and diesel, for many years. My exception was a CLK 270CDi with 5 pots. Again, like you, I found it unrewarding. My current C320 CDi is a different engine entirely. Very quick, very smooth 7 speed box and an average of 43m/g over 56,000 miles with no additional costs beyond servicing.
At the time of purchase I could have bought a 320 petrol for the same price with an average mpg of ~30m/g over the same mileage. I reckon that I have saved £4,600 worth of pre-tax income by choosing the diesel.