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

Source: Internet
Author: User

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

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.