Performance of IQueryable

Question

i needed to write a dynamic query on the customer database for obtaining few fields of a customer.

following is the code

[Route("api/getBasicCustList/{argType}/{argValue}")]
        [HttpGet]
        [Authorize]

        public dynamic getCustomerDataUsername(String argType, String argValue)
        {
            IQueryable<CustomerDTO> query =
               (from recordset in db.Customers
                     select new CustomerDTO
                               {
                                   companyId = recordset.Company.Id,
                                   contactNum = recordset.ContactNum,
                                   username = recordset.UserName,
                                   emailAddress = recordset.Email,
                                   fullName = recordset.FullName,
                                   accountNumber = recordset.RCustId
                               }
                );

            switch (argType)
            {
                case "username" :
                    query = query.Where(c => c.username.StartsWith(argValue));
                    break;
                case "contactnum":
                    long mobNum = Int64.Parse(argValue);
                    query = query.Where(c => c.contactNum == mobNum);
                    break;
                case "fullname":
                    query = query.Where(c => c.fullName.Contains(argValue));
                    break;
            }

            return new { data = query.ToList() };
        }

this works fine and is solving my purpose.

my question here is when i write my first part of the query to get all the customer records and later on apply the where condition dynamically will the results be brought in memory or the complete query is generated and executed at db in one shot?

Since i have just 500 records as of now, i am not able to find any performance lag but when i take this to production i will be dealing with at least 200,000 to 300,000 records.

Answer 1

ok, the answer is

The query won't be executed until you reach that "ToList" at the end of your method

from the MSDN link shared by @GeorgPatscheider its mentioned

At what point query expressions are executed can vary. LINQ queries are always executed when the query variable is iterated over, not when the query variable is created. This is called deferred execution

Deferred execution enables multiple queries to be combined or a query to be extended. When a query is extended, it is modified to include the new operations, and the eventual execution will reflect the changes.

Its also written that if queries have any of Average, Count, First, or Max it will perform an immediate execution.

thanks.