How to: Call Database Functions

  • Article

The SqlFunctions class contains methods that expose SQL Server functions to use in LINQ to Entities queries. When you use SqlFunctions methods in LINQ to Entities queries, the corresponding database functions are executed in the database.

Note

Database functions that perform a calculation on a set of values and return a single value (also known as aggregate database functions) can be directly invoked. Other canonical functions can only be called as part of a LINQ to Entities query. To call an aggregate function directly, you must pass an ObjectQuery to the function. For more information, see the second example below.

Note

The methods in the SqlFunctions class are specific to SQL Server functions. Similar classes that expose database functions may be available through other providers.

Example

The following example uses the AdventureWorks Sales Model. The example executes a LINQ to Entities query that uses the CharIndex method to return all contacts whose last name starts with "Si":

Using AWEntities As New AdventureWorksEntities()

    ' SqlFunctions.CharIndex is executed in the database.
    Dim contacts = From c In AWEntities.Contacts _
        Where SqlFunctions.CharIndex("Si", c.LastName) = 1 _
        Select c

    For Each contact In contacts
        Console.WriteLine(contact.LastName)
    Next
End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    // SqlFunctions.CharIndex is executed in the database.
    var contacts = from c in AWEntities.Contacts
                   where SqlFunctions.CharIndex("Si", c.LastName) == 1
                   select c;

    foreach (var contact in contacts)
    {
        Console.WriteLine(contact.LastName);
    }
}

The following example uses the AdventureWorks Sales Model. The example calls the aggregate ChecksumAggregate method directly. Note that an ObjectQuery is passed to the function, which allows it to be called without being part of a LINQ to Entities query.

Using AWEntities As New AdventureWorksEntities()

    ' SqlFunctions.ChecksumAggregate is executed in the database.
    Dim checkSum As Integer = SqlFunctions.ChecksumAggregate( _
        From o In AWEntities.SalesOrderHeaders _
        Select o.SalesOrderID)

    Console.WriteLine(checkSum)
End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    // SqlFunctions.ChecksumAggregate is executed in the database.
    decimal? checkSum = SqlFunctions.ChecksumAggregate(
        from o in AWEntities.SalesOrderHeaders
        select o.SalesOrderID);

    Console.WriteLine(checkSum);
}

See Also

Concepts

Calling Functions in LINQ to Entities Queries
Queries in LINQ to Entities