Converting SQL to LINQ, Part 4: Functions (Bill Horst)

Converting SQL to LINQ, Part 4: Functions (Bill Horst)

  • Comments 10

This post assumes you’ve read the previous posts in this series:

          Converting SQL to LINQ, Part 1: The Basics

          Converting SQL to LINQ, Part 2: FROM and SELECT

          Converting SQL to LINQ, Part 3: DISTINCT, WHERE, ORDER BY and Operators

 

This post will discuss scalar and aggregate functions.

Functions

SQL SELECT clauses often involve functions, which can be scalar or aggregate.  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.  It is possible to re-create both kinds of functions with VB LINQ expressions, but in very different ways.

Scalar Functions

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

SQL

SELECT LEFT(ItemName, 3) FirstThreeLetters, NOW() CurrentTime

FROM OrderTable

 

VB

From Shipment In OrderTable _

Select FirstThreeLetters = Left(Shipment.ItemName, 3), CurrentTime = Now

 

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.  Even if the function you wish to call does not exist in VB already, you can define your own methods, too.  However, user-defined methods cannot be used in a Database query, as they will throw an exception at runtime.  In the below example, MyFunction is a user-defined function called from the Select clause.

VB

From Shipment In OrderTable _

Select MyFunction(Shipment.Cost, Shipment.ShippingZip)

 

Aggregate Functions

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

 

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

 

SQL

SELECT SUM(Cost) TotalCost, COUNT(*)

FROM OrderTable

WHERE OrderDate > “Sept-29-2007”

 

 

VB

Aggregate Shipment In OrderTable _

Where Shipment.OrderDate > #9/29/2007# _

Into TotalCost = Sum(Shipment.Cost), Count()

 

 

Next week, I will discuss GROUP BY and HAVING.
Leave a Comment
  • Please add 4 and 1 and type the answer here:
  • Post
  • PingBack from http://ajaxus.net/2007/12/13/converting-sql-to-linq-part-4-functions/

  • 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

  • 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

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

  • 引用:从SQL到LINQ,Part4:函数(BillHorst) [原文作者]:BillHorst

    [原文链接]:ConvertingSQLtoLINQ,Part...

  • 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

  • is this possible???

    var _listUsuariosDtaGrid = from clsUsuario in db.Usuarios orderby clsUsuario.Usuario                        select new {

    Usuario = Encoding.UTF8.GetString(clsUsuario.Usuario),

    clsUsuario.NomVend, clsUsuario.AccesoPV

    };

Page 1 of 1 (10 items)