I will try to help with the title question. I think that the real motivation for the Levi-Civita connection comes from looking at surfaces in Euclidean 3-space. Differentate one tangent vector field Y along another X by first extending them to be defined in the ambient space, and then taking the tangential projection of XY, i.e. tangential projection of the Euclidean connection. Levi-Civita discovered that this process is intrinsic, i.e. invariant under isometry of surfaces without carrying along the ambient space, and described precisely by torsion freedom. This was clearly a long and difficult process. Dirac uses this view in his book General Theory of Relativity, and this is how I introduce the Levi-Civita connection in my lectures. I have to agree that there is something missing in the textbook discussions of torsion. I have not found an intuitive understanding of torsion.