Converting SQL to LINQ, Part 2: FROM and SELECT (Bill Horst)
This post assumes you’ve read Converting SQL to LINQ, Part 1: The Basics.
I’ve made the following name changes since the last post, which I hope will make the code examples clearer:
· Customers -> CustomerTable
· Orders -> OrderTable
· cust -> Contact
· CustomerName -> ContactName
· ID -> ContactID
Since I plan to do a handful more of these posts, I welcome any feedback or suggestions so I can make these as clear and useful as possible.
Continuing on to specific clauses, we’ll start with the most fundamental, FROM and SELECT.
FROM
A SQL SELECT statement always begins with a SELECT Clause, followed by a FROM Clause. A VB query expression always begins with a From Clause or Aggregate Clause (Aggregate will be discussed later). A basic SQL FROM clause specifies a table over which to query, and similarly, a LINQ From Clause specifies an object over which to query (CustomerTable). This object could represent “In-Memory” data, a SQL data table, or XML information. My examples use the “In-Memory” case, since it allows the simplest code. In addition to this data object, the VB From clause always includes an identifier for the current “row” (Contact), which basically functions as an alias.
If all columns are selected in the SQL statement (*), no Select clause is required for the VB statement. The From Clause returns all the members by default.
|
SQL |
|
SELECT *
FROM CustomerTable
|
|
VB |
|
From Contact In CustomerTable
|
Alias in FROM
SQL allows you to specify an alias for a table in the FROM Clause, so that all references to columns in that table will be qualified with that alias (Contact). As mentioned above, the identifier specified in the LINQ From Clause serves essentially the same purpose.
|
SQL |
|
SELECT Contact.CustomerID, Contact.Phone
FROM CustomerTable Contact
|
|
VB |
|
From Contact In CustomerTable
Select Contact.CustomerID, Contact.Phone
|
SELECT
SQL SELECT statements start with a list of values to select from the records available (Name, CustomerID). Similarly, LINQ also allows you to select certain members, and will return an object with those members, having an anonymous type. The members specified don’t necessarily need to be part of the object in the From clause, but can be any valid VB expression (e.g. 3 + 4). If the name for the member cannot be inferred, an alias must be specified (see “Alias in SELECT” below).
|
SQL |
|
SELECT Name, CustomerID
FROM CustomerTable
|
|
VB |
|
From Contact In CustomerTable
Select Contact.Name, Contact.CustomerID
|
Alias in SELECT
SQL also allows members in the SELECT clause to have an alias (ContactName, ContactID), by which they are referred to in the rest of the query. LINQ also allows you to specify the name of a member. This is how the member will be referenced after that Select clause in the query, and anywhere the result of the query is specified elsewhere in code.
|
SQL |
|
SELECT Name ContactName, CustomerID ContactID
FROM CustomerTable
|
|
VB |
|
From Contact In CustomerTable
Select ContactName = Contact.Name, ContactID = Contact.CustomerID
|
Next week, I plan to cover DISTINCT, WHERE, ORDER BY, and Operators
- Bill Horst, VB IDE Test