Any SQL query can be slow, especially if you have a large database and/or use a lot of Foreign Keys within your database.
Sometimes small changes in fragmentation, statistics or data composition will trigger a different queryplan which will affect your query immensely.
Good monitoring and a DBA nearby would be your best bets to overcome this. The point is that using a relational database is not a trivial thing once the database becomes large. Until then its relatively simple.
That was long time ago and EF improved a lot since then.
But it can be slow if used improperly. But hand written SQL can be slow too if not carefully crafted.