They did not know that it would be that ineffective
The Dothraki are great warriors particularly in open field. The surprised look on Dany's face tells us that they didn't expect the outcome. They must have been expecting that the Dothraki might hold the dead for a while. If they lasted long enough to bring the White Walkers (not Wights) they could kill them.
We also see the Unsullied use long range weapons when the Dothraki charge. That might have been their plan. Hold the army of dead with Dothraki army and fire catapults. Note that lighting the trench was kind of a backup / retreat plan. They were ready to fight the army of dead outside the castle walls in the first place.
Dothraki are not good at defense
The Dothraki are better off fighting enemies than standing at defense. We have never seen Dothraki holding defensive formation much. They are better off attacking than holding a line.
Off screen explanations
- The dismissal of Dothraki would not showshows the viewers that the army of the dead is much more powerful and dangerous.
- It would also reduce the strength of Dany's army. Given that both of the remaining dragons are still alive this might be a way to give some advantage to Cersi in the upcoming battle.
- As pointed out by Chanandler Bong in comments, this could have been a way to subvert to viewers hopes. From Fandom site,
In the Inside the Episode featurette, the showrunners make no attempt to explain the in-universe reasoning behind this - just that they wanted a dramatic beat of the audience thinking the Dothraki might have some hope with their flaming Arakhs, only to then subvert this by having them wiped out.