Delayed Execution and Materialization from LINQ to SQL

Question

I'm kind of new to this. Curious what happens in the following situation?

var q = //MY LINQ TO SQL QUERY.Select(...) ......... ......... var c = q.Count(); ......... ......... var x = q.Where(....).Select(....); var y = x.ToList();//or something such that forces materialization var d = q.Count();//same as c var e = x.Count(); var f = y.Count(); 

How many times did sql commands trip in db actually? Once upon a time in Count (). Again in Where ()? Or does Linq preserve that it materialized during Count ()?

Or does it also depend on what Where (..) has? As if he were referring to the vs database again, he was just referring to what was received as part of the “q” / or any other .net collections, etc.

Edit:

Updated my code with a few other scripts. Please correct my answers below:

 q -no db trip c -yes, but translates to aggregate qry - select Count(*) and not a result set (as per answer below) x -no db trip. No matter what is written in the Where(..) y - yes d - yes - does not *reuse* c e - yes - select count(*) EVEN THOUGH x already materized during y f - no db trip 

Answer 1

When you call Count , it does not materialize the entire data set. Rather, he prepares and executes a request such as

 SELECT COUNT(*) FROM ... 

Using ExecuteScalar to get the result.

When you call Where and Select , it does not materialize (it is assumed that q is IQueryable ). Instead, it simply prepares a request like

 SELECT col1, col2, ... FROM ... 

But actually it is not fulfilled. It will execute the request only when calling GetEnumerator on q . You will rarely do this directly, but something like the following will execute your request:

 var arry = q.ToArray(); var list = q.ToList(); foreach(var rec in q) ... 

It will execute this query only once, so having multiple foreach loops will not create multiple database queries. Of course, if you create a new IQueryable -based IQueryable (e.g. var q2 = q.Where(...) ), it will not be tied to the result set used by q , so it will have to query the database again.

I have checked your code in LINQPad and it seems that all your analyzes are correct.