I doubt this will happen. As of right now, only certain types of mods can be placed in the mods folders, others need to be placed over game files to overwrite them. Also, unless the game becomes more moddable, I doubt we will see a huge amount of mods even though there are quite a few things you can mod.
I am not sure what the technical and security considerations are with using the Workshop. It seems like the issue of overwriting core game files can be solved by:
1. Loading the mod files in an order from a mod menu at startup
2. Ordering them in the mod folder this way
3. Using either the unaltered game file if no mod existed or the last file from the mod folder if one did
This puts stability at the mercy of modders, but people expect crashes from modded games to be the modder's fault.
I don't know what kind of security problem can occur with mods, and what kind of liability that puts Steam/Stardock with. I know the games that I have played with mods on the Workshop have all been created almost entirely with developer provided tools. I know that a lot of modding work is outside Stardock's provided tools and involves writing XML files. I know enough about security to know there is probably a danger there, but that is not my area of speciality.
I think visibility and the size of the audience adds huge incentives to modders. Modding is a showcase of ingenuity and creativity, multiplying the size of the audience through user-convenience would seem to be exactly what modders want.