Performance Question between two Linq Queries in LinqPad and in Practice

I have a query:

(from sr in ctx.STARS_Route
                            where sr.STARS_RouteStopDestination.Any(i => i.IsWorkingSet == true && i.STARS_DistrictRoute.DistrictId == districtId) == true
                            select sr.DistrictRouteNumber).Distinct();

In LinqPad the query was running at roughly 0.3 seconds. There are roughly 800K records in the STARS_RouteStopDestination table, but the average return is about 30-90 records.

In practice, the method returning the results from this query was taking 4+ seconds! It didn't make any sense.

The only thing I could think of is that the .Any clause was taking a lot of time, but LinqPad said the query was fast. I set up a test (please forgive the names):

using (STARSEntities ctx = new STARSEntities())
        {
            var Original = (from sr in ctx.STARS_Route
                            where sr.STARS_RouteStopDestination.Any(i => i.IsWorkingSet == true && i.STARS_DistrictRoute.DistrictId == districtId) == true
                            select sr.DistrictRouteNumber).Distinct();

            var Entity = (from rsd in ctx.STARS_RouteStopDestination
                          where rsd.STARS_DistrictRoute.DistrictId == districtId
                          && rsd.IsWorkingSet == true
                          select rsd.STARS_Route.DistrictRouteNumber).Distinct();

            DateTime startOriginal = DateTime.Now;
            routes = Original.ToList();
            Debug.WriteLine("Orig开发者_如何学Pythoninal took: " + (DateTime.Now - startOriginal).ToString());
            DateTime startEtity = DateTime.Now;
            routes = Entity.ToList();
            Debug.WriteLine("Entity took: " + (DateTime.Now - startEtity).ToString());
        }

The output blew my mind:

Original took: 00:00:04.0270000

Entity took: 00:00:00.0200000

Why does the query with the .Any clause take so much longer to run, and why would LinqPad say that the Original query runs slightly faster than the Entity query against the same dataset?

---

You are simply acting upon two different objects that has two different strategies of obtaining the result. LINQPad generates a LINQ to SQL model and injects your code into a method with this model in scope.

Your STARSEntities class is of a different type and probably has a completely different interaction with the database.

LINQPad has its own view that displays the SQL being run. You should compare it to the SQL being executed by the EF model. This is displayed in the SQL Server Profiler when you execute.