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 4 and 7 and type the answer here:
  • Post
  • 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

  • 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

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

  • 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?

  • 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

  •        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:

  • Si quieres aprender LINQ para Visual Basic 2008, quizás te interesen los siguientes artículos que el

  • How can we do something like this? Do we have to specify all the fields for both tables?

          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:

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

Page 1 of 2 (25 items) 12