My opinion on the story DLCs is that they're all worth it.
BTDS makes ME1 feel complete by having the Batarians make their first appearance. If you felt it a bit annoying that they got an codex entry, but didn't get any appearance in the standard game, then download this DLC.
I have never played Pinnacle Station. It's regarded by most as the worst DLC for the ME trilogy. It's very combat-focused, and takes place on Pinnacle Station, an arena-sort of place. Since I often enjoy things most others don't, I might have liked this DLC, but i'm not sure. Shepard also gets his/her own apartment in this DLC.
Stolen Thief and Price of Revenge adds new squadmates. Thief adds Kasumi, a master thief. Revenge adds Zaeed, an elite mercenary. Zaeed is free, so you might as well download that. Both squadmates come with their own loyalty missions, so you get some new locations too!
Normandy Crash Site is a free DLC that let's you explore the burial ground of the original Normandy. It's a short DLC, and the mission itself is just a "collection mission", but if you want a little ME1 nostalgia, you should try.
Firewalker adds a new vehicle to replace the Mako. People are pretty divided on this, but it won't cost you anything.
Overlord adds a nice little story, and is a MUST if you love exploration, like me.
LOTSB is highly praised by most, and lets you have Liara as a temporary squadmate. You also finally find out who or what the Shadow Broker is.
Arrival bridges ME2 and ME3, and builds up suspense to the Reapers arrival. Without this DLC, there is no buildup. The Reapers just arrive at the start of ME3, and thus, the experience feels lesser for it. That's at least how I felt. Note that Arrival is a solo-experience though, so Shepard is going in alone. You can't take your squadmates with you.
From Ashes adds Javik, an actual living Prothean. The mission itself isn't great, if you ask me, but the permanent squadmate it provides is interesting, and has a lot to say if you bring him to the Thessia-mission.
Leviathan goes in-depth to explain the reapers origins. It is a very lore-focused DLC, and it attempts to foreshadow the ending, so it doesn't come so out of the blue.
The atmosphere of the DLC is very mysterious and creepy. If you felt that ME3 lacked that mysterious atmosphere from the previous installments, especially ME1, then you should probably try this DLC.
Omega is an action-oriented DLC, which you may or may not like. Personally, I loved it, but I like Aria and Omega, so i'm biased. Note that you can't take your squadmates with you on this mission. Instead you get two temporary squadmates: Aria and a female Turian. If you have ever wondered how a female Turian looks like, then you might like this DLC, just for that.
Citadel is, in my not so humble opinion, the best DLC for the ME trilogy. In this DLC, you say a final-goodbye to your crew from all three games (Aside from Legion, Mordin and Morinth, who does not show up in the DLC - Mordin still has some content though).
This DLC adds a new hub-area, lets you have Wrex as a temporary squadmate through the DLC (Seriously, the DLC is worth it just for that!), and gives you the opportunity to throw a party with your friends from across the three games!
In the Armax Arena, you can join up with some of the ME2 squadmates to combat other enemy factions (including the Collectors from ME2!).
This is the ME trilogy's final DLC, and it is a pure "have-fun" DLC, with a lighthearted tone, and your squadmates as the focus. It really adds so much to the ME3 experience.
That's my opinion on the trilogy DLC.