score:9

Accepted answer

I think you are missing an important concept in Data Manipulation in .NET

Deferred and Immediate Fetching

An important point to emphasize is that by default, LINQ to SQL retrieves the data from the database only when you request it and not when you define a LINQ to SQL query or create a Table collection. This is known as deferred fetching.

When the foreach loop starts, LINQ to SQL creates and runs a SQL SELECT statement derived from the LINQ to SQL query to create an ADO.NET DataReader object. Each iteration of the foreach loop performs the necessary GetXXX methods to fetch the data for that row. After the final row has been fetched and processed by the foreach loop, LINQ to SQL closes the database connection.

Deferred fetching ensures that only the data an application actually uses is retrieved from the database. However, if you are accessing a database running on a remote instance of SQL Server, fetching data row by row does not make the best use of network bandwidth. In this scenario, you can fetch and cache all the data in a single network request by forcing immediate evaluation of the LINQ to SQL query. You can do this by calling the ToList or ToArray extension methods, which fetch the data into a list or array when you define the LINQ to SQL query, like this:

var productsQuery = from p in products.ToList()
select p;

score:3

Try this 1st statement (pay attention to .ToList() statement)

var statistic = DataAccess.Instance.Statistics.Where(p => p.DateStamp >= fromDate && p.DateStamp <= DateTime.UtcNow && p.UserId == userId).
                Select(p => new {p.DateStamp.Year, p.DateStamp.Month, p.DateStamp.Day }).ToList();

score:2

You can do something like .ToList() and it should force it to execute.


Related Articles