{"id":4973,"date":"2007-12-12T12:45:00","date_gmt":"2007-12-12T12:45:00","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/vbteam\/2007\/12\/12\/converting-sql-to-linq-part-4-functions-bill-horst\/"},"modified":"2024-07-05T14:36:57","modified_gmt":"2024-07-05T21:36:57","slug":"converting-sql-to-linq-part-4-functions-bill-horst","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/vbteam\/converting-sql-to-linq-part-4-functions-bill-horst\/","title":{"rendered":"Converting SQL to LINQ, Part 4: Functions (Bill Horst)"},"content":{"rendered":"

This post assumes you\u2019ve read the previous posts in this series:<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 1: The Basics<\/a><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 2: FROM and SELECT<\/a><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 3: DISTINCT, WHERE, ORDER BY and Operators<\/a><\/p>\n

This post will discuss scalar and aggregate functions.<\/p>\n

Functions<\/strong><\/p>\n

SQL SELECT clauses often involve functions, which can be scalar or aggregate.\u00a0 An aggregate function is applied to a field over all the selected records, while a scalar function is called with individual values, one record at a time.\u00a0 It is possible to re-create both kinds of functions with\u00a0VB LINQ expressions, but in very different ways.<\/p>\n

Scalar Functions<\/strong><\/p>\n

Scalar functions are called on each record with whatever parameters are specified.\u00a0 They can appear various places in a SQL query, such as in the SELECT clause.\u00a0 The Scalar Functions available differ from system to system, but usually, there will be an analogous VB method that can be used.\u00a0 If using a Scalar Function in a LINQ Select clause, you will probably need to specify an alias as well (FirstThreeLetters, CurrentTime).<\/p>\n\n\n\n\n
\n

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

\n

SELECT LEFT(ItemName, 3) FirstThreeLetters, NOW() CurrentTime<\/p>\n

FROM OrderTable<\/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 Shipment In OrderTable _<\/p>\n

Select FirstThreeLetters = Left(Shipment.ItemName, 3), CurrentTime = Now<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

In the above case, Left and Now are methods already built into VB, defined in Microsoft.VisualBasic.dll. This will likely be the case for most common scalar functions you will find in SQL statements.\u00a0 Even if the function you wish to call does not exist in VB already, you can define your own methods, too.\u00a0 However, user-defined methods cannot be used in a Database query, as they will throw an exception at runtime.\u00a0 In the below example, MyFunction is a user-defined function called from the Select clause.<\/p>\n\n\n\n\n
\n

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

\n

From Shipment In OrderTable _<\/p>\n

Select MyFunction(Shipment.Cost, Shipment.ShippingZip)<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

Aggregate\u00a0Functions<\/strong><\/p>\n

Aggregate functions are called on certain fields over an entire set of records, and return one value.\u00a0 In a SQL statement, they can appear in the SELECT clause.\u00a0 With VB LINQ, this concept appears a bit differently.<\/p>\n

A VB LINQ expression usually begins with a From clause.\u00a0 However, they can also begin with an Aggregate clause.\u00a0 The Aggregate clause has the same syntax as a From clause, except that it starts with a different keyword.\u00a0 If a query starts with an Aggregate clause, it must end with an Into clause.\u00a0 An Into clause is a comma-delimited list of Aggregate function calls, with aliases that can accompany them.\u00a0 The below example shows a SQL SELECT statement that uses Aggregate functions, and an equivalent VB LINQ expression.<\/p>\n\n\n\n\n
\n

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

\n

SELECT SUM(Cost) TotalCost, COUNT(*)<\/p>\n

FROM OrderTable<\/p>\n

WHERE OrderDate > \u201cSept-29-2007\u201d<\/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

Aggregate Shipment In OrderTable _<\/p>\n

Where Shipment.OrderDate > #9\/29\/2007# _<\/p>\n

Into TotalCost = Sum(Shipment.Cost), Count()<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

Next week, I will discuss GROUP BY and HAVING.<\/p>\n","protected":false},"excerpt":{"rendered":"

This post assumes you\u2019ve read the previous posts in this series: \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 1: The Basics \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 2: FROM and SELECT \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 3: DISTINCT, WHERE, ORDER BY and Operators This post will discuss scalar and aggregate functions. Functions SQL SELECT clauses […]<\/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-4973","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 the previous posts in this series: \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 1: The Basics \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 2: FROM and SELECT \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Converting SQL to LINQ, Part 3: DISTINCT, WHERE, ORDER BY and Operators This post will discuss scalar and aggregate functions. Functions SQL SELECT clauses […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts\/4973","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=4973"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts\/4973\/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=4973"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/categories?post=4973"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/tags?post=4973"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}