Export (0) Print
Expand All
Expand Minimize

How to: Call a User-Defined Function

.NET Framework 4

Entity SQL enables you to call user-defined functions in a query. These functions can be defined inline with the query or as part of the conceptual model. For more information, see User-Defined Functions.

The example in this topic is based on the Adventure Works Sales Model. To run the code in this topic, you must have already added the Adventure Works Sales Model to your project and configured your project to use the Entity Framework. For more information, see How to: Use the Entity Data Model Wizard (Entity Framework) or How to: Manually Configure an Entity Framework Project and How to: Manually Define an Entity Data Model (Entity Framework).

Example

The following example defines a function named OrderTotal in an Entity SQL query. This function takes a SalesOrderHeader object and recalculates the total amount due by adding the values of the SubTotal, TaxAmt, and Freight properties. This function is then called in the query projection to return the recalculated TotalDue with the TotalDue value returned from the data source. The USING operator is required for specifying the namespace of the SalesOrderHeader object passed to the function.

// Query that calls the OrderTotal function to recalculate the order total.
string queryString = @"USING Microsoft.Samples.Entity;
    FUNCTION OrderTotal(o SalesOrderHeader) AS
    (o.SubTotal + o.TaxAmt + o.Freight)

    SELECT [order].TotalDue AS currentTotal, OrderTotal([order]) AS calculated
    FROM AdventureWorksEntities.SalesOrderHeaders AS [order]
    WHERE [order].Contact.ContactID = @customer";

int customerId = 364;


using (AdventureWorksEntities context =
    new AdventureWorksEntities())
{
    ObjectQuery<DbDataRecord> query = new ObjectQuery<DbDataRecord>(queryString, context);
    query.Parameters.Add(new ObjectParameter("customer",customerId));

    foreach (DbDataRecord rec in query)
    {
        Console.WriteLine("Order Total: Current - {0}, Calculated - {1}.", 
            rec[0], rec[1]);
    }
}

See Also

Community Additions

ADD
Show:
© 2014 Microsoft