{"id":5093,"date":"2007-11-28T13:37:00","date_gmt":"2007-11-28T13:37:00","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/vbteam\/2007\/11\/28\/converting-sql-to-linq-part-2-from-and-select-bill-horst\/"},"modified":"2024-07-05T14:37:34","modified_gmt":"2024-07-05T21:37:34","slug":"converting-sql-to-linq-part-2-from-and-select-bill-horst","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/vbteam\/converting-sql-to-linq-part-2-from-and-select-bill-horst\/","title":{"rendered":"Converting SQL to LINQ, Part 2: FROM and SELECT (Bill Horst)"},"content":{"rendered":"

This post assumes you\u2019ve read Converting SQL to LINQ, Part 1: The Basics<\/a>.<\/p>\n

I\u2019ve made the following name changes since the last post, which I hope will make the code examples clearer:<\/p>\n

\u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Customers -> CustomerTable<\/p>\n

\u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Orders -> OrderTable<\/p>\n

\u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 cust -> Contact<\/p>\n

\u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CustomerName -> ContactName<\/p>\n

\u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ID -> ContactID<\/p>\n

Since I plan to do a handful more of these posts, I welcome any feedback or suggestions so\u00a0I can\u00a0make these as clear and useful as possible.<\/p>\n

Continuing on to specific clauses, we\u2019ll start with the most fundamental, FROM and SELECT.<\/p>\n

FROM<\/b><\/p>\n

A SQL SELECT statement always begins with a SELECT Clause, followed by a FROM Clause.\u00a0 A VB query expression always begins with a From Clause or Aggregate Clause (Aggregate will be discussed later).\u00a0 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).\u00a0 This object could represent \u201cIn-Memory\u201d data, a SQL data table, or XML information.\u00a0 My examples use the \u201cIn-Memory\u201d case, since it allows the simplest code. \u00a0In addition to this data object, the VB From clause always includes an identifier for the current \u201crow\u201d (Contact), which basically functions as an alias.<\/p>\n

If all columns are selected in the SQL statement (*), no Select clause is required for the VB statement.\u00a0 The From Clause returns all the members by default.<\/p>\n\n\n\n\n
\n

SQL<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

SELECT *<\/p>\n

FROM CustomerTable<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n\n\n
\n

VB<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

From Contact In CustomerTable<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

Alias in FROM<\/b><\/p>\n

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).\u00a0 As mentioned above, the identifier specified in the LINQ From Clause serves essentially the same purpose.<\/p>\n\n\n\n\n
\n

SQL<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

SELECT Contact.CustomerID, Contact.Phone<\/p>\n

FROM CustomerTable Contact<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n\n\n
\n

VB<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

From Contact In CustomerTable<\/p>\n

Select Contact.CustomerID, Contact.Phone<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

SELECT<\/b><\/p>\n

SQL SELECT statements start with a list of values to select from the records available (Name, CustomerID).\u00a0 Similarly, LINQ also allows you to select certain members, and will return an object with those members, having an anonymous type.\u00a0 The members specified don\u2019t necessarily need to be part of the object in the From clause, but can be any valid VB expression (e.g. 3 + 4).\u00a0 If the name for the member cannot be inferred, an alias must be specified (see \u201cAlias in SELECT\u201d below).<\/p>\n\n\n\n\n
\n

SQL<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

SELECT Name, CustomerID<\/p>\n

FROM CustomerTable<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n\n\n
\n

VB<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

From Contact In CustomerTable<\/p>\n

Select Contact.Name, Contact.CustomerID<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

Alias in SELECT<\/b><\/p>\n

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.\u00a0 LINQ also allows you to specify the name of a member.\u00a0 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.<\/p>\n\n\n\n\n
\n

SQL<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

SELECT Name ContactName, CustomerID ContactID<\/p>\n

FROM CustomerTable<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n\n\n\n\n
\n

VB<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

From Contact In CustomerTable<\/p>\n

Select ContactName = Contact.Name, ContactID = Contact.CustomerID<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n

Next week, I plan to cover DISTINCT, WHERE, ORDER BY, and Operators<\/p>\n

– Bill Horst, VB IDE Test<\/p>\n","protected":false},"excerpt":{"rendered":"

This post assumes you\u2019ve read Converting SQL to LINQ, Part 1: The Basics. I\u2019ve made the following name changes since the last post, which I hope will make the code examples clearer: \u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Customers -> CustomerTable \u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Orders -> OrderTable \u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 cust -> Contact \u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CustomerName -> ContactName \u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ID -> ContactID Since I plan […]<\/p>\n","protected":false},"author":260,"featured_media":8818,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[20,195],"tags":[41,49,94,166],"class_list":["post-5093","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-converting-sql-to-linq","category-visual-basic","tag-bill-horst","tag-converting-sql-to-linq","tag-linqvb9","tag-vb2008"],"acf":[],"blog_post_summary":"

This post assumes you\u2019ve read Converting SQL to LINQ, Part 1: The Basics. I\u2019ve made the following name changes since the last post, which I hope will make the code examples clearer: \u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Customers -> CustomerTable \u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Orders -> OrderTable \u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 cust -> Contact \u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CustomerName -> ContactName \u00b7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ID -> ContactID Since I plan […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts\/5093","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/users\/260"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/comments?post=5093"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts\/5093\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/media\/8818"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/media?parent=5093"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/categories?post=5093"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/tags?post=5093"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}