Entity Framework 6 Recipes 2nd Edition (11-12), entityrecipes
11-12. Define built-in functions
Problem
You want to define a built-in function used in eSQL and LINQ queries.
Solution
We want to use the IsNull function in the database, but EF has not released this function for eSQL or LINQ. Suppose we already have a WebProduct entity model, as shown in Figure 11-12.
Figure 11-12.A WebProduct entity in our model
Release functions for our queries:
1. In Solution Explorer, right-click the. edmx file and open the idea XML editor.
2. Insert the code shown in Listing 11-19 under the <Schema> label in the storage model (storage models) section of the. edmx file. Then, the function is defined at 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>
3. The code for inserting and querying a model is 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 ())
{
Context. Database. ExecuteSqlCommand ("delete from chapter11.webproduct ");
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 CILS are not supported .");
}
}
The output result of the above Listing 11-20 code 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 does it work?
For the ISNULL () function definition shown in Listing 11-18, the function name must be the same as the function name in the database (case-insensitive). This time, unlike the definition of the function in the previous section of this chapter, is in the data storage layer. this function is available in the database. We just define it at the storage layer. when using this function in an eSQL statement, you must use the complete namespace. (EFRecipesModel. store. ISNULL ()). when using this function in a LINQ query, you need to create a bootstrap method. Because the IQueryable <T> type is not returned, you do not need to implement the method body.
Appendix: script file of the database used in the Creation example