Question: I'm using Linq2Sql to return the results of a stored procedure. The sproc provides 100,000 records in under 2 seconds. Applying ToList() takes over 2 minutes.

The project is an ASP.NET WebForm. In the code-behind I'm attempting to get the records from a transactional system to apply various analyses for dashboard-type reporting. 100K records is an average month's worth of data. Everything works fine with smaller data. 

```
using (FooDataContext dbml = new FooDataContext())
{
    var query = dbml.FooBar(OneParam, TwoParam, ThreeParam);
    //no delay

    var results = query.ToList();
    //takes over 2 minutes -- consistent network traffic throughout

    ReportGenerator.PivotTable(results);
    ReportGenerator.Chart(results);
    //etc.
}

```

I used ToList() to take advantage of Linq's hydrated sproc objects, which are handy for evaluating the results with lambda expressions. 

But ToList() takes a very, very long time to construct each of the results for this much data. If I pause the process during that time, I can see that it's just looping over and over through the sproc's constructor. Looking at my network traffic seems to confirm that the code is going back out to the database for each object. Setting DeferredLoadingEnabled to false didn't help.

The funny thing is, I thought that a disadvantage of stored procedures was that they dumped all of the data back at you at once, rather than as an IQueryable?

Answer: 
I think that you are trying to solve the problem in a manner which isn't optimal. If you want to provide dashboard reports, you should have background processes (perhaps sql agent jobs or windows services) building materialized dashboard tables with the numbers crunched into smaller "report dtos" that you can then query and put into your dashboard. I don't care what the SQL is, pulling 100k records per request AND THEN performing some calculations/processing to display that data in a meaningful way is going to be wasteful and slower performing.
Solution for me was to use old fashioned ADO.NET to query the stored proc. Obviously not as easy as L2S but exponentially faster in this case as with L2S it seems to run the procedure for each row.

```
public List<object> Foo(SqlConnection connection)
{
    var query = "[dbo].[FooBar]";
    var command = new SqlCommand(query, connection);
    command.CommandType = CommandType.StoredProcedure;
    connection.Open();

    var reader = command.ExecuteReader();
    var results = new List<object>(); // Or whatever type your data is.

    while (reader.Read())
    {
        // Make this work for your particular data structure:
        results.Add(reader.GetString(0));
    }

    connection.Close();

    return results;
}

```

Also be sure to create your connection with a using statement just to be safe:

```
using (var connection = new SqlConnection(connectionString))
{
    results = Foo(connection);
}

ReportGenerator.PivotTable(results);
ReportGenerator.Chart(results);
//etc.

```