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.
The SQL won't be the same as Linq is [lazy loading](https://stackoverflow.com/questions/36274/what-is-lazy-loading). So your call to `.ToList()` will force .Net to evaluate the expression, then in memory select the `first()` item. 

Where as the other option should add `top 1` into the SQL

E.G.

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

 //SQL executed here
 var User = query.First();

```

and

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

 //SQL executed here!
 var list = query.ToList();
 var User = query.First();

```

As below, the first query **should be faster**! I would suggest doing a [SQL profiler](http://technet.microsoft.com/en-us/library/ms181091.aspx) to see what's going on. The speed of the queries will depend on your data structure, number of records, indexes, etc.

The timing of your test will alter the results also. As a couple of people have mentioned in comments, the first time you hit EF it needs to initialise and load the metadata. so if you run these together, the first one should always be slow.

Here's some more info on EF [performance considerations](http://msdn.microsoft.com/en-us/library/cc853327%28v=vs.110%29.aspx)

notice the line:

> 
> Model and mapping metadata used by the Entity Framework is loaded into
>  a MetadataWorkspace. This metadata is cached globally and is available
>  to other instances of ObjectContext in the same application domain.
> 
> 
> 

&

> 
> Because an open connection to the database consumes a valuable
>  resource, the Entity Framework opens and closes the database
>  connection only as needed. You can also explicitly open the
>  connection. For more information, see Managing Connections and
>  Transactions in the Entity Framework.
> 
> 
>