Out of all the standalone expansions for Dawn of War this is the one you want for the singleplayer campaign. The other games are also good in this regard, but Dark Crusade is the most replayable.
The lore is outdated by today's standards, especially for the necrons who were fleshed out a lot more later, but it's still a decent introduction to 40K video games.