Well I've played through most of the DLC now, completed The Assignment and The Executioner and halfway through The Consequence.
The Juli Kidman expansions are fantastic, a lot of backstory is filled in and clarified, including (minor spoilers that you can probably guess if you've played the main game):
spoiler (click to show/hide)
Exactly what the purpose of the STEM system is, how/why Ruvik was created and named as such, and exactly how STEM works and creates the game world of Evil Within.
The Kidman levels are also scary AF (much scarier than main game), and I've had some nightmares about them. Be warned!
The Executioner is a fun action spinoff. It seems to have had a mixed response from players, but I think it's been done well. Its a first-person brawler (kinda like Zeno Clash) where you play as Keeper, and open up a can of whoop-ass on those stupid zombies. The melee is simple (one attack button) but there are running attacks and a dodge button, and a good variety of weapons. Some of the boss fights are interesting and overall the combat is solid and satisfying. It's mostly pretty easy however there is a boss rush room of sorts in NG+ which is tough as nails, and I haven't been able to clear that one yet. The story additions are minor and kinda WTF. Still not a bad piece of content.
All of the content is loaded with unlockables and secret collectibles, and achievements add good replayability also. All up I'd say the content adds around 10-12 more hours of gameplay if you are going for full achievement completion. A great example of quality DLC and I wouldn't hesitate to recommend it. Bethesda could do well to re-release a GOTY edition.