Entity Framework 4 in action Reading Notes-Chapter 4: querying using LINQ to entities: Using Functions

ArticleDirectory

• 4.7.1 Standard Functions
• 4.7.2 database functions

4.7 use functions

You can use a function to extend the query by using LINQ to entities. There are four types of functions that can be applied:

Canonicalized functions-a set of predefined functions not provided by LINQ to entities themselves.

Database functions-a set of predefined SQL server functions.

Functions defined in the model-User-Defined Entity SQL functions stored in EDM.

Custom database functions-User-Defined database functions that can be used in queries.

In this part, only the normative functions and database functions are involved. The other two functions deserve more explanations and require more ef knowledge. We will discuss them in Chapter 11.

4.7.1 Standard Functions

Standardized functions are an effective way to perform database operations. For example, there are functions that perform mathematical operations and date comparison. In ef1.0, canonicalized functions can only be executed in Entity SQL queries, but now they have been encapsulated using the CLR method, so that they can be called by LINQ to entities. This change extends the functionality of LINQ to entities by reusing existing features.

Let's look at an example. Our customers want an order list for more than five days of delivery. It is quite simple to query using LINQ:

VaRResult =FromOInCTX. OrdersWhereO. orderdate. adddays (5) <O. actualshippingdateSelectO;
 

The query is compiled, but an exception occurs during runtime, because the Translation Engine cannot convert the adddays method into an appropriate SQL statement.

Canonicalized functions use the diffdays method to fill this vulnerability. This method accepts two dates as parameters and returns the number of days between them. This method and other normative functions can be found in the entityfunctions class in the system. Data. Objects namespace.

 
VaRResult =FromOInCTX. OrdersWhereEntityfunctions. Diffdays (O. orderdate, O. actualshippingdate)> 5SelectO;
 

Another example uses mathematical functions. All operations can be performed using methods in the system. Math class. However, just like the datetime method, the SQL Translation Engine does not support the math method. You can use pow, round, ceiling, and floor in the entityfunctions class as an alternative.

These functions can be called anywhere in the query, not just the WHERE clause. For example, you can use the ABS function in the select clause to obtain the absolute value of a number.

A standard function is a function unrelated to the database of an Entity SQL statement. But in many casesCodeSpecifying a specific database is not a problem because you know that you will not change the database you are using. In this case, you can call functions of a specific database to better use this platform.

4.7.2 database functions

Each database has its own function set. Some functions are interconnected in different RDBMS systems, such as ABS, ltrim, and rtrim. They can all be called through LINQ to entities or object functions. Other functions are unique or have different features in each database.

Fortunately, you can also call these functions. EF has many functions specified to SQL server in the sqlfunctions class in the system. Data. Objects. sqlclient namespace. Checksum, charindex, cos, getdate, and Rand are examples of these useful functions.

Apart from the standard functions and database functions, there is no difference between them. They are called as static methods and can be called anywhere in the query. The following code uses a database function to display orders that have been delivered within five days.

VaRResult =FromOInCTX. OrdersWhereSqlfunctions. Datediff ("D", O. orderdate, O. actualshippingdate)> 5SelectO;
 

By using database functions, you can work with your code to a specific database (in this example, SQL Server ). This is not always a good idea, because if you change the database, you must change your code. But if you know that you will not change your applicationProgramYou can use database functions for databases. Be sure to carefully consider whether to use these functions.

Using database-specific functions is one of the two ways to work with your database to a database platform. Another option is to embed SQL queries in the code. Even if SQL is a standard language, queries are often dependent on the characteristics of a specific database. Therefore, you must specify your code to a specific database.