The very best, like no other DLC ever was:
Bloodborne: The Old Hunters: Challenging and hauntingly beautiful, even more so than the main game, which already excells in those areas. This to me is the gold standard of what a DLC should aim to be. Add to it that in the fight against Lady Maria it gave us - what I personally consider - The very best boss fight of this gen so far. Dark Souls 3's DLC are fantastic too, but for the sake of staying with 1 game per developer I gotta choose this one, because it is that good.
Witcher 3: Blood and wine: Fantastic setting and very good story, This adds what? 40-50hrs to an already amazing game. Hearts of Stome was good but I find Bn'W to be the best of the two.
Monster Hunter World: Iceborne: Probably the game I've played the most this gen, adds a huge amount of new monsters, new maps, slightly different mechanics with the Clutch Claw, I cant get tired of it. Bit expensive but it adds so many more hours of gameplay that the price is justified for me.
Mario Kart 8: Character/Track packs. Not much to explain here I hope.
Im unsure if the Grimm Troupe of Hollow Knight and the Treasure Trove from Shovel Knight should count as DLC since I think of the more as updates, but all the same a little shout out to those fantastic games.
The very worst like no other DLC should ever be:
Spider Man the City that never sleeps: Similar sentiment to the OP. Not much to add here.
Skyrim: Hearthfire: Absolutely everything in this add on is boring to me and feels very out of place from the main game IMO.
Final Fantasy XV: Every single episodic dlc. Wont expand much on this, they just dont add to the game in any significant way. This game was in development for what, 10, 11 years? No reason for these to not be part of the main game.
Other than that I'd completely ignore any downloadable item/piece of equipment that is not free. From BOTW to the Tales series - any time a game uses this quick cashgrab practice annoys me. Another point in favor to Witcher 3 thats gives them for free.