Relative alignment between dense molecular cores and ambient magnetic field: The synergy of numerical models and observations