Entity SQL First entry

Entity SQL is the SQL class language provided by the ADO Entity Framework to support the Entity Data Model (EDM). Entity SQL can be used for object queries and queries that are executed using the EntityClient provider.

> Keywords

Value keyword

ESQL provides a SELECT VALUE clause to skip implicit row constructs. Only one item can be specified in the SELECT VALUE clause. When you use such clauses, the row wrapper is not constructed for the items in the SELECT clause, and you can generate a collection of the shapes you want, for example: SELECT VALUE it from northwindentities.customers as it

It key words

It appears in ESQL, and the alias default value "It" of the query object is changed to another string, for example:
"Select VALUE it from Northwindentities.customers as it".

> Notes:

An Entity SQL query can contain comments. The comment lines start with two dashes (--).
"Select VALUE it from Northwindentities.customers as it – this a comment"

> select query

For example:
SELECT VALUE it from northwindentities.customers as it

> Parameters

Parameters are variables defined outside esql, each parameter has a name and type, the parameter name is defined in the query expression, and prefixed with the @ symbol. For example:
Select VALUE C from Northwindentities.customers as C where [email protected]

> Aggregation

Enity SQL does not support *, so ESQL does not support count (*), but instead uses count (0), for example:
Select count (0) from Northwindentities.customers

> Paging Skip/limit

You can perform physical paging by using the SKIP and LIMIT sub-clauses in the ORDER by clause. To perform physical paging in a deterministic manner, you should use SKIP and LIMIT. If you just want to limit the number of rows in the results in a non-deterministic way, you should use TOP. TOP and Skip/limit are mutually exclusive.
Using Skip/limit paging, the ESQL code is as follows:
Select value C from northwindentities.customers as C order by C.customerid Skip 0 Limit 10

> TOP

The SELECT clause can have an optional TOP sub-clause after the optional all/distinct modifier. The TOP sub-clause specifies that only the first set of rows will be returned in the query results. The ESQL code is as follows:
Select Top (C.customerid) from Northwindentities.customers as C order by C.customerid

> Null handling

Null literals are compatible with any type in the Entity SQL type system, and you can use cast for type conversions, for example:
Select cast (C.region as String) from Northwindentities.customers as C order by C.customerid limit 10
Where nvarchar can be string, numeric types can be converted to Int32, and other types of conversions are similar. If the conversion cannot be completed, the exception is reported. There are also ways to deal with treat.

> Identifiers

Entity SQL provides two types of identifiers: simple identifiers and quoted identifiers
Simple identifier: A simple identifier in Entity SQL is a sequence of alphanumeric and underscore characters. The first character of an identifier must be an alphabetic character (A-Z or a-Z).

Quoted identifiers: Quoted identifiers are any sequence of characters enclosed in square brackets ([]). With the Chinese part, use square brackets to include it, otherwise the following exception message will be reported: "Simple identifier" Chinese "can only contain basic Latin characters." To use Unicode characters, use the escape identifier "

The correct code is as follows:
Select C.customerid as [Chinese characters] from Northwindentities.customers as C order by C.customerid Skip 0 Limit 10

> ROW

eSQL can use row to build records of anonymous struct types. For example:
SELECT VALUE Row (P.productid as productid,p.productname as ProductName) from Northwindentities.products as P order by p.pr Oductid LIMIT 10

> Key

Extracts the key of a reference or entity expression. The following ESQL statement returns the primary key of the Customer table directly:
String esql = "Select Value key (c) from Northwindentities.customers as C order by C.customerid LIMIT 10"

> Createref/ref/deref

Createref creates a reference to an entity in the entity set.
Ref returns a reference to an entity instance, which can then be used as an entity to access its properties, with the ESQL statement as follows:
SELECT ref (c). CustomerID from Northwindentities.customers as C order by C.customerid LIMIT 10
The deref operator dereference a reference value and produces the result of that dereference.

> Case statement:

String esql = "Using Sqlserver;select case when Len (Trim (C.customerid)) ==0 and true else false end from NorthwindEntities . Customers as C order by C.customerid limit 10 ";

> Operators

The operators supported by ESQL are: Plus +, minus-, multiply *, divide/, modulus%,-minus sign. The ESQL statements are as follows:
Select 100/2 as OP from Northwindentities.customers as C order by C.customerid limit 10

> Comparison Operators

The comparison operators supported by ESQL are: =,>,>=,is [not] null,<,[not] between,!=,<>,[not) like. The ESQL statements are as follows:
Select Value p from northwindentities.products as p where p.unitprice > order by P.productid limit 10

> Logical operators

The logical operators supported by ESQL are: and (&&), not (!), or (| |). The ESQL statements are as follows:
Select Value p from northwindentities.products as P where P.unitprice > p.unitprice<100 ORDER by P.productid Limit 10
Or
Select Value p from northwindentities.products as P where p.unitprice > && p.unitprice<100 ORDER by P.pro Ductid Limit 10

> String Join Operators

The plus sign (+) is the only operator in Entity SQL that can concatenate strings together. The ESQL statements are as follows:
Select C.customerid + c.contactname from Northwindentities.customers as C order by C.customerid limit 10

> Nested queries

In Entity SQL, nested queries must be enclosed in parentheses, and the order of nested queries will not be preserved
Select C1. CustomerID from (select Value C from Northwindentities.customers as C order by C.customerid limit) as C1

> Date-time functions

The date-time functions provided by ESQL are: Currentdatetime () Gets the date and time of the current server, Month,day,year,second, Minute, hour, and so on. For example:
Select Currentdatetime () from Northwindentities.customers as C order by C.customerid limit 10

> String Functions

The string functions provided by ESQL are: Concat,indexof,left,length,ltrim,replace,reverse,rtrim,substring,trim,tolower,toupper. For example:
Select Reverse (p.productname) as ProductName from Northwindentities.products as P order by P.productid limit 10

> GUID

ESQL provides the NewGuid () function, which produces a new GUID. For example:
Select NewGuid () from Northwindentities.customers as C order by C.customerid limit 10

> Math functions:

Abs,ceiling,floor,round

> Statistical functions:

Avg,bigcount,count,max,min,stdev,sum

> Bit calculation function

If null input is provided, these functions return NULL. The return type of these functions is the same as the parameter type. If the function takes more than one parameter, the arguments must have the same type. To perform bitwise operations on different types, you need to explicitly cast to the same type.
Bitwiseand,bitwisenot,bitwiseor,bitwisexor

> Namespaces

Entity SQL introduces namespaces to avoid name collisions with global identifiers such as type names, entity sets, functions, and so on. Namespace support in Entity SQL is similar to namespace support in the. NET Framework.
Entity SQL provides two forms of USING clauses: Qualifying namespaces (where shorter aliases are provided to represent namespaces) and unqualified namespaces, as shown in the following example:
USING System.Data;
USING tsql = System.Data;

For example:
String esql = "using System; Select CAST (P.unitprice as Int32) from Northwindentities.products as P order by P.productid limit 10 ";
String esql = "using system;using SQL Server; Select (CAST (P.unitprice as Int32)), Sqlserver.ltrim (P.productname) as Namelen from northwindentities.products as P order by P.productid limit 10 ";

Finally, let's briefly describe some of the differences between ESQL and T-sql:

All column references in > Entity SQL must be qualified with a table alias.

> ESQL does not support Any,all qualified operators and * operations

> Entity SQL currently does not provide support for DML statements (INSERT, UPDATE, delete).

> The current version of Entity SQL does not provide support for DDL.

Entity SQL First entry