Chapter 11. Working with functions and views

 

 

In the previous chapter, you saw that you can embed native SQL commands into the storage model of the EDM to simulate stored procedures. The same idea can be applied to create custom views that aren’t on the database. Creating such views can allow you to write simpler queries, easing the development process.

Another thing that eases querying is the use of database and model functions. These are pieces of code that can be reused across different queries, avoiding repetitive code. In Entity Framework 1.0, these functions could be used only via Entity SQL, but now they’re available in LINQ to Entities too. What’s even better, now you can create your own set of functions in the EDM, allowing further customization and even more reuse.

This chapter is all about these feature. First we’ll discuss embedding views in the EDM, and specifically the defining query feature, which is one of the most powerful, and hidden, features of Entity Framework. Then, we’ll talk about custom database functions and user-defined functions. By the end of this chapter, you’ll graduate from the university of the EDM.

Let’s first look at how you can embed views into the storage model.

11.1. Views in the storage model: defining queries

Conceptually, stored procedures and views are quite different. A stored procedure is a block of statements that optionally returns data; a view is an object that contains data obtained by a predefined query. But the main difference is that stored procedures can’t be combined on the server, whereas views can. If the stored procedures at your disposal already cover all your needs, you may not need views, but in our experience, views are often worth a place in your toolbox.

There are two ways to handle views in Entity Framework:

• Map them as if they were tables—In this case, Entity Framework makes no difference between views and tables. If you modify properties of the entities returned by the query to a view, the context will keep track of them and will generate the statements required to update them on the database (which will fail). Although technically possible, we don’t recommend this approach.
• Map them using a defining query—In this case, Entity Framework knows that the entity is read-only, and unless you specifically instruct it on how to update data, it disables this option. This is the most common scenario because, in most cases, data coming from a view is read-only.

The storage schema is where you decide which approach to use. If you opt for the first choice, chapter 5 contains all the information you need, because Entity Framework treats views as tables. If you opt for the second option, the next section contains all you need to know.

11.1.1. Creating a defining query

Views are a storage affair. It’s not surprising that when you have learned how to define them in the SSDL, there’s nothing more you have to learn. In fact, when you map an entity to a view, the conceptual and the mapping schemas simply define the entity and map it to the view as if it were a normal table.

Before delving into the storage schema, let’s look at a query that generates a view. The following query retrieves the total amount of the orders, grouped by customer:

CREATE VIEW SalesByCustomer
AS
  SELECT c.Name, SUM(d.Quantity * (d.UnitPrice - d.Discount)) AS Total
  FROM dbo.[Order] AS o
  INNER JOIN dbo.Company AS c ON c.CompanyId = o.CustomerId
  INNER JOIN dbo.OrderDetail AS d ON o.OrderId = d.OrderId
  GROUP BY c.Name

Defining a view in the SSDL is simple. In the EntitySet element inside Entity-Container, you put a DefiningQuery node, and inside it you write the SELECT, in database-native SQL code, from the database view. The next snippet shows how this is done:

<EntitySet Name="SalesByCustomer"
  EntityType="OrderITModel.Store.SalesByCustomer">
  <DefiningQuery>
    SELECT Name, Total FROM SalesByCustomer
  </DefiningQuery>
</EntitySet>

When it comes to the entity description, you must choose a key. In this example, you could use the name of the customer, because there is one row per customer, and two customers can’t have the same name. In situations where there isn’t a unique natural key, consider including in the view an autogenerated column that acts as a key.

After you’ve created a class, mapped it against the view, and generated the related entity set in the context, you can query it as if it were a table. The following listing demonstrates this.

Listing 11.1. Code for querying a view, and the generated SQL

C#

from s in ctx.SalesByCustomer
orderby s.Total descending
select s

VB

From s In ctx.SalesByCustomer
Order By s.Total Descending

SQL

SELECT
[Extent1].[Name] AS [Name],
[Extent1].[Total] AS [Total]
FROM (
  SELECT Name, Total
  FROM SalesByCustomer
) AS [Extent1]
ORDER BY [Extent1].[Total] DESC

As you can see, when the SQL generator creates the code, the SQL in the Defining-Query node is embedded in a nested query, and operations defined in the LINQ to Entities or Entity SQL query act on the nested one.

You have seen that the query in the DefiningQuery element isn’t the query that defines the view, but is a command that returns all of its data. This means that in a DefiningQuery element, you can write SQL code to cover any needs you may have. This is the real power of the defining query: if there’s something you can’t map using EDM, a defining query is the solution.

An example of this power is when you have to map a stored procedure to an entity with complex properties.

11.1.2. Mapping stored procedures to classes with complex properties

You know that a stored procedure can’t be mapped to an entity that defines a complex property. This is because the mapping is based on column and property names, and complex properties break this model. A defining query creates a sort of EDM view, so an entity with complex properties can be mapped against it. If the SQL in the defining query returns data from a stored procedure (such as EXEC GetOrders 1), you can easily map it to an entity with complex properties, overcoming the current limitations. If it seems too good to be true, that’s because it isn’t completely true.

You must keep a couple of caveats in mind when following this path:

• A defining query creates a read-only entity. A defining query specifies a query by which you retrieve data. If you need to update data retrieved using a defining query, Entity Framework can’t generate INSERT, UPDATE, and DELETE (CUD) commands based on such a query. To solve this problem, you have to map CUD operations to ad hoc stored procedures.
• Stored procedures can’t be queried. You can’t query stored procedures on the server; you can’t write a SQL statement like SELECT * FROM GetOrders.

In spite of the second limitation, you can still achieve the goal of mapping a database function (not a stored procedure) to an entity with complex properties by bending the database model. The technique we’ll look at uses a SQL Server feature known as table-valued functions.

Like stored procedures, table-valued functions expose the result of a query, but they can be queried on the server. The following snippet shows the code that invokes the table-valued function in the DefininqQuery:

<EntitySet Name="Orders"
  EntityType="OrderITModel.Store.Order">
  <DefiningQuery>
    SELECT * FROM GetOrders()
  </DefiningQuery>
</EntitySet>

GetOrders is the table-valued function. Now you can easily map this view to the Order class, even if it contains the Address complex property.

What happens when relationships come into play? Order has a relationship with its details and with the customer. Because Order is mapped to a query and not directly to a table, the SQL code may get complicated. Fortunately, the runtime query composition of Entity Framework is what the Entity Framework team has concentrated more of its efforts on. An entity mapped to a storage object that uses a defining query can participate in relationships like any other table without any additional effort. The query-nesting process during SQL generation for a defining query is valid even when relationships are taken into account. The result is that the following queries work exactly as expected:

C#

var order = ctx.Orders.Include("Details");
var customer = order.Customer;

VB

Dim order = ctx.Orders.Include("Details")
Dim customer = order.Customer

Mapping an entity to a defining query that invokes a table-valued function that returns all the records of a table may seem useless, but this way you can have database functions that map against entities with complex property too. It’s a good example of the power of the EDM.

Now you know everything about functions and views that return data. The next things we’ll look at help in the reuse of queries: user-defined functions and scalar-valued functions. They allow you to define logic in (sort of) functions that can be reused in querying, so that writing queries becomes simpler.

11.2. User-defined functions and scalar-valued functions

You often need to calculate the total amount of an order detail. The formula is pretty simple: (unit price - discount) * quantity. If you need to calculate this amount in lots of queries, you end up placing the same formula in many different places, and then if you need to change things, you have to check lots of queries. In a large project, this is unacceptable.

To overcome this problem, you can create a function that performs the calculation for you. You can’t use a CLR function because LINQ to Entities isn’t able to translate it into SQL, and Entity SQL has no knowledge of CLR functions; so how can you solve the problem? The answer is to use a scalar-valued function.

11.2.1. Scalar-valued functions

A scalar-valued function is a special type of database function that accepts any parameter you have and returns a scalar value. The following SQL code represents the (unit price - discount) * quantity function:

CREATE FUNCTION GetTotalAmount
(@unitprice as money, @quantity as int, @discount as money)
RETURNS money
AS
BEGIN
  return (@unitprice - @discount) * @quantity
END

When you have the function, you need to import it into the storage schema using the designer, as you did for stored procedures. Then you can invoke the database function using the following Entity SQL query:

SELECT o.OrderId,
  SUM(SELECT VALUE OrderITModel.Store.GetTotalAmount(d.UnitPrice,
        d.Quantity, d.Discount)
        FROM o.OrderDetails As d)
  FROM OrderITEntities.Orders AS o

In Entity Framework 1.0, only Entity SQL lets you use the database functions. There’s no way to use it in a LINQ to Entities query even if you import the function to the conceptual schema. This is because LINQ to Entities knows nothing about the EDM; it works on .NET classes and methods.

Fortunately, this limitation was lifted in Entity Framework version 4.0. To use a scalar-valued function in LINQ to Entities, you need to create a stub method, as shown in the following listing, which acts as a bridge between LINQ to Entities and the EDM.

Listing 11.2. Stub method that maps a function in the storage to a CLR method

C#

[EdmFunction("OrderITModel.Store", "GetTotalAmount")]
public static Nullable<decimal> GetTotalAmount(Nullable<decimal> unitprice,
  Nullable<int> quantity, Nullable<decimal> discount)
{
  throw new NotImplementedException("Cannot invoke this method");
}

VB

<EdmFunction("OrderITModel.Store", "GetTotalAmount")> _
Public Shared Function GetTotalAmount( _
  ByVal unitprice As Nullable(Of Decimal), _
  ByVal quantity As Nullable(Of Integer), _
  ByVal discount As Nullable(Of Decimal)) As Nullable(Of Decimal)
    Throw New NotImplementedException("Cannot invoke this method")
End Function

This function has many prerequisites:

• It must be static.
• It must return the same type as the database function.
• It must be marked with the EdmFunction attribute. (The attribute needs two mandatory parameters: the storage schema namespace and the name of the function.)
• Its body should throw an exception. (This isn’t mandatory, but because the function is only a stub for LINQ to Entities, its code is never really invoked.)

 

Note

Unfortunately, the POCO template doesn’t generate the stub method, so you have to write everything on your own. In chapter 13, we’ll show you how to modify the template to generate this code too.

 

After you’ve created the stub method, LINQ to Entities knows that this stub method is mapped to the database function in the storage schema, so you can consume it in your queries as shown in the following listing. We prefer putting the method in the context class, but you can place it wherever you like.

Listing 11.3. Using the stub function in LINQ to Entities

C#

from o in ctx.Orders
select new
{
  o.OrderId,
  Amount = o.OrderDetails.Sum(d => OrderITEntities.GetTotalAmount(
                                d.UnitPrice,
                                d.Quantity,
                                d.Discount))

}

VB

From o In ctx.Orders
Select New With
{
  .o.OrderId,
  .Amount = o.OrderDetails.Sum(Function(d) OrderITEntities.GetTotalAmount(
                                 d.UnitPrice,
                                 d.Quantity,
                                 d.Discount))
}

Placing querying logic in a database function facilitates code reuse and maintainability. This is probably one of the greatest features in Entity Framework 4.0 because in large-scale projects, reusability and maintainability are essential in the long term.

But what if you can’t add the function to the database? What if you need a function that returns more than a scalar value? The database can’t help in such scenarios, so you have to resort to user-defined functions.

11.2.2. User-defined functions

When the database can’t help, the EDM comes to the rescue. In this case, it lets you define a conceptual function that can be used in both Entity SQL and LINQ to Entities (always via a stub).

The function is defined in the CSDL, which knows nothing about SQL. So the question is, how do you conceptually define a function? The answer is, using Entity SQL. You can create a function whose body is an Entity SQL expression that returns a result. This function is a user-defined function.

The output result can be anything. It can be a scalar value, an object, or a DbData-Record. This is the big difference between user-defined functions and scalar-valued functions—the latter can only return scalar values. This doesn’t mean you should only adopt user-defined functions; they’re just more powerful. The choice is entirely up to you.

User-defined functions have one big problem: they aren’t supported by the designer. You have to manually change the EDM to use them. If you don’t like touching the raw XML, this is a real pain.

The first step is to create the function in the conceptual schema, as shown here.

Listing 11.4. User-defined function that returns and accepts scalar values

<Schema ...>
  <Function Name="GetUDFTotalAmount" ReturnType="Decimal">
    <Parameter Name="UnitPrice" Type="Decimal" />
    <Parameter Name="Quantity" Type="Int32" />
    <Parameter Name="Discount" Type="Decimal" />
    <DefiningExpression>
      (UnitPrice - Discount) * Quantity
    </DefiningExpression>
  </Function>
</Schema>

The declaration is straightforward. In the Function node, you declare the function name and return type. Then, you include a Parameter node for each input parameter. Finally, you create a DefiningExpression node, inside which the function code is written using Entity SQL. That’s it for the EDM.

Next, you have to create the CLR stub method for the function to make it available to LINQ to Entities. That’s done the same way as for a scalar-valued function except that the first parameter of the EdmFunction attribute must contain the namespace of the conceptual schema, not the namespace of the storage one. Of course, the name must be changed too. The final code is shown in the following snippet:

C#

[EdmFunction("OrderITModel", "GetUDFTotalAmount")]

VB

<EdmFunction("OrderITModel", "GetUDFTotalAmount")> _

Now you can invoke the function in both LINQ to Entities and Entity SQL queries. In LINQ to Entities queries, you won’t see the difference between a scalar-valued function and a user-defined one. The code is exactly the same, because you simply invoke a method. In Entity SQL, you have to change the way you invoke the function compared with how you invoke the database scalar-valued function because the user-defined function is defined on the conceptual side. The difference is highlighted in the next snippet:

SELECT o.OrderId,
  SUM(SELECT VALUE OrderITModel.GetUDFTotalAmount(d.UnitPrice,
        d.Quantity, d.Discount)
        FROM o.OrderDetails As d)
  FROM OrderITEntities.Orders AS o

So far, scalar-valued functions and user-defined functions have reached the same goal. From now on, you’ll see how user-defined functions are more powerful. We’ll start with an interesting feature: the ability to pass objects as parameters.

Passing an Object as a Parameter of a Function

Suppose that, in the future, you’re asked for another type of discount. To achieve this goal, you add a new column and property to the OrderDetail table and class. Naturally, this affects the way the total amount is calculated, because the new discount comes into play.

You need to change the GetUDFTotalAmount function to accept a fourth parameter, and that means changing all callers to pass that parameter. If the function is used in several places, this work can be tedious and error prone. The alternative is to change your approach and to pass the entire OrderDetail object. This way, if you need to add another parameter, you won’t have to change the code but just the function in the EDM. That’s shown in this snippet:

<Function Name="GetUDFTotalAmount" ReturnType="Decimal">
  <Parameter Name="detail" Type="OrderITModel.OrderDetail" />
    <DefiningExpression>
      (detail.UnitPrice - detail.Discount) * detail.Quantity
    </DefiningExpression>
</Function>

Notice that now there is only one Parameter node, and its type is a class mapped in the EDM (OrderDetail). The code is Entity SQL, so you can refer to the parameter properties as in a classic Entity SQL query.

The stub signature is different because now it accepts an OrderDetail instance as a parameter, and not the unit price, quantity, and discount parameters. Naturally, both LINQ to Entities and Entity SQL queries must be adapted to this different parameter list.

Passing an object as a parameter is only half of the game. You can even let the function return objects.

Returning a Nontyped Object from a Function

Suppose that you often perform projections. Repeating this in every query is error prone and troublesome when something changes. It would be great if you could encapsulate the projection in a function and then reuse it.

The following listing takes out the customer’s name and the main information about the shipping and billing addresses.

Listing 11.5. User-defined function that accepts an object and returns a DbDataRecord

<Function Name="GetUDFAddresses">
  <ReturnType>
    <RowType>
      <Property Name="Name" Type="String" />
      <Property Name="BillingAddress" Type="String"/>
      <Property Name="BillingCity" Type="String"/>
      <Property Name="ShippingAddress" Type="String"/>
      <Property Name="ShippingCity" Type="String"/>
    </RowType>
  </ReturnType>
  <Parameter Name="customer" Type="OrderITModel.Customer" />
  <DefiningExpression>
    ROW(detail.Name, detail.BillingAddress.Address,
        detail.BillingAddress.City, detail.ShippingAddress.Address,
        detail.ShippingAddress.City)
  </DefiningExpression>
</Function>

The return type of this function is a DbDataRecord that contains the columns specified in the ReturnType/RowType node. It’s important that the Entity SQL expression return the columns in the same order as they’re declared, and that they’re wrapped inside the ROW function.

The stub method returns a DbDataRecord. There’s nothing more you need to do. Now you can use the function in listing 11.5 in both LINQ to Entities and Entity SQL queries, as in the following code:

C#

from c in ctx.Companies.OfType<Customer>()
select OrderITEntities.GetUDFAddresses(c)

VB

From c In ctx.Companies.OfType(Of Customer)()
Select OrderITEntities.GetUDFAddresses(c)

Entity SQL

SELECT OrderITModel.GetUDFAddresses(c)
FROM OFTYPE(OrderITEntities.Companies, OrderIT.Model.Customer) As c

Working with the DbDataRecord isn’t bad, but having a class returned is much better. This is another great opportunity you can take advantage of.

Returning a Typed Object from a Function

The tweaks needed to switch from a DbDataRecord instance to a typed object are very minor. First, you have to create the class that holds the data. Because it must be in the CSDL, you can easily create that using the designer. Then you have to create the function shown in the following fragment:

<Function Name="GetUDFTypedAddresses">
  <ReturnType Type="OrderITModel.CustomerProjection"/>
  <Parameter Name="customer" Type="OrderITModel.Customer" />
  <DefiningExpression>
    OrderITModel.CustomerProjection(customer.Name,
      customer.BillingAddress.Address, customer.BillingAddress.City,
      customer.ShippingAddress.Address, customer.ShippingAddress.City)
  </DefiningExpression>
</Function>

The first thing to notice here is that the return type is neither a scalar value nor a generic class, but a specific type (the complex one generated in the designer). The second point is that the return columns aren’t wrapped in a Row function, but in the return type class. This is an Entity SQL feature that allows the return of a typed object instead of a generic DbDataRecord. The stub method for such a function returns the object instead of a DbDataRecord.

 

Note

It’s mandatory to place the method parameters in the same order in which the properties of the class are declared.

 

The LINQ to Entities and Entity SQL queries don’t change. The only thing that changes is the way the application manages the result, because now it’s a class and that’s much better.

All of the preceding functions have one thing in common—they return a single instance. Because functions are all about reuse and encapsulation, it would be a pity if you couldn’t return collections too. Fortunately, that’s possible.

11.2.3. User-defined functions and collection results

Suppose that in many queries, you need to return only the order details that have no discount. Repeating these conditions in all the queries would be a problem; a ready-to-use function would definitely be better.

User-defined functions can return an enumerable of scalar values, an enumerable of DbDataRecord instances, or an enumerable of objects. There’s no limit to what you can do.

Returning a List of Scalar Values

Let’s start with the basics and return the IDs of the details that have no discount. Creating this function is pretty easy, as you can see in this snippet:

<Function Name="GetUDFDetailsWithNoDiscount_Scaar"
  ReturnType="Collection(Int32)">
  <Parameter Name="o" Type="OrderITModel.Order" />
  <DefiningExpression>
    SELECT VALUE d.OrderDetailId FROM o.OrderDetails AS d
    WHERE d.discount == 0
  </DefiningExpression>
</Function>

The ReturnType attribute contains the Collection keyword, which says that this function returns a list of Int32 instances and not a single value. In the Entity SQL code in the DefiningExpression element, you place a full query, not just a simple expression. With these simple tweaks, a function can return a collection of items.

The stub method must return an IEnumerable<Nullable<Int32>> object. That’s the only tweak needed to make the stub method work. Once again, you can use the method in your queries.

Returning a List of Generic Objects

You may need more information than just the ID of the details. For instance, you may need the quantity and the unit price. The following listing shows how you can do that using the DbDataRecord class.

Listing 11.6. User-defined function that returns a list of DbDataRecord objects

<Function Name="GetUDFDetailsWithNoDiscount_Record">
  <ReturnType>
    <CollectionType>
      <RowType>
        <Property Name="OrderDetailId" Type="Int32"/>
        <Property Name="UnitPrice" Type="Decimal"/>
        <Property Name="Quantity" Type="Int32"/>
      </RowType>
    </CollectionType>
  </ReturnType>
  <Parameter Name="o" Type="OrderITModel.Order" />
  <DefiningExpression>
    SELECT d.OrderDetailId, d.UnitPrice, d.Quantity
    FROM o.OrderDetails AS d
    WHERE d.discount == 0
  </DefiningExpression>
</Function>

The ReturnType attribute disappears, and you create the ReturnType node to specify the shape of the result. Because it’s a list, you use a CollectionType element and then use RowType to specify the output properties.

The stub returns an IEnumerable<DbDatarecord>. You’re ready again to use the function in your queries.

Returning a List of Typed Objects

Returning a list of typed objects is fairly simple. You just have to specify the type of the object returned and prepare the Entity SQL query using the following code:

<Function Name="GetUDFDetailsWithNoDiscount_Object"
  ReturnType="Collection(OrderITModel.OrderDetail)">
  <Parameter Name="o" Type="OrderITModel.Order" />
  <DefiningExpression>
    SELECT VALUE d FROM o.OrderDetails AS d WHERE d.discount == 0
  </DefiningExpression>
</Function>

This code is very similar to that used for returning a scalar value, and it needs no further explanation.

The stub method for such function is simple. In fact, it returns an IEnumerable <OrderDetail> object.

In the end, scalar-valued functions enable a great level of reuse in your code. That’s worth taking into account when you’re developing a project.

11.3. Summary

Mastering functions and defining queries is important in any real-world project. Functions, in particular, really make a difference when you’re writing queries. They avoid repetitive code and promote code reuse to an incredible extent. No technology before this has ever enabled such code reuse in queries. You can’t help falling in love with them.

What’s great about functions is that in addition to enabling the use of database functions in LINQ to Entities, they also enable you to write your own custom functions in Entity SQL and make them available to LINQ to Entities. Thumbs up!

Now you have a complete understanding of the EDM, and you’ve been introduced to its potential and its intricacies. What you need to know next is how you can access EDM metadata in code. That’s the subject of the next chapter.