DLC both bothers me and delights me in equal measure.
There is no denying that some companies - I'm looking at you, NIS America - abuse the hell out of it, and by no means give you value for money...and that they have the sheer audacity to add trophies as part of the package is both insulting and infuriating. Disgaea D2 is easily the best example of the worst case scenario: characters that cost £2.39 a pop AND have a trophy attached to them. Meanwhile, Disgaea 3: Absence of Detention comes with ALL the DLC that was included with the third game - plus some extras - for less than what the DLC pack on the store costs! Similarly, both Tales of Graces f and Tales of Xillia charge outrageous prices for costume changes and attachments that do absolutely nothing. Whereas Tales of Symphonia Chronicles gives you a costume set for free just for having save data from one of the two titles! Talk about blatant double standards...
Similarly, it bugs me when I see substantial DLC within a week of release - or sometimes not even that - as well. That sort of content really should be in the main game, and the only excuse I can see is that developers just want to make more money. It might be their right to distribute their product however they want, but it's a serious problem with the industry in general.
If I'm going to pay for additional content, I expect it to enhance and/or extend my gaming experience significantly. Things like additional characters, overpowered weapons and costume changes I will refuse to pay for, because in previous generations that content would have been a part of the package, and they don't really add anything I can do without. Something like an expansion pack, that adds over an hour of playtime, I'll be more willing to pay for. Whilst I can do without it still, it keeps me playing the game for longer, adds a distraction from the main story - usually a relevant one - and, usually, there are additional characters/weapons/costumes/whatever I can get as a bonus anyway.
The best DLC I've played to date...hmmm...well, I really enjoyed the Mass Effect 3 expansions; some of them really weren't long enough, but they were reasonably priced and, whilst you didn't HAVE to play them to appreciate the story, they really built on the universe - especially the Prothean and Leviathan DLCs. Citadel was just plain fun. Omega was the weakest of the four, but it was a fairly lengthy distraction.