LINQ to SQL deferred execution and materialization

Question

I am sort 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 the sql statements make a trip to the db actually? Once at Count(). Again at Where()? Or Linq retains what it materialized during Count()?

Or it also depends on what the Where(..) has? Like if it is again referencing to database vs it just references what's obtained as part of 'q'/ or any other .net collections etc?

Edit:

Updated my code with a couple of other scenarios. 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 it prepares and executes a query like

SELECT COUNT(*) FROM ...

Using ExecuteScalar to get the result.

When you call Where and Select it does not materialize anything (assuming q is an IQueryable). Instead it's just preparing a query like

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

But it doesn't actually execute it at that point. It will only execute the query when you call GetEnumerator on q. You'll rarely do this directly, but anything like the following will cause your query to be executed:

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

It will only execute this query once, so having multiple foreach loops will not create multiple database queries. Of course, if you create a new IQueryable based on q (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 tested out your code in LINQPad, and it seems all your analyses are correct.