Two of the best DLC's ever made in my opinion were The Shadow Broker for ME2, and The Citadel for ME3, both were story driven DLCs that added to the base game content, with The Citadel being what many players considered to be the real ending of the ME Trilogy.
Other DLC's I felt added much story content to the base game:
The Witcher 3: Blood & Wine, Hearts of Stone
Fallout 4: Far Harbor
Skyrim: Dawnguard, Dragon Born
Obivion: Shivering Isles
Fallout New Vegas: Honest Hearts, Dead Money, Old World Blues
Neverwinter Nights 2: Mask of the Betrayer
Baldur's Gate 2: Throne of Bhaal
Dragon Age Origins: Awakening
Dragon Age Inquisition: Jaws of Hakkon, Trespasser
Neverwinter Nights 1: Shadows of Underentide, Hordes of the Underdark