Functions
Functions provide a power mechanism for code reuse, and offer you a good way to make your code cleaner and more understandable. They can also be used to leverage code in the Entity Framework runtime as well as in the database layer. Functions are of various types: Rowset Functions, Aggregate Functions, Ranking Functions, and Scalar Functions. Functions are either deterministic or nondeterministic. Functions are deterministic when they always return the same result any time that they are called by using a specific set of input values. Functions are nondeterministic when they could return different results every time they are called, even with the same specific set of input values.
In the first seven recipes, we explore model-defined functions. These functions allow you to create functions at the conceptual layer. These functions are defined in terms of Entity Framework types and your model entities. This makes them portable across data store implementations.
In the remaining recipes, we show you how to use functions defined by Entity Framework and the database layer. These functions are implemented for you, and they allow you to leverage existing code either in Entity Framework’s runtime or, closer to your data, in the database layer.
11-1. Returning a Scalar Value from a Model-Defined Function
Problem
You want to define a function in the conceptual model that takes an instance of an entity and returns a scalar value.
Solution
Suppose that you have a model like the one shown in Figure 11-1.
Figure 11-1. A model for products and categories
To create a model-defined function that takes an instance of the Category entity and returns the average unit price for all of the products in the given category, do the following:
- Right-click the .edmx file in the Solution Explorer, and select Open With
XML Editor. - Insert the code in Listing 11-1 just below the <Schema> tag in the conceptual models section of the .edmx file. This defines the function in the model.
Listing 11-1. Definition of the AverageUnitPrice() Function in the Model
<Function Name="AverageUnitPrice" ReturnType="Edm.Decimal">
<Parameter Name="category" Type="EFRecipesModel.Category" />
<DefiningExpression>
ANYELEMENT(Select VALUE Avg(p.UnitPrice)
from EFRecipesEntities.Products as p where p.Category == category
)
</DefiningExpression>
</Function> - Insert into and query the model using code similar to the pattern shown in Listing 11-2.
Listing 11-2. Inserting into and Querying the Model Using the Model-Defined Function AverageUnitPrice()
class Program
{
static void Main(string[] args)
{
RunExample();
}
static void RunExample()
{
using (var context = new EFRecipesEntities())
{
var c1 = new Category { CategoryName = "Backpacking Tents" };
var p1 = new Product
{
ProductName = "Hooligan",
UnitPrice = 89.99M,
Category = c1
};
var p2 = new Product
{
ProductName = "Kraz",
UnitPrice = 99.99M,
Category = c1
};
var p3 = new Product
{
ProductName = "Sundome",
UnitPrice = 49.99M,
Category = c1
};
context.Categories.Add(c1);
context.Products.Add(p1);
context.Products.Add(p2);
context.Products.Add(p3);
var c2 = new Category { CategoryName = "Family Tents" };
var p4 = new Product
{
ProductName = "Evanston",
UnitPrice = 169.99M,
Category = c2
};
var p5 = new Product
{
ProductName = "Montana",
UnitPrice = 149.99M,
Category = c2
};
context.Categories.Add(c2);
context.Products.Add(p4);
context.Products.Add(p5);
context.SaveChanges();
}
// with eSQL
using (var context = new EFRecipesEntities())
{
Console.WriteLine("Using eSQL for the query...");
Console.WriteLine();
string sql = @"Select c.CategoryName, EFRecipesModel
.AverageUnitPrice(c) as AveragePrice from
EFRecipesEntities.Categories as c";
var objectContext = (context as IObjectContextAdapter).ObjectContext;
var cats = objectContext.CreateQuery<DbDataRecord>(sql);
foreach (var cat in cats)
{
Console.WriteLine("Category '{0}' has an average price of {1}",
cat[”CategoryName”], ((decimal)cat["AveragePrice"]).ToString("C"));
}
}
// with LINQ
using (var context = new EFRecipesEntities())
{
Console.WriteLine();
Console.WriteLine("Using LINQ for the query...");
Console.WriteLine();
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.
In the first query example, we build an eSQL statement that calls the AverageUnitPrice() function. We create and execute the query. For each row in the results, we pull out the data for the first column, which is the category name, and the data for the second column, which is the average unit price for the category. We display them for each row.
The second query example is a little more interesting. Here we use the AverageUnitPrice() function in a LINQ query. To do this, we need to add a stub method in a separate class. The method is decorated with the [EdmFunction()] attribute, which marks it as an implementation of a model-defined function. This CLR method will not actually be called, which is evident by the exception we throw in the body of the method. Because we return a scalar value, the method’s implementation here is simply for the signature (the parameter number, types, and return type). In the LINQ query, we grab each category and reshape the results into an anonymous type that holds the category name and the result of calling the AverageUnitPrice() method in the MyFunction class. This is the stub we created that is tied to the AverageUnitPrice() model-defined function. For each of the resulting objects, we display the category name and the category’s average unit price.
DbContext is the light version of ObjectContext. Whenever a CreateQuery is to be used to execute a Sql (Entity SQL), then ObjectContext is required. Thus ObjectContext is fetched through DbContext using (context as IObjectContextAdapter) ObjectContext.
The parameters for model-defined functions can be scalar, entity types, complex types, anonymous types, or collections of these. In many of the recipes in this chapter, we’ll show you how to create and use model-defined functions with these parameter types.
The parameters for model-defined functions don’t show direction. There are no “out” parameters, only implied “in” parameters. The reason for this is that model-defined functions are composable and can be used as part of LINQ queries. This prevents them from returning values in output parameters.
In this example, we returned a single scalar decimal value. To do this, we had to explicitly return a scalar using the AnyElement operator. Entity Framework does not know how to map a collection to a scalar value. We help out here by using the AnyElement operator, which signals that only a single value will result from the query. It just so happens that we return a collection of just one element from which the AnyElement operator selects just one element.
Best Practice
Model-defined functions provide a clean and practical way to implement parts of a conceptual model that would be tedious if not impossible any other way. Here are some best practices and uses for model-defined functions.
- Model-defined functions are written in eSQL and defined at the conceptual layer. This provides a level of abstraction from the details of the store layer and allows you to leverage a more complete model independent of the store layer.
- You can define functions for expressions that you commonly use in your LINQ or eSQL queries. This provides better code organization and allows code reuse. Also, if you use LINQ, then due to the nature of IntelliSense and compile-time checks, there will be fewer code issues because of typos.
- Model-defined functions are composable, which allows you to implement functions that serve as building blocks for more complex expressions. This can both simplify your code and make it more maintainable.
- Model-defined functions can be used in places where you have computed properties. A computed property, like a function, is a read-only value. For properties, you incur the cost of computing the value when the entity is materialized, whether or not you need the computed property. With a model-defined function, the cost of computing the value is incurred only when you actually need the value.
11-2. Filtering an Entity Collection Using a Model-Defined Function
Solution
Suppose that we have a model with Customers and Invoices, as shown in Figure 11-2.
Figure 11-2. Customer and Invoice in a model
Let’s say that we want to create a model-defined function that takes a collection of invoices and filters the collection to those invoices that have an amount greater than $300. Just for fun, let’s use this model-defined function in a query that further filters this collection to just those invoices created after 5/1/2013. Of course, we’ll want to load all of the customers associated with these invoices.
To get started, do the following:
- Right-click the .edmx file in the Solution Explorer, and select Open With XML Editor.
- Insert the code in Listing 11-3 just below the <Schema> tag in the conceptual models section of the .edmx file. This defines the function in the model.
Listing 11-3. The GetInvoices() Model-Defined Function
<Function Name="GetInvoices" ReturnType="Collection(EFRecipesModel.Invoice)" >
<Parameter Name="invoices" Type="Collection(EFRecipesModel.Invoice)">
</Parameter>
<DefiningExpression>
Select VALUE i
from invoices as i where i.Amount > 300M
</DefiningExpression>
</Function> - Insert into and query the model using code similar to the pattern shown in Listing 11-4.
Listing 11-4. Querying the Model Using the GetInvoices() Model-Defined Function with Both eSQL and LINQ
class Program
{
static void Main(string[] args)
{
RunExample();
}
static void RunExample()
{
using (var context = new EFRecipesEntities())
{
DateTime d1 = DateTime.Parse("8/8/2013");
DateTime d2 = DateTime.Parse("8/12/2012");
var c1 = new Customer { Name = "Jill Robinson", City = "Dallas" };
var c2 = new Customer { Name = "Jerry Jones", City = "Denver" };
var c3 = new Customer { Name = "Janis Brady", City = "Dallas" };
var c4 = new Customer { Name = "Steve Foster", City = "Dallas" };
context.Invoices.Add(new Invoice
{
Amount = 302.99M,
Description = "New Tires",
Date = d1,
Customer = c1
});
context.Invoices.Add(new Invoice
{
Amount = 430.39M,
Description = "Brakes and Shocks",
Date = d1,
Customer = c2
});
context.Invoices.Add(new Invoice
{
Amount = 102.28M,
Description = "Wheel Alignment",
Date = d1,
Customer = c3
});
context.Invoices.Add(new Invoice
{
Amount = 629.82M,
Description = "A/C Repair",
Date = d2,
Customer = c4
});
context.SaveChanges();
}
using (var context = new EFRecipesEntities())
{
Console.WriteLine("Using eSQL query...");
string sql = @"Select value i from
EFRecipesModel.GetInvoices(EFRecipesEntities.Invoices) as i
where i.Date > DATETIME'2013-05-1 00:00'
and i.Customer.City = @City";
var objectContext = (context as IObjectContextAdapter).ObjectContext;
var invoices = objectContext.CreateQuery<Invoice>(sql,
new ObjectParameter("City", "Dallas")).Include("Customer");
foreach (var invoice in invoices)
{
Console.WriteLine("Customer: {0} Invoice for: {1}, Amount: {2}",
invoice.Customer.Name, invoice.Description, invoice.Amount);
}
}
using (var context = new EFRecipesEntities())
{
Console.WriteLine();
Console.WriteLine("Using LINQ query...");
DateTime date = DateTime.Parse("5/1/2013");
var invoices = from invoice in
MyFunctions.GetInvoices(context.Invoices)
where invoice.Date > date
where invoice.Customer.City == "Dallas"
select invoice;
foreach (var invoice in ((DbQuery<Invoice>)invoices)
.Include("Customer"))
{
Console.WriteLine("Customer: {0}, Invoice for: {1}, Amount: {2}",
invoice.Customer.Name, invoice.Description, invoice.Amount);
}
}
}
}
public class MyFunctions
{
[EdmFunction("EFRecipesModel", "GetInvoices")]
public static IQueryable<Invoice> GetInvoices(IQueryable<Invoice> invoices)
{
return invoices.Provider.CreateQuery<Invoice>(
Expression.Call((MethodInfo) MethodInfo.GetCurrentMethod(),
Expression.Constant(invoices,
typeof(IQueryable<Invoice>))));
}
}
Following is the output of the code in Listing 11-4:
Using eSQL for the query...
Customer: Jill Robinson Invoice for: New Tires, Amount: 302.99
Using LINQ for the query...
Customer: Jill Robinson, Invoice for: New Tires, Amount: 302.99
How It Works
From the definition of our GetInvoices() function in Listing 11-3, we see that it takes a collection of Invoices and returns a collection of Invoices. On the CLR side, this translates to taking an IQueryable<Invoice> and returning an IQueryable<Invoice>.
In the eSQL expression, we use the GetInvoices() function in the from clause. We pass in the unfiltered collection of Invoices and our GetInvoices() function returns the filtered collection. We further filter the collection by date and the customer’s city using a where clause. Then we use CreateQuery<Invoice>() to build the ObjectQuery<Invoice>. In building the query, we pass in the parameter to filter by city and use the Include() method to include the related customers. Once we have the ObjectQuery<Invoice>, we iterate over the resulting collection and print out the invoices that matched the two filters that we applied.
For the LINQ query, the story is a little more interesting. Here we build the expression using the GetInvoices() method in the from clause and filter the resulting collection by date and city, much like we did with the eSQLexpression. However, to use our function in a LINQ query, we need to implement a CLR method that takes an IQueryable<Invoice> and returns an IQueryable<Invoice>. Unlike the stub method in Recipe 11-1, in which the model-defined function returned a scalar value, here we have to provide an implementation in the body of the method. Creating this method is often referred to as bootstrapping.
Here are some rules for bootstrapping:
- Bootstrapping is required when a model-defined function returns an IQueryable<T>.
- When a function returns an IQueryable<T> but does not take an IQueryable<T>, the bootstrapping method must be implemented in a partial class of the ObjectContext.
The second rule comes about because we can’t return an IQueryable<T> that has meaning in our ObjectContext without starting with an IQueryable<T>. If we pass in an IQueryable<T>, then we can perform some operation in our bootstrapping method that returns a related IQueryable<T>. However, we can’t manufacture an IQueryable<T> outside of a partial class of our ObjectContext. In our example, we received an IQueryable<T> as a parameter, so we are free to implement the bootstrapping code outside of a partial class of our ObjectContext.
In the implementation of our bootstrapping method, we get an instance of IQueryProvider from the IQueryable<Invoice> through the Provider property. IQueryProvider.CreateQuery<Invoice>() allows us to tack onto the expression tree for the IQueryable<T>. Here we add in the call to the GetInvoices() function, passing in the collection of invoices that we have.
11-3. Returning a Computed Column from a Model-Defined Function
Problem
You want to return a computed column from a model-defined function.
Solution
Suppose that we have an Employee entity containing the properties FirstName, LastName, and BirthDate, as shown in Figure 11-3.
Figure 11-3. An Employee entity with a few typical properties
We want to create a model-defined function that returns the full name of the employee by combining the FirstName and LastName columns. We want to create another model-defined function that returns the age of the employee based on the value in the BirthDate column.
To create and use these functions, do the following:
- Right-click the .edmx file in the Solution Explorer, and click Open With XML Editor. This will open the .edmx file in the XML Editor.
- Insert the code in Listing 11-5 just below the <Schema> tag in the conceptual models section of the .edmx file. This defines the functions in the model.
Listing 11-5. Code for Model-Defined Functions
<Function Name="FullName" ReturnType="Edm.String">
<Parameter Name="emp" Type="EFRecipesModel.Employee" />
<DefiningExpression>
Trim(emp.FirstName) + " " + Trim(emp.LastName)
</DefiningExpression>
</Function>
<Function Name="Age" ReturnType="Edm.Int32">
<Parameter Name="emp" Type="EFRecipesModel.Employee" />
<DefiningExpression>
Year(CurrentDateTime()) - Year(emp.BirthDate)
</DefiningExpression>
</Function> - Insert into and query the model using code similar to the pattern shown in Listing 11-6.
Listing 11-6. Inserting into and Querying the Model Invoking the Model-Defined Functions Using Both eSQL and LINQ
class Program
{
static void Main(string[] args)
{
RunExample();
}
static void RunExample()
{
using (var context = new EFRecipesEntities())
{
context.Employees.Add(new Employee
{
FirstName = "Jill",
LastName = "Robins",
BirthDate = DateTime.Parse("3/2/1976")
});
context.Employees.Add(new Employee
{
FirstName = "Michael",
LastName = "Kirk",
BirthDate = DateTime.Parse("4/12/1985")
});
context.Employees.Add(new Employee
{
FirstName = "Karen",
LastName = "Stanford",
BirthDate = DateTime.Parse("7/6/1963")
});
context.SaveChanges();
}
using (var context = new EFRecipesEntities())
{
Console.WriteLine("Query using eSQL");
var esql = @"Select EFRecipesModel.FullName(e) as Name,
EFRecipesModel.Age(e) as Age from
EFRecipesEntities.Employees as e";
var objectContext = (context as IObjectContextAdapter).ObjectContext;
var emps = objectContext.CreateQuery<DbDataRecord>(esql);
foreach (var emp in emps)
{
Console.WriteLine("Employee: {0}, Age: {1}", emp["Name"],
emp["Age"]);
}
}
using (var context = new EFRecipesEntities())
{
Console.WriteLine(" Query using LINQ");
var emps = from e in context.Employees
select new
{
Name = MyFunctions.FullName(e),
Age = MyFunctions.Age(e)
};
foreach (var emp in emps)
{
Console.WriteLine("Employee: {0}, Age: {1}", emp.Name,
emp.Age.ToString());
}
}
}
}
public class MyFunctions
{
[EdmFunction("EFRecipesModel", "FullName")]
public static string FullName(Employee employee)
{
throw new NotSupportedException("Direct calls are not supported.");
}
[EdmFunction("EFRecipesModel", "Age")]
public static int Age(Employee employee)
{
throw new NotSupportedException("Direct calls are not supported.");
}
}
The output of the code from Listing 11-6 is as follows:
Query using eSQL
Employee: Jill Robins, Age: 37
Employee: Michael Kirk, Age: 28
Employee: Karen Stanford, Age: 50
Query using LINQ
Employee: Jill Robins, Age: 37
Employee: Michael Kirk, Age: 28
Employee: Karen Stanford, Age: 50
How It Works
Our model-defined functions return types Edm.String for the FullName() function and Edm.Int32 for the Age() function. These functions are defined on the conceptual level, so they don’t directly refer to any type system outside of the Entity Data Model’s type system. These primitive types are easily translated to the CLR type system.
In the <DefiningExpression> or body of the model-defined functions, we directly access the properties of the entities we received in the parameters. There is no need to use a select statement. However, the resulting expression must have a type that matches the type defined as the return type of the function.
After inserting a few employees into our model, we first query using eSQL. We construct an eSQL expression that invokes our two model-defined functions and projects the results to the Name and Age columns. Our eSQL expression results in a collection of anonymous types that contain just the Name and Age members. Because we’re not returning one of the types defined in the model, we declare the type in CreateQuery<T>() to be DbDataRecord. We iterate over the collection resulting from the evaluation of the query and print out the employees’ names and ages.
For the LINQ query, we select from the Employees entity set and project onto an anonymous type containing the Name and Age members. We set these members to the result of invoking our FullName() and Age() functions. As seen in the previous recipes in this chapter, we need to define the corresponding CLR methods. Because we are returning scalar values, these methods are never called and are used only for their signatures. The implementation of these methods reflects this.
We could have created read-only properties in a partial declaration of our Employee entity to implement the full name and age calculations. However, this would force the evaluation of these methods each time the entity is retrieved. With model-defined functions, we perform the calculations only when needed.
11-4. Calling a Model-Defined Function from a Model-Defined Function
Problem
You want to use a model-defined function in the implementation of another model-defined function.
Solution
Suppose that we have the model shown in Figure 11-4, representing the types of associates in a company along with their reporting structure
Figure 11-4. A model representing the associate types in a company together with the reporting association
In our fictional company, team members are managed by a team leader. Team leaders are managed by project managers. Supervisors manage project managers. Of course, there could be many other associate types, but for simplicity we’ll stick with just these few.
If we wanted to return all of the team members for a given project manager or supervisor, we would need to drill down through the project managers and team leaders to get to the team members. To hide the complexity of navigating through these layers, we can create model-defined functions that allow easier and more direct access to these navigation properties.
To create and use these functions, do the following:
- Right-click the .edmx file in the Solution Explorer, and click Open With XML Editor. This will open the .edmx file in the XML Editor.
- Insert the code in Listing 11-7 just below the <Schema> tag in the conceptual models section of the .edmx file. This defines the functions in the model.
Listing 11-7. Model-Defined Functions for Navigating the Associate Hierarchy
<Function Name="GetProjectManager" ReturnType="EFRecipesModel.ProjectManager">
<Parameter Name="teammember" Type="EFRecipesModel.TeamMember" />
<DefiningExpression>
treat(teammember.Manager.Manager as EFRecipesModel.ProjectManager)
</DefiningExpression>
</Function>
<Function Name="GetSupervisor" ReturnType="EFRecipesModel.Supervisor">
<Parameter Name="teammember" Type="EFRecipesModel.TeamMember" />
<DefiningExpression>
treat(EFRecipesModel.GetProjectManager(teammember).Manager as
EFRecipesModel.Supervisor)
</DefiningExpression>
</Function> - Insert into and query the model using code similar to the pattern shown in Listing 11-8.
Listing 11-8. Using Both eSQL and LINQ to Query the Model
class Program
{
static void Main(string[] args)
{
RunExample();
}
static void RunExample()
{
using (var context = new EFRecipesEntities())
{
var john = new Supervisor { Name = "John Smith" };
var steve = new Supervisor {Name = "Steve Johnson"};
var jill = new ProjectManager { Name = "Jill Masterson",
Manager = john };
var karen = new ProjectManager { Name = "Karen Carns",
Manager = steve };
var bob = new TeamLead { Name = "Bob Richardson", Manager = karen };
var tom = new TeamLead { Name = "Tom Landers", Manager = jill };
var nancy = new TeamMember { Name = "Nancy Jones", Manager = tom };
var stacy = new TeamMember { Name = "Stacy Rutgers",
Manager = bob };
context.Associates.Add(john);
context.Associates.Add(steve);
context.SaveChanges();
}
using (var context = new EFRecipesEntities())
{
Console.WriteLine("Using eSQL...");
var emps = context.Associates.OfType<TeamMember>()
.Where(@"EFRecipesModel.GetProjectManager(it).Name =
@projectManager ||
EFRecipesModel.GetSupervisor(it).Name == @supervisor",
new ObjectParameter("projectManager", "Jill Masterson"),
new ObjectParameter("supervisor", "Steve Johnson"));
Console.WriteLine("Team members that report up to either");
Console.WriteLine("Project Manager Jill Masterson ");
Console.WriteLine("or Supervisor Steve Johnson");
foreach (var emp in emps)
{
Console.WriteLine(" Associate: {0}", emp.Name);
}
}
using (var context = new EFRecipesEntities())
{
Console.WriteLine();
Console.WriteLine("Using LINQ...");
var emps = from e in context.Associates.OfType<TeamMember>()
where MyFunctions.GetProjectManager(e).Name ==
"Jill Masterson" ||
MyFunctions.GetSupervisor(e).Name == "Steve Johnson"
select e;
Console.WriteLine("Team members that report up to either");
Console.WriteLine("Project Manager Jill Masterson ");
Console.WriteLine("or Supervisor Steve Johnson");
foreach (var emp in emps)
{
Console.WriteLine(" Associate: {0}", emp.Name);
}
}
}
}
public class MyFunctions
{
[EdmFunction("EFRecipesModel", "GetProjectManager")]
public static ProjectManager GetProjectManager(TeamMember member)
{
throw new NotSupportedException("Direct calls not supported.");
}
[EdmFunction("EFRecipesModel", "GetSupervisor")]
public static Supervisor GetSupervisor(TeamMember member)
{
throw new NotSupportedException("Direct calls not supported.");
}
}
The output of the code from Listing 11-8 is as follows:
Using eSQL...
Team members that report up to either
Project Manager Jill Masterson
or Supervisor Steve Johnson
Associate: Nancy Jones
Associate: Stacy Rutgers
Using LINQ...
Team members that report up to either
Project Manager Jill Masterson
or Supervisor Steve Johnson
Associate: Nancy Jones
Associate: Stacy Rutgers
How It Works
In the GetSupervisor() function in Listing 11-7, we need to make three hops through the Manager navigation property. The first one gets the team lead from the team member, the second one gets the project manager from the team lead, and the final one gets the supervisor from the project manager. We already created the GetProjectManager() function in Listing 11-7, so we can leverage that function to simplify the implementation of the GetSupervisor() function.
We use the treat() eSQL operator to cast an instance of Associate to its concrete type, which is either ProjectManager or Supervisor. If we didn’t use the treat() operator, Entity Framework would raise an exception complaining that it cannot map the instance of Associate to ProjectManager or Supervisor.
In Listing 11-8, using the GetProjectManager() and GetSupervisor() functions allows us to simplify the code by hiding all of the traversal through the object graph via the Manager navigation property.
Because we are not returning IQueryable<T> from our model-defined function, we didn’t need to provide an implementation of the stubs we require to use these functions in the LINQ query.
11-5. Returning an Anonymous Type from a Model-Defined Function
Problem
You want to create a model-defined function that returns an anonymous type.
Solution
Let’s say that you have a model for hotel reservations like the one shown in Figure 11-5.
Figure 11-5. A model for hotel reservations
You want to retrieve the total number of reservations and the total room revenue for each visitor. Because you will need this information in several places, you want to create a model-defined function that takes in a search parameter and returns a collection of anonymous types containing the summary information for each visitor.
To create and use this model-defined function, do the following:
- Right-click the .edmx file in the Solution Explorer, and click Open With XML Editor. This will open the .edmx file in the XML Editor.
- Insert the code in Listing 11-9 just below the <Schema> tag in the conceptual models section of the .edmx file. This defines the function in the model.
Listing 11-9. The VisitorSummary() Model-Defined Function
<Function Name="VisitorSummary">
<Parameter Name="StartDate" Type="Edm.DateTime" />
<Parameter Name="Days" Type="Edm.Int32" />
<ReturnType>
<CollectionType>
<RowType>
<Property Name="Name" Type="Edm.String" />
<Property Name="TotalReservations" Type="Edm.Int32" />
<Property Name="BusinessEarned" Type="Edm.Decimal" />
</RowType>
</CollectionType>
</ReturnType>
<DefiningExpression>
Select
r.Visitor.Name,
COUNT(r.ReservationId) as TotalReservations,
SUM(r.Cost) as BusinessEarned
from EFRecipesEntities.Reservations as r
where r.ReservationDate between StartDate and
AddDays(StartDate,Days)
group by r.Visitor.Name
</DefiningExpression>
</Function> - Insert into and query the model using code similar to the pattern shown in Listing 11-10.
Listing 11-10. Querying the Model Using the VistorySummary() Model-Defined Function
class Program
{
static void Main(string[] args)
{
RunExample();
}
static void RunExample()
{
using (var context = new EFRecipesEntities())
{
var hotel = new Hotel { Name = "Five Seasons Resort" };
var v1 = new Visitor { Name = "Alex Stevens" };
var v2 = new Visitor { Name = "Joan Hills" };
var r1 = new Reservation { Cost = 79.99M, Hotel = hotel,
ReservationDate = DateTime.Parse("2/19/2010"), Visitor = v1 };
var r2 = new Reservation { Cost = 99.99M, Hotel = hotel,
ReservationDate = DateTime.Parse("2/17/2010"), Visitor = v2 };
var r3 = new Reservation { Cost = 109.99M, Hotel = hotel,
ReservationDate = DateTime.Parse("2/18/2010"), Visitor = v1 };
var r4 = new Reservation { Cost = 89.99M, Hotel = hotel,
ReservationDate = DateTime.Parse("2/17/2010"), Visitor = v2 };
context.Hotels.Add(hotel);
context.SaveChanges();
}
using (var context = new EFRecipesEntities())
{
Console.WriteLine("Using eSQL...");
var esql = @"Select value v from
EFRecipesModel.VisitorSummary(DATETIME'2010-02-16 00:00', 7) as v";
var objectContext = (context as IObjectContextAdapter).ObjectContext;
var visitors = objectContext.CreateQuery<DbDataRecord>(esql);
foreach (var visitor in visitors)
{
Console.WriteLine("{0}, Total Reservations: {1}, Revenue: {2:C}",
visitor["Name"], visitor["TotalReservations"],
visitor["BusinessEarned"]);
}
}
using (var context = new EFRecipesEntities())
{
Console.WriteLine();
Console.WriteLine("Using LINQ...");
var visitors = from v in
context.VisitorSummary(DateTime.Parse("2/16/2010"), 7)
select v;
foreach (var visitor in visitors)
{
Console.WriteLine("{0}, Total Reservations: {1}, Revenue: {2:C}",
visitor["Name"], visitor["TotalReservations"],
visitor["BusinessEarned"]);
}
}
}
}
partial class EFRecipesEntities
{
[EdmFunction("EFRecipesModel", "VisitorSummary")]
public IQueryable<DbDataRecord> VisitorSummary(DateTime StartDate, int Days)
{
return this.QueryProvider.CreateQuery<DbDataRecord>(
Expression.Call(
Expression.Constant(this),
(MethodInfo)MethodInfo.GetCurrentMethod(),
new Expression[] { Expression.Constant(StartDate),
Expression.Constant(Days) }
));
}
}
The output from the code in Listing 11-10 is as follows:
Using eSQL...
Alex Stevens, Total Reservations: 2, Revenue: $189.98
Joan Hills, Total Reservations: 2, Revenue: $189.98
Using LINQ...
Alex Stevens, Total Reservations: 2, Revenue: $189.98
Joan Hills, Total Reservations: 2, Revenue: $189.98
How It Works
In Listing 11-9, for the definition of the VisitorSummary() function, we group the results by visitor, which is the navigation property exposed on the entity. To get the total count of reservations for each visitor, we use the eSQL Count()function. To get the total revenue, we use the Sum() function.
In the function, we shape the results as a collection of rows of three values: Name, TotalReservations, and BusinessEarned. Here we use the <CollectionType> and <RowType> tags to indicate the return type. In CLR terms, this is a collection of DbDataRecords.
To use the function in a LINQ query, we create a CLR method that returns IQueryable<DbDataRecord>. As in the previous recipes, we decorated the method with the EdmFunction()attribute. However, because we are returning an IQueryable<T>, we need to implement the body of the method to include the function call in the expression tree. Furthermore, because we need access to the QueryProvider in our ObjectContext to return an IQueryable<T>, we need to implement this method inside the EFRecipesEntities class.
11-6. Returning a Complex Type from a Model-Defined Function
Problem
You want to return a complex type from a model-defined function.
Solution
Suppose that we have a model for patients and their visits to a local hospital. This model is shown in Figure 11-6.
Figure 11-6. A model for patient visits
You want to create a model-defined function that returns summary information about the patient with their name, the total number of visits, and their accumulated bill. Additionally, you want to filter the results to include only patients over 40 years old.
To create and use the model-defined function, do the following:
- Right-click the designer, and select Add Complex Type.
- Right-click the new complex type in the Model Browser. Rename the type to VisitSummary, and add the following properties:
- Name of type String, not nullable
- TotalVisits of type Int32, not nullable
- TotalCost of type Decimal, not nullable
- Right-click the .edmx file in the Solution Explorer, and click Open With XML Editor. This will open the .edmx file in the XML Editor.
- Insert the code in Listing 11-11 just below the <Schema> tag in the conceptual models section of the .edmx file. This defines the function in the model.
Listing 11-11. The GetVisitSummary() Model-Defined Function
<Function Name="GetVisitSummary" ReturnType="Collection(EFRecipesModel.VisitSummary)">
<DefiningExpression>
select VALUE EFRecipesModel.VisitSummary(pv.Patient.Name,
Count(pv.VisitId),Sum(pv.Cost))
from EFRecipesEntities.PatientVisits as pv
group by pv.Patient.PatientId
</DefiningExpression>
</Function> - Insert into and query the model using code similar to the pattern shown in Listing 11-12.
Listing 11-12. Using eSQL and LINQ with the VisitSummary() Function to Query the Model
class Program
{
static void Main(string[] args)
{
RunExample();
}
static void RunExample()
{
using (var context = new EFRecipesEntities())
{
string hospital = "Oakland General";
var p1 = new Patient { Name = "Robin Rosen", Age = 41 };
var p2 = new Patient { Name = "Alex Jones", Age = 39 };
var p3 = new Patient { Name = "Susan Kirby", Age = 54 };
var v1 = new PatientVisit { Cost = 98.38M, Hospital = hospital,
Patient = p1 };
var v2 = new PatientVisit { Cost = 1122.98M, Hospital = hospital,
Patient = p1 };
var v3 = new PatientVisit { Cost = 2292.72M, Hospital = hospital,
Patient = p2 };
var v4 = new PatientVisit { Cost = 1145.73M, Hospital = hospital,
Patient = p3 };
var v5 = new PatientVisit { Cost = 2891.07M, Hospital = hospital,
Patient = p3 };
context.Patients.Add (p1);
context.Patients.Add (p2);
context.Patients.Add (p3);
context.SaveChanges();
}
using (var context = new EFRecipesEntities())
{
Console.WriteLine("Query using eSQL...");
var esql = @"Select value ps from EFRecipesEntities.Patients
as p join EFRecipesModel.GetVisitSummary()
as ps on p.Name = ps.Name where p.Age > 40";
var objectContext = (context as IObjectContextAdapter).ObjectContext;
var patients = objectContext.CreateQuery<VisitSummary>(esql);
foreach (var patient in patients)
{
Console.WriteLine("{0}, Visits: {1}, Total Bill: {2}",
patient.Name, patient.TotalVisits.ToString(),
patient.TotalCost.ToString("C"));
}
}
using (var context = new EFRecipesEntities())
{
Console.WriteLine();
Console.WriteLine("Query using LINQ...");
var patients = from p in context.Patients
join ps in context.GetVisitSummary() on p.Name equals
ps.Name
where p.Age >= 40
select ps;
foreach (var patient in patients)
{
Console.WriteLine("{0}, Visits: {1}, Total Bill: {2}",
patient.Name, patient.TotalVisits.ToString(),
patient.TotalCost.ToString("C"));
}
}
}
}
partial class EFRecipesEntities
{
[EdmFunction("EFRecipesModel", "GetVisitSummary")]
public IQueryable<VisitSummary> GetVisitSummary()
{
return this.QueryProvider.CreateQuery<VisitSummary>(
Expression.Call(Expression.Constant(this),
(MethodInfo)MethodInfo.GetCurrentMethod()));
}
}
The code in Listing 11-12 produces the following output:
Query using eSQL...
Robin Rosen, Visits: 2, Total Bill: $1,221.36
Susan Kirby, Visits: 2, Total Bill: $4,036.80
Query using LINQ...
Robin Rosen, Visits: 2, Total Bill: $1,221.36
Susan Kirby, Visits: 2, Total Bill: $4,036.80
How It Works
We started by creating the complex type in the model. With the complex type created, we defined the GetVisitSummary() function in Listing 11-11 as returning a collection of our newly created complex type. Notice that the constructor for our complex type takes in parameters in the same order as those defined by our complex type. You might need to double-check in the .edmx file to make sure that the designer created the complex type properties in the order in which you created them interactively.
Because our function returns IQueryable<VisitSummary>, we need to implement the bootstrapping code. Also, because we need to get access to the QueryProvider inside our ObjectContext, we need to implement the method in a partial class of our EFRecipesEntities class, which is our ObjectContext.
You might be wondering when you would return a collection of complex types rather than a collection of anonymous types from a function. If you used the function in a LINQ query, the bootstrapping method would need to return IQueryable<DbDataRecord> for the anonymous type. However, although this collection could not be filtered further, a collection of complex types could be further filtered.
11-7. Returning a Collection of Entity References from a Model-Defined Function
Problem
You want to return a collection of entity references from a model-defined function.
Solution
Let’s say that you have a model, such as the one shown in Figure 11-7, for events and their sponsors. Sponsors provide different levels of financial support for events. Platinum sponsors provide the highest level of financial support.
Figure 11-7. A model for events and their sponsors
You want to create a model-defined function that returns a collection of all the sponsors who are at the Platinum level. Because you need only the entity key information for the sponsor, the function needs to return only a collection of references to the sponsors.
To create and use the model-defined function, do the following:
- Right-click the .edmx file in the Solution Explorer, and click Open With XML Editor. This will open the .edmx file in the XML Editor.
- Insert the code in Listing 11-13 just below the <Schema> tag in the conceptual models section of the .edmx file. This defines the function in the model.
Listing 11-13. The Definition of the PlatinumSponsors() Function
<Function Name="PlatinumSponsors">
<ReturnType>
<CollectionType>
<ReferenceType Type="EFRecipesModel.Sponsor" />
</CollectionType>
</ReturnType>
<DefiningExpression>
select value ref(s)
from EFRecipesEntities.Sponsors as s
where s.SponsorType.Description == 'Platinum'
</DefiningExpression>
</Function> - Insert into and query the model using code similar to the pattern shown in Listing 11-14.
Listing 11-14. Using eSQL and Our PlatinumSponsors() Function to Find All Events with Platinum-Level Sponsors
class Program
{
static void Main(string[] args)
{
RunExample();
}
static void RunExample()
{
using (var context = new EFRecipesEntities())
{
var platst = new SponsorType { Description = "Platinum" };
var goldst = new SponsorType { Description = "Gold" };
var sp1 = new Sponsor
{
Name = "Rex's Auto Body Shop",
SponsorType = goldst
};
var sp2 = new Sponsor
{
Name = "Midtown Eye Care Center",
SponsorType = platst
};
var sp3 = new Sponsor
{
Name = "Tri-Cities Ford",
SponsorType = platst
};
var ev1 = new Event { Name = "OctoberFest", Sponsor = sp1 };
var ev2 = new Event { Name = "Concerts in the Park", Sponsor = sp2 };
var ev3 = new Event { Name = "11th Street Art Festival", Sponsor = sp3 };
context.Events.Add(ev1);
context.Events.Add(ev2);
context.Events.Add(ev3);
context.SaveChanges();
}
using (var context = new EFRecipesEntities())
{
Console.WriteLine("Events with Platinum Sponsors");
Console.WriteLine("=============================");
var esql = @"select value e from EFRecipesEntities.Events as e where
ref(e.Sponsor) in (EFRecipesModel.PlatinumSponsors())";
var objectContext = (context as IObjectContextAdapter).ObjectContext;
var events = objectContext.CreateQuery<Event>(esql);
foreach (var ev in events)
{
Console.WriteLine(ev.Name);
}
}
}
}
The output of the code in Listing 11-14 is as follows:
Events with Platinum Sponsors
=============================
Concerts in the Park
11th Street Art Festival
How It Works
The <ReferenceType> element in the conceptual model denotes a reference to an entity type. This means that we are returning a reference to an entity, not the complete entity. Our model-defined function returns a collection of references to Platinum-level sponsors. To illustrate using our function, we created an eSQL expression in Listing 11-14 to get all of the events with Platinum-level sponsors. There are, of course, lots of different ways to get the events sponsored by Platinum-level sponsors, but by encapsulating the collection of Platinum-level sponsors in our model-defined function, we introduce a bit of code reusability.
We didn’t show a corresponding use in a LINQ query because the bootstrapping code would need to return an IQueryable<EntityKey>, which is fine, but a subsequent Contains clause would not work because the result is not strongly typed.
11-8. Using Canonical Functions in eSQL
Problem
You want to call a canonical function in your eSQL query. A canonical function is an eSQL function that is natively supported by all data providers. Examples include Sum(), Count(), and Avg().
Solution
Suppose that we have a model for customers and their orders, as shown in Figure 11-8.
Figure 11-8. A model for customers and their orders
You want to retrieve the number of orders and the total purchase amount made by customers who have placed orders above the average order.
To create and use this query, follow the pattern shown in Listing 11-15.
Listing 11-15. Querying the Model in eSQL Using the Sum(), Count(), and Avg() Functions
class Program
{
static void Main(string[] args)
{
RunExample();
}
static void RunExample()
{
using (var context = new EFRecipesEntities())
{
var c1 = new Customer { Name = "Jill Masters", City = "Raytown" };
var c2 = new Customer { Name = "Bob Meyers", City = "Austin" };
var c3 = new Customer { Name = "Robin Rosen", City = "Dallas" };
var o1 = new Order { OrderAmount = 12.99M, Customer = c1 };
var o2 = new Order { OrderAmount = 99.39M, Customer = c2 };
var o3 = new Order { OrderAmount = 101.29M, Customer = c3 };
context.Orders.Add(o1);
context.Orders.Add(o2);
context.Orders.Add(o3);
context.SaveChanges();
}
using (var context = new EFRecipesEntities())
{
Console.WriteLine("Customers with above average total purchases");
var esql = @"select o.Customer.Name, count(o.OrderId) as TotalOrders,
Sum(o.OrderAmount) as TotalPurchases
from EFRecipesEntities.Orders as o
where o.OrderAmount >
anyelement(select value Avg(o.OrderAmount) from
EFRecipesEntities.Orders as o)
group by o.Customer.Name";
var objectContext = (context as IObjectContextAdapter).ObjectContext;
var summary = objectContext.CreateQuery<DbDataRecord>(esql);
foreach (var item in summary)
{
Console.WriteLine(" {0}, Total Orders: {1}, Total: {2:C}",
item["Name"], item["TotalOrders"], item["TotalPurchases"]);
}
}
}
}
The output of the code in Listing 11-15 is as follows:
Customers with above average total purchases
Bob Meyers, Total Orders: 1, Total: $99.39
Robin Rosen, Total Orders: 1, Total: $101.29
How It Works
In this recipe, we used the canonical functions Count(),Sum(),and Avg(). These functions are independent of the data store, which means that they are portable and return types in the EDM space rather than data store-specific or CLR types.
11-9. Using Canonical Functions in LINQ
Problem
You want to use canonical functions in a LINQ query.
Solution
Let’s say that you have a model for movie rentals like the one shown in Figure 11-9. The MovieRental entity holds the date that the movie was rented and the date that it was returned, as well as any late fees that have been accumulated.
Figure 11-9. The MovieRental entity that has the dates for a rental period along with any late fees
You want to retrieve all of the movies that were returned more than 10 days after they were rented. These are the late movies.
To create and use this query, follow the pattern shown in Listing 11-16.
Listing 11-16. Retrieving the Late Movies using the DateDiff() Function
class Program
{
static void Main(string[] args)
{
RunExample();
}
static void RunExample()
{
using (var context = new EFRecipesEntities())
{
var mr1 = new MovieRental
{
Title = "A Day in the Life",
RentalDate = DateTime.Parse("2/19/2013"),
ReturnedDate = DateTime.Parse("3/4/2013"),
LateFees = 3M
};
var mr2 = new MovieRental
{
Title = "The Shortest Yard",
RentalDate = DateTime.Parse("3/15/2013"),
ReturnedDate = DateTime.Parse("3/20/2013"),
LateFees = 0M
};
var mr3 = new MovieRental
{
Title = "Jim's Story",
RentalDate = DateTime.Parse("3/2/2013"),
ReturnedDate = DateTime.Parse("3/19/2013"),
LateFees = 3M
};
context.MovieRentals.Add(mr1);
context.MovieRentals.Add(mr2);
context.MovieRentals.Add(mr3);
context.SaveChanges();
}
using (var context = new EFRecipesEntities())
{
Console.WriteLine("Movie rentals late returns");
Console.WriteLine("==========================");
var late = from r in context.MovieRentals
where DbFunctions.DiffDays(r.RentalDate, r.ReturnedDate) > 10
select r;
foreach (var rental in late)
{
Console.WriteLine("{0} was {1} days late, fee: {2}", rental.Title,
(rental.ReturnedDate - rental.RentalDate).Days - 10,
rental.LateFees.ToString("C"));
}
}
}
}
The output of the code in Listing 11-16 is the following:
Movie rentals late returns
==========================
A Day in the Life was 3 days late, fee: $3.00
Jim's Story was 7 days late, fee: $3.00
How It Works
Canonical functions, which are defined in Entity Framework, are data source-agnostic and supported by all data providers. The types returned from canonical functions are defined in terms of types from the Entity Data Model.
In this recipe, we used the DiffDays() function to calculate the number of days between the start and end of the rental period. Because DiffDays() is a canonical function, it will be implemented by all providers.
Best Practice
You may be asking yourself, “When should I use EntityFunctions?” Entity Framework provides translations for some expressions into the canonical functions, but the translation is limited. Not every CLR method will translate to the corresponding canonical function.
Here’s the best practice. If there is a translation available, use it. It makes the code easier to read. If there is no translation available, use the EntityFunction class to call the canonical function explicitly, as in the following code snippet:
var laterentals = from r in context.MovieRentals
where (r.ReturnedDate - r.RentalDate).Days > 10
select r;
does not translate to the Canonical Function, so you should use,
var laterentals = from r in context.MovieRentals
where EntityFunctions.DiffDays(r.RentalDate,
r.ReturnedDate) > 10
select r;
11-10. Calling Database Functions in eSQL
Problem
You want to call a database function in an eSQL statement.
Solution
Let’s say that you have an eCommerce website, and you need to find all of the customers within a certain distance of a given ZIP code. Your model might look like the one shown in Figure 11-10.
Figure 11-10. WebCustomer and Zip entities in a model
We’ll need to pull out some basic math functions to get this to work. Unfortunately, Entity Framework does not have the canonical functions we need, so we’ll have to use the functions available in the data store.
Use the pattern in Listing 11-17 to call the database functions from an eSQL expression.
Listing 11-17. Using Database Functions to Determine the Distance between a Customer and a Given Zip Code
class Program
{
static void Main(string[] args)
{
RunExample();
}
static void RunExample()
{
using (var context = new EFRecipesEntities())
{
var c1 = new WebCustomer { Name = "Alex Stevens", Zip = "76039" };
var c2 = new WebCustomer { Name = "Janis Jones", Zip = "76040" };
var c3 = new WebCustomer { Name = "Cathy Robins", Zip = "76111" };
context.Zips.Add(new Zip
{
Latitude = 32.834298M,
Longitude = -32.834298M,
ZipCode = "76039"
});
context.Zips.Add(new Zip
{
Latitude = 32.835298M,
Longitude = -32.834798M,
ZipCode = "76040"
});
context.Zips.Add(new Zip
{
Latitude = 33.834298M,
Longitude = -31.834298M,
ZipCode = "76111"
});
context.WebCustomers.Add(c1);
context.WebCustomers.Add(c2);
context.WebCustomers.Add(c3);
context.SaveChanges();
}
using (var context = new EFRecipesEntities())
{
string esql = @"select value c
from EFRecipesEntities.WebCustomers as c
join
(SELECT z.ZipCode,
3958.75 * (SqlServer.Atan(SqlServer.Sqrt(1 -
SqlServer.power(((SqlServer.Sin(t2.Latitude/57.2958M) *
SqlServer.Sin(z.Latitude/57.2958M)) +
(SqlServer.Cos(t2.Latitude/57.2958M) *
SqlServer.Cos(z.Latitude/57.2958M) *
SqlServer.Cos((z.Longitude/57.2958M) -
(t2.Longitude/57.2958M)))), 2)) /(
((SqlServer.Sin(t2.Latitude/57.2958M) *
SqlServer.Sin(z.Latitude/57.2958M)) +
(SqlServer.Cos(t2.Latitude/57.2958M) *
SqlServer.Cos(z.Latitude/57.2958M) *
SqlServer.Cos((z.Longitude/57.2958M) -
(t2.Longitude/57.2958M))))))
) as DistanceInMiles
FROM EFRecipesEntities.Zips AS z join
(select top(1) z2.Latitude as Latitude,z2.Longitude as
Longitude
from EFRecipesEntities.Zips as z2
where z2.ZipCode = @Zip
) as t2 on 1 = 1
) as matchingzips on matchingzips.ZipCode = c.Zip
where matchingzips.DistanceInMiles <= @RadiusInMiles";
var objectContext = (context as IObjectContextAdapter).ObjectContext;
var custs = objectContext.CreateQuery<WebCustomer>(esql,
new ObjectParameter("Zip", "76039"),
new ObjectParameter("RadiusInMiles", 5));
Console.WriteLine("Customers within 5 miles of 76039");
foreach (var cust in custs)
{
Console.WriteLine("Customer: {0}", cust.Name);
}
}
}
}
The output of the code in Listing 11-17 is as follows:
Customers within 5 miles of 76039
Customer: Alex Stevens
Customer: Janis Jones
How It Works
Okay, the eSQL is a little complex, but the complexity is because we’re calling a bunch of database functions. Using the database functions in eSQL is fairly simple. These functions are available in the SqlServer namespace. Not all database functions are available in eSQL, so check the current Microsoft documentation to get a complete list. These functions are available only for SQL Server database.
In this example, the Zip entity has the latitude and longitude for each ZIP code. These values represent the geographic location of the center of the ZIP code. To calculate the distance between two ZIP codes involves a bit of math. Luckily, the database side provides the necessary functions to do the calculation.
11-11. Calling Database Functions in LINQ
Problem
You want to call a database function in a LINQ query.
Solution
Let’s say that you have an Appointment entity in your model, and you want to query for all of the appointments that you have on a given day of the week. The Appointment entity might look like the one shown in Figure 11-11.
Figure 11-11. An Appointment entity with the start and end times for appointments
If we want to find all of the appointments for Thursday, we can’t use the CLR enum DayOfWeek.Thursday to compare with the StartsAt property in a where clause because this does not translate to a data store statement. We need to use the pattern shown in Listing 11-18.
Listing 11-18. Using a Database Function in a LINQ Query
class Program
{
static void Main(string[] args)
{
RunExample();
}
static void RunExample()
{
using (var context = new EFRecipesEntities())
{
var app1 = new Appointment
{
StartsAt = DateTime.Parse("7/23/2013 14:00"),
GoesTo = DateTime.Parse("7/23/2013 15:00")
};
var app2 = new Appointment
{
StartsAt = DateTime.Parse("7/24/2013 9:00"),
GoesTo = DateTime.Parse("7/24/2013 11:00")
};
var app3 = new Appointment
{
StartsAt = DateTime.Parse("7/24/2013 13:00"),
GoesTo = DateTime.Parse("7/23/2013 15:00")
};
context.Appointments.Add(app1);
context.Appointments.Add(app2);
context.Appointments.Add(app3);
context.SaveChanges();
}
using (var context = new EFRecipesEntities())
{
var apps = from a in context.Appointments
where SqlFunctions.DatePart("WEEKDAY", a.StartsAt) == 4
select a;
Console.WriteLine("Appointments for Thursday");
Console.WriteLine("=========================");
foreach (var appointment in apps)
{
Console.WriteLine("Appointment from {0} to {1}",
appointment.StartsAt.ToShortTimeString(),
appointment.GoesTo.ToShortTimeString());
}
}
}
}
The output of the code in Listing 11-18 is as follows:
Appointments for Thursday
=========================
Appointment from 9:00 AM to 11:00 AM
Appointment from 1:00 PM to 3:00 PM
How It Works
Database functions are available for use in both eSQL and LINQ queries. These functions are exposed via methods in the SqlFunctions class. Because these functions execute on the database side, the behavior you get might differ slightly from what you would expect on the .NET side. For example, DayOfWeek.Thursday evaluates to 4 on the .NET side. On the database side, Thursday is the fifth day of the week, so we check for a value of 5.
As with database functions in eSQL, not all database functions are available for LINQ queries. Check the current documentation from Microsoft for a complete list of the available functions.
11-12. Defining Built-in Functions
Problem
You want to define a built-in function for use in an eSQL or LINQ query.
Solution
Let’s say that you want to use the IsNull function in the database, but this function is not currently exposed by Entity Framework for either eSQL or LINQ. Suppose we have a WebProduct entity in our model like the one shown in Figure 11-12.
Figure 11-12. A WebProduct entity in our model
To expose this database function for your queries, do the following:
- Right-click the .edmx file in the Solution Explorer, and click Open With XML Editor. This will open the .edmx file in the XML Editor.
- Insert the code in Listing 11-19 just below the <Schema> tag in the storage models section of the .edmx file. This defines the functions in the storage layer.
Listing 11-19. Defining Our Function in the Storage Layer
<Function Name="ISNULL" ReturnType="varchar" BuiltIn="true" Schema="dbo">
<Parameter Name="expr1" Type="varchar" Mode="In" />
<Parameter Name="expr2" Type="varchar" Mode="In" />
</Function> - Insert into and query the model using code similar to the pattern shown in Listing 11-19.
Listing 11-20. Using the ISNULL() Function in an eSQL and LINQ Query
class Program
{
static void Main(string[] args)
{
RunExample();
}
static void RunExample()
{
using (var context = new EFRecipesEntities())
{
var w1 = new WebProduct
{
Name = "Camping Tent",
Description = "Family Camping Tent, Color Green"
};
var w2 = new WebProduct { Name = "Chemical Light" };
var w3 = new WebProduct
{
Name = "Ground Cover",
Description = "Blue ground cover"
};
context.WebProducts.Add(w1);
context.WebProducts.Add(w2);
context.WebProducts.Add(w3);
context.SaveChanges();
}
using (var context = new EFRecipesEntities())
{
Console.WriteLine("Query using eSQL...");
var esql = @"select value
EFRecipesModel.Store.ISNULL(p.Description,p.Name)
from EFRecipesEntities.WebProducts as p";
var objectContext = (context as IObjectContextAdapter).ObjectContext;
var prods = objectContext.CreateQuery<string>(esql);
foreach (var prod in prods)
{
Console.WriteLine("Product Description: {0}", prod);
}
}
using (var context = new EFRecipesEntities())
{
Console.WriteLine();
Console.WriteLine("Query using LINQ...");
var prods = from p in context.WebProducts
select BuiltinFunctions.ISNULL(p.Description, p.Name);
foreach (var prod in prods)
{
Console.WriteLine(prod);
}
}
}
}
public class BuiltinFunctions
{
[EdmFunction("EFRecipesModel.Store", "ISNULL")]
public static string ISNULL(string check_expression, string replacementvalue)
{
throw new NotSupportedException("Direct calls are not supported.");
}
}
The output from the code in Listing 11-20 is as follows:
Query using eSQL...
Product Description: Family Camping Tent, Color Green
Product Description: Chemical Light
Product Description: Blue ground cover
Query using LINQ...
Family Camping Tent, Color Green
Chemical Light
Blue ground cover
How It Works
In the definition of the ISNULL() function in Listing 11-18, we need to match the name of the database function with our function’s name. Both have to be the same in spelling but not in case.
We defined the function not in the conceptual layer, as in previous recipes in this chapter, but in the store layer. This function is already available in the database; we are simply surfacing it in the store layer for our use.
When we use the function in the eSQL statement, we need to fully qualify the namespace for the function. Here that fully qualified name is EFRecipesModel.Store.ISNULL().
To use the function in a LINQ query, we need to create the bootstrapping method. We are not returning an IQueryable<T>, so no implementation of the method is required.