Question: **Why does forcing materialization using `ToList()` make my query orders of magnitude faster when, if anything, it should do the exact opposite?**

1) Calling `First()` immediately

```
    // "Context" is an Entity Framework DB-first model

    var query = from x in Context.Users
                where x.Username.ToLower().Equals(User.Identity.Name.ToLower())
                select x;

    var User = query.First();

    //  ** The above takes 30+ seconds to run **

```

2) Calling `First()` *after* calling `ToList()`:

```
    var query = from x in Context.Users
                where x.Username.ToLower().Equals(User.Identity.Name.ToLower())
                select x;

    var User = query.ToList().First();     // Added ToList() before First()

    // ** Now it takes < 1 second to run! **

```

Update and Resolution
=====================

After getting the generated SQL, the only difference is, as expected, the addition of `TOP (1)` in the first query. As [Andyz Smith](https://stackoverflow.com/a/18361548/110549) says in his answer below, the root cause is that the SQL Server optimizer, in this particular case, chooses a worse execution plan when `TOP (1)` is added. Thus the problem has nothing to do with LINQ (which did the right thing by adding `TOP (1)`) and everything to do with the idiosyncrasies of SQL Server.

Answer: 
I can only think of one reason...
To test it, can you please remove the `Where` clause and re-run the test? Comment here if the result is the first statement being faster, and i will explain why.

**Edit**   

In the LINQ statement Where clause, you are using the .ToLower() method of the string. My guess is that LINQ does not have built in conversion to SQL for this method, so the resultant SQL is something line

```
SELECT *
FROM Users

```

Now, we know that LINQ lazy loads, but it also knows that since it has not evaluated the `WHERE` clause, it needs to load the elements to do the comparison. 

**Hypothesis**  

The first query is lazy loading **EVERY** element in the result set. It is then doing the .ToLower() comparison and returning the first result. This results in `n` requests to the server and a huge performance overhead. Cannot be sure without seeing the SQL Tracelog.

The Second statement calls ToList, which requests a batch SQL before doing the ToLower comparison, resulting in only one request to the server

**Alternative Hypothesis**  

If the profiler shows only one server execution, try executing the same query with the Top 1 clause and see if it takes as long. As per this post ([Why is doing a top(1) on an indexed column in SQL Server slow?](https://stackoverflow.com/questions/2453765/why-is-doing-a-top1-on-an-indexed-column-in-sql-server-slow)) the TOP clause can sometimes mess with the SQL server optimiser and stop it using the correct indices. 

**Curiosity edit**  

try changing the LINQ to 

```
var query = from x in Context.Users
            where x.Username.Equals(User.Identity.Name, StringComparison.OrdinalIgnoreCase)
            select x;

```

Credit to @Scott for finding the way to do case insensitive comparison in LINQ. Give it a go and see if it is faster.
So, the optimizer chooses a bad way to run the query.

Since you can't add optimizer hints to the SQL to force the optimizer to choose a better plan I see two options.

1. Add a covering index/indexed view **on all the columns that are retrieved/included in the select** Pretty ludicrous, but I think it will work, because that index will make it easy peasy for the optimizer to choose a better plan.
2. Always prematurely materialize queries that include First or Last or Take.  Dangerous because as the data gets larger the break even point between pulling all the data locally and doing the First()  and doing the query with Top on the server is going to change.

<http://geekswithblogs.net/Martinez/archive/2013/01/30/why-sql-top-may-slow-down-your-query-and-how.aspx>

<https://groups.google.com/forum/m/#!topic/microsoft.public.sqlserver.server/L2USxkyV1uw>

<http://connect.microsoft.com/SQLServer/feedback/details/781990/top-1-is-not-considered-as-a-factor-for-query-optimization>

[TOP slows down query](https://stackoverflow.com/questions/6681841/top-slows-down-query)

[Why does TOP or SET ROWCOUNT make my query so slow?](https://stackoverflow.com/questions/1759797/why-does-top-or-set-rowcount-make-my-query-so-slow)