Why is the frame attached on the Earth non-inertial in General Relativity? ( neglecting rotations etc..)

In Newtonian mechanics, a frame of reference is always global, global Cartesian coordinate systems always exist, and there is a one-to-one association between frames and coordinate systems. None of these things hold true in general relativity.

If there exists some neighborhood of a point in spacetime on which coordinates exist such that the metric is exactly $eta$, then spacetime is flat in that whole neighborhood. The equivalence principle doesn't say that spacetime is flat.

Secondly consider a frame attached on the Earth ( neglect rotation). See a particle at rest. It remains at rest ( or uniform motion). So why is the frame attached on the earth non inertial.

I have a bottle of beer sitting on my desk right now. The desk is applying an upward normal force to it, and this is the only force that acts on it. (Gravity is not a force.) In a frame of reference fixed to the desk, the bottle's acceleration is zero. This is clearly not an inertial frame -- in an inertial frame, an object with a nonzero net force acting on it should accelerate. The experimental results are the same results you'd see aboard a spaceship accelerating at 1 g, which is clearly not an inertial frame.

I am confused with the link of Geodesics with the above facts. Books say, the trajectory of inertial frame is a geodesic. But with respect to what frame is that trajectory a geodesic

A curve is either a geodesic or not. That's a primary notion. Frames are a secondary notion. If a test mass has zero force acting on it, then its world-line is a geodesic. We could then ask whether the test mass appears to accelerate in a certain frame. If so, then that frame is not inertial.

For the first part, a free falling non rotating frame is always locally a Minkowski spacetime.

At any small interval, all free objects inside or nearby the ISS moves in straight lines at constant speed.

But for longer intervals they start to deviate more and more from that approximation. That discrepancy is a indication that the spacetime is not flat, except for that locally approximation.

For the second part, any free falling frame follows a geodesic in spacetime. If its Minkowski spacetime is only locally valid, instead of the velocities of the celestial bodies around be constant, their covariant velocities (calculated for the valid metric) are constant.

About object at rest on the surface of the earth, they are of couse not following a geodesics, but the meaning of its acceleration is that the covariant derivative of its velocity for the Schwarzschild metric is not zero. Instead, it gives $g$ as result.