EntityDAC

LINQ Query Syntax

Common query syntax.

count = integer value
index = integer value
  
query ::=
  query-result
    [ {    .Union(query)
        |  .Concat(query)
        |  .Except(query)
        |  .Intersect(query) } ]
  
query-result ::=
  query-sequence
  [ ( (  .Skip(count) 
         .Take(count) )
      |  .ElementAt(index) 
      |  .First ) ]
  |
  [ (    .Any [ (condition) ]
      |  .All(condition) ) ]
  |
  [ (    .Count
      |  .Max
      |  .Min
      |  .Sum
      |  .Average
      |  .Distinct ) ]
  
query-sequence ::=
  [Linq.]
      From(range-variable)
  [ { From(range-variable) } ]
  [ {    .Join(range-variable) [ .On(condition) ]
      |  .LeftJoin(range-variable).On(condition)
      |  .RightJoin(range-variable).On(condition)
      |  .FullJoin(range-variable).On(condition) } ]
  [   .Where(condition) ]
  [ { .GroupBy(group-expression) } ]
  [ { .OrderBy(order-expression) } ]
  [ { .OrderByDescending(order-expression) } ]
  [ { .ThenBy(order-expression) } ]
  [ { .ThenByDescending(order-expression) } ]
      .Select [ (select-expression) ]
     

Query clause arguments.

  
Pattern = string value
  
attribute ::=
    entity-attribute 
  | reference-attribute
  | collection-entity-attribute
  
operand ::=
    attribute
  | string value
  | number value
  | (expression)
  | null (* only as a right operand in "=" and "<>" expressions *)
  
expression ::=
    -operand
  | operand + operand
  | operand - operand
  | operand * operand
  | operand / operand
  | operand mod operand
  | operand div operand
  | operand shl operand
  | operand shr operand
  | operand = operand
  | operand <> operand
  | operand > operand
  | operand < operand
  | operand >= operand
  | operand <= operand
  | operand or operand
  | operand and operand
  | operand xor operand
  | operand not operand
  | trunc(operand)
  | round(operand)
  | attribute.Contains(pattern)
  
condition = expression
group-expression = expression
  
order-expression ::=
    expression
  | [expression, {expression}]
  
select-expression ::=
    expression
  | [expression, {expression}]
  

Query clauses.

  

From

  
[Linq.]From(range-variable) [ .From(range-variable) ]
  

The clause defines the data source for the further query constructing and introduces a range variable, that should be used for making further clauses arguments.

From is a required clause. Every query statement begins with a From clause. The initial From clause can be followed by zero or more From clauses for combining multiple data sources. In this case, the sources are combined with CROSS JOIN.

The required clause argument is a previously declared and initialized range variable (see. Range variables, references and collections).

Sample:

  Linq.From(Emp)
  Linq.From(Emp).From(Dept)
  

Join, LeftJoin, RightJoin, FullJoin, On

  
[ {    .Join(range-variable) [ .On(condition) ]
    |  .LeftJoin(range-variable).On(condition)
    |  .RightJoin(range-variable).On(condition)
    |  .FullJoin(range-variable).On(condition) } ]

Join clauses correlate new data source with the source of the preceding clause. The On clause determines the matching criterion of elements of both sources.

A join clause is an optional clause. It can be placed either after a From clause or after other join clause. Using On with Join is optional, whereas using it with  LeftJoin, RightJoin, FullJoin is required.

Join types created by clauses:

The required join clause argument is a previously declared and initialized range variable (see. Range variables, references and collections).

The required On clause argument is a logical expression that defines the correspondence between joined sources (like "equals" in LINQ).

Sample:

Linq.From(Emp)
    .Join(Dept)
 
Linq.From(Emp)
    .Join(Dept).On(Emp['DeptNo'] = Dept['DeptNo'])
 
Linq.From(Emp)
    .LeftJoin(Dept).On(Emp['DeptNo'] = Dept['DeptNo'])

Where

.Where(condition)

The clause defines a filter that excludes items from the result of the preceding clauses.

Where is an optional clause. It can be placed immediately after a source clause (From or Join).

The required Where clause argument is a logical expression that defines the condition which each of result elements must conform.

Sample:

Linq.From(Emp)
    .Where(Emp['Sal'] > 1000)

GroupBy

[ { .GroupBy(group-expression) } ]

The clause groups the elements of a result of the preceding clauses according to a specified expression.

GroupBy is an optional clause.  It can be placed after a source clause (From or Join) or after a Where clause The required GroupBy clause argument is an expression that defines the grouping criteria.

Sample:

Linq.From(Emp)
    .GroupBy(Emp['DeptNo'])

OrderBy, OrderByDescending, ThenBy, ThenByDescending

[ { .OrderBy(order-expression) } ]
[ { .OrderByDescending(order-expression) } ]
[ { .ThenBy(order-expression) } ]
[ { .ThenByDescending(order-expression) } ]

The clauses specify an ordering for the result sequence.

An order clause is an optional clause. It can be placed after a source clause (From or Join) or after a limiting clause (Where or GroupBy).

OrderBy sets an ascending sorting of the result elements, whereas OrderByDescending sets a descending sorting. ThenBy and ThenByDescending clauses are complete analogues of OrderBy and OrderByDescending accordingly, and introduced only for compatibility with classical IEnumerable extension methods. To allow multiple sort criteria, any number of order operators can be applied after each other.

The required order clause argument is an expression that defines a sort criteria of result elements. The argument also can be specified as an array of expressions to allow multiple sort criteria.

Sample:

Linq.From(Emp)
    .OrderBy(Emp['DeptNo'])
    .ThenBy([Emp['Sal'], Emp['Comm']])

Select

.Select [ (select-expression) ]

The clauses populates members of the query result sequence.

Select is a required clause. Every query statement must contain a Select clause.

The optional Select clause argument is an expression or array of expressions that specifies the data fields of the result sequence element. If no argument specified, the result element consists of all data fields of all query sources specified in From and join clauses.

Sample:

Linq.From(Emp)
    .Select
 
Linq.From(Emp)
    .Select(Emp['EName'])
 
Linq.From(Emp)
    .Select([Emp['EName'], Emp.Reference['Dept']['DName']])

Result fields naming. The As clause.

field-name = string value
attribute.As(field-name)
TExpression(operand).As(field-name)

The clause defines the name of the result element field.

The As clause is not a LINQ query clause itself, but it is closely related to the Select clause, as it allows to control names of the result sequence fields.

Commonly, if no explicit name for a result field is specified, the field obtains its name automatically corresponding to the following rules:

In order to specify the field name manually, the As clause has to be used. The use of As can be implemented in two ways:

Sample:

Linq.From(Emp)
    .Select([Emp['EName'].As('Name'),
    TExpression(Emp['EmpNo'] + 1).As('Id')])

Union, Concat, Except, Intersect


[ {    .Union(query)
    |  .Concat(query)
    |  .Except(query)
    |  .Intersect(query) } ]

The clauses combines the result sequence of the query with a result sequence of another query.

A union clause is an optional clause. It can be placed immediately after a Select clause or after any of "post-Select" clauses (aggregates, partitioning methods or quantifiers).

Union combines two sequences and excludes duplicates from the return set. Concat returns all the elements in the input sequences including duplicates. Except returns those elements in the main sequence that do not appear in the combined sequence. It does not also return those elements in the combined sequence that do not appear in the main sequence. Intersect returns a set that contains all the elements of the main sequence that also appear in the combined sequence, but no other elements.

The required clause argument must be a complete query statement that returns the combined sequence with the same result fields count and field types as the main sequence.

Sample:

Linq.From(Emp)
    .Select(Emp['EmpNo'])
    .Union(
 
DataContext.From(Dept)
    .Select(Dept['DeptNo'])
    )

Skip, Take, ElementAt, First


[ ( (  .Skip(count) 
       .Take(count) )
    |  .ElementAt(index) 
    |  .First ) ]

Partitioning clauses are used to extract a number of elements from the query result sequence.

Skip clause bypasses a specified number of elements in an input sequence and then returns the remaining elements. Skip can be applied on the result of the Select or Union clause. Take returns a specified number of contiguous elements from the input sequence. Take can be used alone after the Select or Union clause, and in this case it will return a number of elements form the start of a sequence. But, usually, the Skip and Take methods are functional complements and used together in order to perform result sequence pagination. In this case, Take has to be applied after Skip. ElementAt is used to obtain the sequence element at the specified index. First returns the first element in the specified sequence.

The required Skip argument is a number of elements that have to be bypassed. The required Take argument is a number of elements that have to be returned. The required ElementAt argument is a zero-based index of the element that has to be returned.  

Sample:

Linq.From(Emp)
    .Select([Emp['EmpNo'], Emp['EName']])
    .Skip(10)
 
Linq.From(Emp)
    .Select([Emp['EmpNo'], Emp['EName']])
    .Take(5)	
 
Linq.From(Emp)
    .Select([Emp['EmpNo'], Emp['EName']])
    .Skip(10)
    .Take(5)
 
Linq.From(Emp)
    .Select([Emp['EmpNo'], Emp['EName']])
    .ElementAt(5)
 
Linq.From(Emp)
    .Select([Emp['EmpNo'], Emp['EName']])
    .First

Any, All


[ (    .Any [ (condition) ]
    |  .All(condition) ) ]

Quantifiers are used to check the conformity of result sequence elements to a certain condition.

Any clause (if used without argument) returns True if the input sequence contains any elements, otherwise False. When Any is called with an argument, it determines whether any element of a sequence satisfies the specified condition and returns True in this case, otherwise False. All clause returns True if every element of the input sequence satisfies the specified condition, or if the sequence is empty. Otherwise it returns False.

The optional Any argument and required All argument is a logical expression that defines the condition which sequence elements must conform.

Sample:

Linq.From(Emp)
    .Select([Emp['EmpNo'], Emp['EName']])
    .Any
 
Linq.From(Emp)
    .Select([Emp['EmpNo'], Emp['EName']])
    .Any(Emp['Sal'] = 1000)
 
Linq.From(Emp)
    .Select([Emp['EmpNo'], Emp['EName']])
    .All(Emp['Sal'] >= 1000)
 

Count, Max, Min, Sum, Average

  [ (    .Count
      |  .Max
      |  .Min
      |  .Sum
      |  .Average ) ]

Count returns the number of elements in the input sequence. Max returns the maximum value in a sequence. Min returns the minimum value in a sequence. Sum computes the sum of a sequence values. Average computes the average of a sequence values.

None of aggregate clauses has arguments.

Sample:

Linq.From(Emp)
    .Select(Emp['EmpNo'])
    .Count
 
Linq.From(Emp)
    .Select(Emp['Sal'])
    .Max
 
Linq.From(Emp)
    .Select(Emp['Sal'])
    .Min
 
Linq.From(Emp)
    .Select(Emp['Sal'])
    .Sum
 
Linq.From(Emp)
    .Select(Emp['Sal'])
    .Average

Contains

attribute.Contains(pattern | query | [expression, {expression}])

Contains is used as an expression of the Where clause and defines the condition, which each of result elements must conform.

The argument of Contains can be a search pattern, a query or a set of expressions.

When the argument is a search pattern, Contains checks the corresponding attribute of each result element for conformity with this pattern. In this case, Contains will be thanslated into the LIKE SQL clause, so the search pattern can contain wildcards applicable in the LIKE clause.

When the argument is a set of expressions or a query, Contains checks whether the corresponding attribute of each result element is included in the specified set or a set returned by the query. In this case, Contains will be thanslated into the IN SQL clause.

Sample:


Linq.From(Emp)
    .Where(Emp['EName'].Contains('%dams'))
    .Select
 
Linq.From(Emp)
    .Where(Emp['DeptNo'].Contains(Linq.From(Dept).Select([Dept['DeptNo']]).Expression))
    .Select
 
Linq.From(Dept)
    .Where(Dept['DeptNo'].Contains([1, 2, 3]).Expression))
    .Select
© 1997-2024 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback