As a technophile who also happens to write I know how hard it can be when the documentation for a new technology is squiffy. Squiffy being you can’t figure it out from existing documentation and blogs because too many details are left out. This what happened when I started looking at EF4 and Model Defined Functions. A model “declared” function is one that you import from your storage provider, like a SQL Server stored procedure. A model defined function is one that is completely defined in your model. EF4 supports model defined functions but the documentation leaves out too many details that explain exactly how you get this to work. In this blog I will walk you through creating a model defined function in EF4 and then binding the results to our XtraPivotGrid.
An overview of the process requires some data you want to show in a Pivot Grid, an idea for tweaking the result set, and Entity Framework 4 model, and a custom query defined in the model only. The two basic steps are to add an XtraPivotGrid control to a WinForms project and add an ADO.NET Entity Data Model. (This example is based on Visual Studio 2010 and DevExpress controls—I used the a beta version of DevExpress controls for 10.2.) There are a lot of blogs and articles on EF Models but here is the nuts and bolts. (If you know how to do this step skip the next section.)
Adding an Entity Framework Model to Your WinForms Application
An Entity Framework Data Model is created from an applet in the Add New Items dialog. In its simplest form an EF model is simply some code generated classes that create entities based on some storage item like a database table. The basic idea is that once you have the data model interaction with the data store is easier. (We all refer to this as a data access layer in a general sense.)
To create the entity data model Follow these steps to create:
- Create a WinForms project. (This will work in other kinds of projects too.)
- Click Project|Add New Item
- Select the ADO.NET Entity Data Model from the Add New Item dialog and name it NorthwindModel
- Click Add
- In the Entity Data Model Wizard (that starts up) select Generate from database and click Next
- Choose an existing Northwind connection or click New Connection to add a connection
- Use the defaults for everything else in this step and click Next
- In the Choose Your Data Objects step expand Tables and select the Products table
- Click Finish
That’s all there is to it. After you click Finish the .edmx file and designer will be open. An entity model is the important element you need.
Defining a Model Defined Function
A model defined function is one that exists only in your model. A model declared function is one you import from your server. We will be creating a model defined function. For our scenario we will add a model defined function that filters on ReorderLevel. It is worth stating that you could add such a trivial filter right in the LINQ query, but we will create the model defined function and use such a simple example to focus on the mechanics of model functions. As a general rule I would only add a model defined function if it gave my solution some extra leverage that might be hard for my developers to do themselves with LINQ like joins.
The key to adding a model defined function is to modify the model XML, but a lot of the blogs don’t tell you which XML to modify and leave out a few steps. You should be able to reproduce this process because I am not going to leave any steps out. To create the model defined function you need to modify the SSDL in the EDMX file and then import the function and call it with your LINQ query, binding the results to the PivotGrid. Here they are:
- Right click on the .EDMX file in the solution explorer and select Open With
- Choose the XML (Text) Editor—yep, we gotta modify the EDMX file directly
- The EDMX file will look like Listing 1. Find the SSDL section
- At the end of the SSDL section just before the closing </Schema> tag add a <Function> tag
- Add a Name attribute Name=”GetProductsByReorderLevel”
- Add an IsComposable=”false” attribute
- In the <Function> tag add a <CommandText> tag and write your vanilla SQL inside of the <CommandText></CommandText> tag; I used select * from Products where ReorderLevel > @p1
- Make sure you use an @ in the query itself or the framework will think the right hand side operand is a column
- After the <CommandText>—parameters are expected to follow—add a <Parameter> tag with a Name=”p1”, Mode=”In”, and Type=”int” attributes. (Make sure you don’t use @ in the parameter Name attribute; doing so will kill the code generation.)
Here is the added XML block and Listing 1 shows the complete EDMX listing.
<Function Name="GetProductsByReorderLevel" IsComposable="false">
<CommandText>
select * FROM Products
where ReorderLevel > @p1;
</CommandText>
<Parameter Name="p1" Mode="In" Type="int" MaxLength="75" />
</Function>
Listing 1: The complete .EDMX listing with the SSDL model defined function.
<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="2.0" xmlns:edmx="http://schemas.microsoft.com/ado/2008/10/edmx">
<!-- EF Runtime content -->
<edmx:Runtime>
<!-- SSDL content -->
<edmx:StorageModels>
<Schema Namespace="NorthwindModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">
<EntityContainer Name="NorthwindModelStoreContainer">
<EntitySet Name="Products" EntityType="NorthwindModel.Store.Products" store:Type="Tables" Schema="dbo" />
</EntityContainer>
<EntityType Name="Products">
<Key>
<PropertyRef Name="ProductID" />
</Key>
<Property Name="ProductID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
<Property Name="ProductName" Type="nvarchar" Nullable="false" MaxLength="40" />
<Property Name="SupplierID" Type="int" />
<Property Name="CategoryID" Type="int" />
<Property Name="QuantityPerUnit" Type="nvarchar" MaxLength="20" />
<Property Name="UnitPrice" Type="money" />
<Property Name="UnitsInStock" Type="smallint" />
<Property Name="UnitsOnOrder" Type="smallint" />
<Property Name="ReorderLevel" Type="smallint" />
<Property Name="Discontinued" Type="bit" Nullable="false" />
</EntityType>
<Function Name="GetProductsByReorderLevel" IsComposable="false">
<CommandText>
select * FROM Products
where ReorderLevel > @p1;
</CommandText>
<Parameter Name="p1" Mode="In" Type="int" MaxLength="75" />
</Function>
</Schema>
</edmx:StorageModels>
<!-- CSDL content -->
<edmx:ConceptualModels>
<Schema Namespace="NorthwindModel" Alias="Self"
xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation"
xmlns="http://schemas.microsoft.com/ado/2008/09/edm">
<EntityContainer Name="NorthwindEntities" annotation:LazyLoadingEnabled="true">
<EntitySet Name="Products" EntityType="NorthwindModel.Product" />
</EntityContainer>
<EntityType Name="Product">
<Key>
<PropertyRef Name="ProductID" />
</Key>
<Property Name="ProductID" Type="Int32" Nullable="false"
annotation:StoreGeneratedPattern="Identity" />
<Property Name="ProductName" Type="String" Nullable="false"
MaxLength="40" Unicode="true" FixedLength="false" />
<Property Name="SupplierID" Type="Int32" />
<Property Name="CategoryID" Type="Int32" />
<Property Name="QuantityPerUnit" Type="String" MaxLength="20"
Unicode="true" FixedLength="false" />
<Property Name="UnitPrice" Type="Decimal" Precision="19" Scale="4" />
<Property Name="UnitsInStock" Type="Int16" />
<Property Name="UnitsOnOrder" Type="Int16" />
<Property Name="ReorderLevel" Type="Int16" />
<Property Name="Discontinued" Type="Boolean" Nullable="false" />
</EntityType>
</Schema>
</edmx:ConceptualModels>
<!-- C-S mapping content -->
<edmx:Mappings>
<Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2008/09/mapping/cs">
<EntityContainerMapping StorageEntityContainer="NorthwindModelStoreContainer"
CdmEntityContainer="NorthwindEntities">
<EntitySetMapping Name="Products"><EntityTypeMapping
TypeName="NorthwindModel.Product"><MappingFragment StoreEntitySet="Products">
<ScalarProperty Name="ProductID" ColumnName="ProductID" />
<ScalarProperty Name="ProductName" ColumnName="ProductName" />
<ScalarProperty Name="SupplierID" ColumnName="SupplierID" />
<ScalarProperty Name="CategoryID" ColumnName="CategoryID" />
<ScalarProperty Name="QuantityPerUnit" ColumnName="QuantityPerUnit" />
<ScalarProperty Name="UnitPrice" ColumnName="UnitPrice" />
<ScalarProperty Name="UnitsInStock" ColumnName="UnitsInStock" />
<ScalarProperty Name="UnitsOnOrder" ColumnName="UnitsOnOrder" />
<ScalarProperty Name="ReorderLevel" ColumnName="ReorderLevel" />
<ScalarProperty Name="Discontinued" ColumnName="Discontinued" />
</MappingFragment></EntityTypeMapping></EntitySetMapping>
</EntityContainerMapping>
</Mapping>
</edmx:Mappings>
</edmx:Runtime>
<!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
<Designer xmlns="http://schemas.microsoft.com/ado/2008/10/edmx">
<Connection>
<DesignerInfoPropertySet>
<DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" />
</DesignerInfoPropertySet>
</Connection>
<Options>
<DesignerInfoPropertySet>
<DesignerProperty Name="ValidateOnBuild" Value="true" />
<DesignerProperty Name="EnablePluralization" Value="True" />
<DesignerProperty Name="IncludeForeignKeysInModel" Value="True" />
</DesignerInfoPropertySet>
</Options>
<!-- Diagram content (shape and connector positions) -->
<Diagrams>
<Diagram Name="NorthwindModel">
<EntityTypeShape EntityType="NorthwindModel.Product" Width="1.5" PointX="0.75"
PointY="0.75" Height="2.9417936197916665" IsExpanded="true" />
</Diagram>
</Diagrams>
</Designer>
</edmx:Edmx>
I intentionally did not use code formatting and re-paginated the XML to fit the page, but don’t do that. Just add the <Function> block in bold in Listing 1.
One might think that is all there is too it, but the Entity Framework maps at the the conceptual storage level, CSDL, and we added the model function at the storage level. You need to map the function at the conceptual level next.
Before you continue close the .edmx file. Make sure you save it. Then, double-click the .edmx file to open the designer, right click, and click Validate to make sure your XML is good.
Mapping the Model Defined Function at the Conceptual Level
Your model-defined function at the SSDL should now be available. To view the model select View|Other Windows|Entity Data Model Browser. If everything is going well so far your function should be available in the Model Browser under Northwind.Store|Stored Procedures—see Figure 1.
Figure 1: The model-defined function at the storage level.
The next step is to import the function, which in essence adds an entry to the CSDL level and let’s you call it. To add the function at the conceptual level right click on the NorthwindModel.edmx file and follow these steps:
- Click Add|Function Imports
- Using Figure 2 as a visual guide, enter a function name. I used GetProductsByReorderLevel, but if you have funky Sproc names then you can make them more code friendly here.
- Select the Stored Procedure Name
- Change the Returns a Collection of to Entities and select the Product entity type
- Click OK
Now if you check the Model Browser you will see that the Function Imports section contains a usable CSDL function—see Figure 3.
Figure 2: Add Function Import to add the function to the CSDL-conceptual level—so you can call it with LINQ to Entities.
Figure 3: The function GetProductsByReorderLevel can now be called from LINQ to Entities.
Binding the PivotGrid to the Model Defined Function
Unfortunately the data source wizard in Visual Studio 2010 doesn’t know about model defined functions so we can’t use the wizard to get the data from the model defined function. However, since the model-defined function is mapped to the Product entity you can use the Data Source Configuration Wizard to bind to an Object. Follow these steps to configure the PivotGrid with Product entity data.
- Click the PivotGridControl’s smart tags menu, click Choose Data Source|Add Project Data Source
- In the Data Source Configuration Wizard choose Object
- Click Next
- Expand the project’s node
- Expand the first child node and check Product
- Click Finish
Figure 4: Let the Data Source Configuration Wizard configure the XtraPivotGrid’s fields for you.
In the code behind you need just a couple of lines in the code behind and you are off to the races.
Listing 2 initializes the NorthwindEntities object, calls the model-defined function, and updates the productBindingSource.DataSource to point to the new data. It takes some work to get here but most of the effort was completed by wizards with just a tiny bit of XML editing and a very little bit of code. Since code is generally the most time consuming thing and the most expensive this is actually a beautiful thing.
Listing 2: Once you get your entities right it takes just a little bit of code to use them.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace ModelDefinedFunctionAndPivotGrid
{
public partial class Form1 : Form
{
NorthwindEntities entities = new NorthwindEntities();
public Form1()
{
InitializeComponent();
var results = from product in entities.GetProductsByReorderLevel(10)
select product;
productBindingSource.DataSource = results;
}
}
}
I’d love to see an SSDL designer for model defined functions. I’d also like to eliminate the leap from SSDL to CSDL. It doesn’t make a lot of sense that one would add a function they didn’t want to use from LINQ, but EF4 is a big improvement over earlier versions. Figure 5 shows the running application after I have moved some fields around at runtime.
Figure 4: The XtraPivotGrid with data from a model-defined function and a single LINQ query.