Converting SQL to LINQ, Part 2: FROM and SELECT (Bill Horst)

Converting SQL to LINQ, Part 2: FROM and SELECT (Bill Horst)

Rate This
  • Comments 25

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
Leave a Comment
  • Please add 2 and 6 and type the answer here:
  • Post
  • 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...

  • 引用:从SQL到LINQ,Part2:FROM和SELECT(BillHorst) [原文作者]:BillHorst

    [原文链接]:ConvertingSQLtoL...

  • This post assumes you’ve read the previous posts in this series: Converting SQL to LINQ, Part 1: The

  • This post assumes you’ve read the previous posts in this series: Converting SQL to LINQ, Part 1: The

  • 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

    Lovely

Page 2 of 2 (25 items) 12