Manipulating Data Using LINQ

Using entity and database functions

Functions are an important part of modern database applications. You use them to perform a variety of tasks, such as finding the average value of a customer’s purchases. Creating and using functions need not be a grueling task. The following sections describe how to create and use functions with the Entity Framework. You can find this example in the \Microsoft Press\Entity Framework Development Step by Step\Chapter 06\ModelFirst (Function) folder of the downloadable source code.

Creating the function

Before you can use a function, you must create it. The following procedure demonstrates one technique for creating functions in SQL Server without leaving the Visual Studio IDE. The procedure relies on the ModelFirst example you created in the “Creating a model-first example” section of Chapter 3.

1. Copy the ModelFirst example you created in Chapter 3 to a new folder and use this new copy for this example (rather than the copy you created in Chapter 3).

2. Choose View | Server Explorer. You’ll see the Server Explorer window shown here:

   

3. Open the Rewards2 connection.

4. Right-click the Functions folder and choose Add New | Table-Based Function. Visual Studio opens a new SQL file for you that contains a basic template for creating table-based functions.

5. Type the following code into the file:

   USE [Rewards2]
   GO
   CREATE FUNCTION [dbo].[AveragePurchase]
   (
      @CustomerId int
   )
   RETURNS DECIMAL(3,2)
   AS
   BEGIN
      DECLARE @Average DECIMAL(3,2)
      SELECT @Average = avg(Amount)
         FROM Purchases
         WHERE CustomersId = @CustomerId;
      RETURN @Average
   END

   This function begins by selecting the appropriate database for modification. It then creates a function named AveragePurchase, which accepts a single input, CustomerId. The function creates a variable, @Average, of type DECIMAL, and uses it as part of an SQL statement that selects the average of the purchases contained in Amount from the Purchases table, where the CustomerId value matches the @CustomerId input. The result is the average purchase amount for a single customer.

6. Right-click anywhere in the code window and choose Execute from the context menu. You’ll see the Connect To Server dialog box.

7. Provide the required credentials and click Connect. Visual Studio will execute the command for you. You should see “Command(s) completed successfully.” on the Message tab that appears when you execute the command.

8. Right-click the Rewards2 entry in Server Explorer and choose Refresh from the context menu. You’ll see the new function appear in the Functions folder, as shown here:

   

9. Right-click AveragePurchase and choose Execute from the context menu. You’ll see an Execute Function dialog box like the one shown here, telling you the function requires an input value to execute:

   

   Click to view larger image

10. Type 1 in the Value field for @CustomerId and click OK. Visual Studio automatically creates a new query and executes it. You’ll see the output shown here:

    

    Click to view larger image

11. Close the SQL file without saving it. The test shows that the query works.

Accessing the function

At this point, you have a database function you can use. You know it works because you tested it. Of course, you have to figure out how to access the function from your code. The following procedure shows how to access the function from within your application.

1. Open the Rewards2Model.EDMX file by double-clicking its entry in Solution Explorer.

2. Right-click in any clear area of the designer and choose Update Model From Database from the context menu. You’ll see the Update Wizard dialog box shown here:

   

   Click to view larger image

3. Check AveragePurchase and click Finish. It seems as if nothing has happened to your diagram, but the .EDMX file does indeed include a change.

4. Open the Form1.cs file. Add this using statement to the beginning of the file:

   using System.Data.Objects.DataClasses;

5. Add this function to the file:

   [EdmFunction("Rewards2Model.Store", "AveragePurchase")]
   public static decimal? AveragePurchase(Int32 CustomerId)
   {
      throw new NotSupportedException("Direct calls are not supported.");
   }

   This function requires a little explanation. The [EdmFunction()] attribute tells the compiler to look into the .EDMX file in the requested store, which is Rewards2Model.Store in this case, for a function named AveragePurchase. You added this entry during the update, even though it doesn’t show up in the designer.

   The function itself requires an odd format. For one thing, it’s a static function, and the return type is decimal. Notice the question mark (?) behind the type declaration. You must include it or the function won’t work. The function name comes next, along with any arguments the function requires. The only content for the function is the exception shown. The function actually executes at the database.

6. Add a new button to Form1. Name the button btnAverage and set its Text property to &Average.

7. Double-click btnAverage to create a new click event handler.

8. Type the following code for the btnAverage_Click() event handler:

   private void btnAverage_Click(object sender, EventArgs e)
   {
      // Create the context.
      Rewards2ModelContainer context = new Rewards2ModelContainer();
   
      // Make the query.
      var CustomerList =
         from cust in context.Customers
         select new
         {
            Name = cust.CustomerName,
            Average = AveragePurchase(cust.Id)
         };
   
      // Create a string to hold the result.
      StringBuilder Output = new StringBuilder();
   
      // Parse the result.
      foreach (var CustEntry in CustomerList)
         Output.Append(
            CustEntry.Name + " makes an average purchase of "
            + CustEntry.Average + ".\r\n");
   
      // Display the result on screen.
      MessageBox.Show(Output.ToString());
   }

   The code begins by creating a context. It then creates a LINQ to Entities query based on that context. Notice that the select part of the query is different. It creates a new object that contains two entries: Name and Average. The Name entry is directly obtained from cust.CustomerName. However, the Average entry is actually a call to the AveragePurchase() function you created in the database in the “Creating the function” section. What you end up with is a structure-like IQueryable object. (Tracing through this example in the debugger is educational, and you should give it a try.)

   After the application obtains the names and averages, it creates a string from them using a foreach loop. Notice that you access the entries as properties. CustEntry is actually an anonymous type. The code ends by displaying the output in a message box.

   NOTE

   This is an example of an application where you must use var instead of either IQueryable or IEnumerable. The problem is that you’re working with an anonymous type—a type that isn’t known at design time.

9. Click Start or press F5. The application compiles and runs.

10. Click Average. You’ll see the output shown here: