CODEWITHSUNDEEP
c#.netlinqlinq-to-sql
Tomas
Tomas

Reputation: 18117

Linq to SQL execute query immediately

I have Linq query and would like to execute it immediately and then manipulate data. Now in the code below the first (1) Linq query is executed when second (2) Linq query is executed. I would like to execute first(1) query first, how to do that? 

// 1
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 });


values = new int[interval];
labels = new string[interval];

for (var i = 0; i < labels.Length; i++)
{
    // 2
    var recordsCount = statistic.Count(p => p.Year == dayStep.Year && p.Month == dayStep.Month && p.Day == dayStep.Day);
}

Upvotes: 3

Views: 4661

Answers (3)

Pankaj Upadhyay
Pankaj Upadhyay

Reputation: 13594

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;

Upvotes: 9

Sam
Sam

Reputation: 10113

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

Upvotes: 2

Oleg Dok
Oleg Dok

Reputation: 21766

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();

Upvotes: 3

Related Questions