Part -2 Query Expressions
Query Expression:
As I mentioned on the previous post, query is a set of information used to retrieve data from data sources. So query operates on a data source (SQL, XML, collections… ). From application stand point, it sees the execution result as IEnumerable (Of T) or IQueryable(Of T) collection
A query expression is a query made of certain clauses and with certain syntax.
Starting a query expression
A query expression starts with from clause. This specifies the data source and the range variable. Consider the example
var queryVar = from employee in employeeList where employee.Salary>10000
In this example employeeList is the data source and employee is the range variable. Since the compiler can infer the type, there is no need to specify range variable’s type.
The range variable is like an iteration variable. Actual iteration won’t happen in query expression. When query gets executed this variable will serve as a reference to each successive element in employeeList.
A range variable can be introduced by a let statement. Scope of a range statement is until the query is exited either with a semicolon or with a continuation clause.
A query expression may contain multiple from clause. As an example
Var queryVar = from employee in employeeList from department in departmentList where employee.Id = department.Id select new {EmployeeName=employee.Name, departmentName=department.Name}
Ending query expression
A query expression must end with a select or group clause.
Grouping
group clause helps to group result based on a key. When we group, the result will be a list of lists of IGrouping(Of TKey, TElement). Each element in the list is an object that has a Key member and a list of elements that are grouped under that key. To access the elements use nested foreach loop. Group keys can be any type, such as a string, a built-in numeric type, or a user-defined named type or anonymous type.
var querGroupByEmpdeptId = from employee in employeeList group employee by employee.DepartmentId
Selecting
select clause produces and shape the result of the query. When select clause produces something other than a copy of the source element, the operation is called a projection
Into and let clause
into clause is used conjunction with select, join or group clause to create a temporary identifier that stores the query.
let clause is used to store the value of an expression and it can be used in the subsequent clause.
var studentQuery = from student in students let avg = (int)student.Scores.Average() group student by (avg == 0 ? 0 : avg / 10) into g orderby g.Key select g; (from MSDN)
Filtering
where clause is used to filter out data from data source.
Ex: var employeeQuery = from employee in employees where employee.Salary >1000
The where clause is a filtering mechanism. It can be positioned almost anywhere in a query expression, except it cannot be the first or last clause. A where clause may appear either before or after a group clause depending on whether you have to filter the source elements before or after they are grouped.
Sorting
ordererby clause causes the returned sequence or subsequence (group) to be sorted in either ascending or descending order. By default it is ascending, so this key word is optional
Ex: var employeeSortByNameQuery = from employee in employees orderby employee.Name
Joining
join clause is used to associating elements from different source sequences that have no direct relationship in the object model. The only requirement is that the elements in each source share some value that can be compared for equality.
There are three kinds of join
- Inner Join
- Group join
- Left outer join
