**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][1] (not [Wights][2]) 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**

 1. The dismissal of Dothraki would not show the viewers that the army of the dead is much more powerful and dangerous.
 2. 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.
 3. As pointed out by Chanandler Bong in comments, this could have been a way to subvert to viewers hopes. From [Fandom site][3], 

> 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.

  [1]: https://gameofthrones.fandom.com/wiki/White_Walkers
  [2]: https://gameofthrones.fandom.com/wiki/Wights
  [3]: https://gameofthrones.fandom.com/wiki/The_Long_Night_(episode)#Military_tactics