A group blog from members of the VB team
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.
SELECT Contact.CustomerID, Contact.Phone
FROM CustomerTable Contact
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).
SELECT Name, CustomerID
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.
SELECT Name ContactName, CustomerID ContactID
Select ContactName = Contact.Name, ContactID = Contact.CustomerID
Next week, I plan to cover DISTINCT, WHERE, ORDER BY, and Operators
PingBack from http://www.absolutely-people-search.info/?p=718
This post assumes you’ve read the previous posts in this series: Converting SQL to LINQ, Part 1: The
Si quieres aprender LINQ para Visual Basic 2008, quizás te interesen los siguientes artículos que el
Hi Bill,
One addition: when using an alias, the member name must be preceded by a period. So it must be:
Select .ContactName = Contact.Name, ContactID = Contact.CustomerID
How do you Select all columns from two tables?
Dim igetanerror = from i in db1, j in db2 Select i, j
Hi Lennart,
Which version of VS are you using? I don't think this period should be necessary.
To the anonymous poster above, what error are you seeing?
Dim iiresult = From ii In db.InventoryItems, il In db.InventoryLogs Select ii, il
GridView2.DataSource = iiresult
GridView2.DataBind()
Server Error in '/' Application.
--------------------------------------------------------------------------------
The data source for GridView with id 'GridView2' did not have any properties or attributes from which to generate columns. Ensure that your data source has content.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Web.HttpException: The data source for GridView with id 'GridView2' did not have any properties or attributes from which to generate columns. Ensure that your data source has content.
Source Error:
Line 80:
Line 81: GridView2.DataSource = iiresult
Line 82: GridView2.DataBind()
Line 83:
Line 84:
How can we do something like this? Do we have to specify all the fields for both tables?