Database Reference
In-Depth Information
var cats = from c in context.Categories
select new
{
Name = c.CategoryName,
AveragePrice = MyFunctions.AverageUnitPrice(c)
};
foreach (var cat in cats)
{
Console.WriteLine("Category '{0}' has an average price of {1}",
cat.Name, cat.AveragePrice.ToString("C"));
}
}
}
}
public class MyFunctions
{
[EdmFunction("EFRecipesModel", "AverageUnitPrice")]
public static decimal AverageUnitPrice(Category category)
{
throw new NotSupportedException("Direct calls are not supported!");
}
}
Following is the output of the code in Listing 11-2:
Using eSQL for the query...
Category 'Backpacking Tents' has an average price of $79.99
Category 'Family Tents' has an average price of $159.99
Using LINQ for the query...
Category 'Backpacking Tents' has an average price of $79.99
Category 'Family Tents' has an average price of $159.99
How It Works
Model-defined functions are created in the conceptual layer and written in eSQL. Of course, this allows you to
program against the entities in your model as we have done here, referencing the Category and Product entities and
their association in the function's implementation. The added benefit is that we are not tied to a specific storage layer.
We could swap out the lower layers, even the database provider, and our program would still work.
The designer currently provides no support for model-defined functions. Unlike stored procedures, which are
supported by the designer, model-defined functions do not show up in the model browser nor anywhere else in the
designer. The designer will not check for syntax errors in the eSQL. You will find out about these at runtime. However,
the designer will at least tolerate model-defined functions enough to open the .edmx file. Model-defined functions are
evaluated in Entity Framework and not in the back-end database.
In Listing 11-2, the code starts off by inserting a couple of categories and a few products for each. Once we have
the data in place, we query it using two slightly different approaches.
