Entity Framework 6 Recipes 2nd Edition (11-12), entityrecipes

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