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.
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.
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
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
This post assumes you’ve read the previous posts in this series: Converting SQL to LINQ, Part 1: The
引用: 从SQL到LINQ,Part3:DISTINCT,WHERE,ORDERBYandOperators(BillHorst) [原文作者]:BillHorst...
Bill Horst of the VIsual Basic team at Microsoft has been writing a great series on Converting SQL to
ここでは、このシリーズの前の投稿をお読みになっていることを前提としています。 Converting SQL to LINQ, Part 1: The Basics ( 英語 ) Converting SQL
This is horrible.
Most people do not use in memory tables, they use "real" SQL tables from a server. How do we reference a table on a server ??????
how return the identity field or id field on insert a new row?, sample: insert into costumertable (name) values ('bill the kid') select @@identity
Very nicely progressing to understand LINQ in relation to SQL