What is LINQ for Delphi in EntityDAC?

The main idea of EntityDAC, as well as any ORM for RAD Studio, is to free the programmer from having to implement a business-logic, and the logic of database interaction separately in the application source code. When writing code, programmer abstracts himself from specifics of a DBMS and operates the database entities using structures of his “native” programming language.

I.e., if in the classic approach a similar code is used for updating a database record:

Query1.SQL.Text := 'UPDATE Master ' +
                   'SET Name = ''New Name'' ' +
                   'WHERE MasterId = 10';
Query1.Execute;

then when using EntityDAC, a similar operation is performed in a more “natural” way:

var
  Master: TMaster;
begin
  // retrieve a TMaster instance
  
  Master.Name := 'New Name';
  Master.Save;
end;

In the above sample, a code fragment is commented deliberately, that logically leads us to the following task: retrieving an entity (or entity collection) by required criteria in the context of ORM ideology.

In the classical approach, SQL is used:

Query1.SQL.Text := 'SELECT * FROM Master WHERE MasterId = 10';
Query1.Open;

And in EntityDAC, to retrieve entities or their collections, a special language is used — LINQ.

What is LINQ?

LINQ – is a language specialized on writing queries similar to SQL-queries serving for retrieving data from the database and mapping them to entities or entity collections.

What are the advantages of using LINQ?

Firstly: a programmer gets a universal syntax for writing queries, that doesn’t depend on the specifics of the used DBMS.

For instance: the task is to retrieve first 5 records from a table. In MySQL and InterBase the corresponding queries will look differently, that must be taken into account when writing an application:

MySQL

SELECT Name
FROM Master
LIMIT 5;

InterBase

SELECT FIRST 5 Name
FROM Master;

When using LINQ, it will be enough to write one query in the application source code, that will be executed correctly not depending on the used DBMS:

Query := From(Master).Select.Take(5);

Secondly: using the “native” Delphi syntax when writing queries gives the programmer all the advantages of the built-in code editor:

And finally, syntax mistakes and typos are fully avoided when writing queries.

Sample:

Query1.SQL.Text := 'SELECT Name FROM Master WHER MasterId = 10';

or

Query1.SQL.Text := 'SELECT Nam FROM Master WHERE MasterId = 10';

In the classical use of SQL such errors would appear only in application run time. Using LINQ minimizes probability of such situations, since LINQ is validated at the stage of application compilation.

LINQ architecture in EntityDAC

The LINQ syntax is built on the principle of “method chaining”, i.e. each subsequent query operator is a method of execution result of the previous operator. This approach eliminates the use of intermediate variables and makes the query code more readable. For example, compare the code:

Query := From(Master).Where(Master.Id = 10).Select;

with such a structure:

a := From(Master);
b := a.Where(Master.Id = 10)
c := b.Select;

Moreover, the principle of «method chaining» defines the overall structure of a LINQ query, that differs from the usual query structure in SQL. Building a LINQ query is based on the following two provisions:

• each query operator returns a set of entities;
• each subsequent operator applies a specific action to this set and returns the resulting subset.

Let’s consider an example query:

Query := From(Master).
         Where(Master.Id > 10).
         OrderBy(Master.Name).
         Select(Master.Name);

The From(Master) operator returns the whole instance set of the Master entity. The following operator Where(Master.Id > 10) applies a filter by the Id field to this set and «passes» the resulting subset to the following operator. The OrderBy(Master.Name) operator sorts the resulting subset by the Master.Name field and passes it further. Finally, the Select(Master.Name) operator generates a new subset of entities, that contain the only field Master.Name. The generated query is saved to the Query variable for further execution and result materializing.

The detailed description of the LINQ syntax, implemented in EntityDAC, is available in the corresponding section of the EntityDAC documentation.

Query result materialization means retrieving query results as entities or entity collections using the GetEntity and GetEntities methods.

Query := From(Emp).
         Where(Emp.DeptNo = 10).
         Select;

Emps := Context.GetEntities<TEmp>(Query);

Some peculiarities LINQ implementation in EntityDAC

In all the code samples in this article the generated LINQ query is assigned to the Query variable. A variable for storing the generated LINQ query must be declared in the following way:

var
  Query: ILinqQueryable;

This approach frees the programmer from having to monitor the destruction of the variable.

Any LINQ query begins from specifying the source of data, i.e. the “From” operator. There is a global function defined in the EntityDAC.Linq module of EntityDAC:

function From(const Source: TExpression): ILinqFrom;

that is the «starting point» for building a LINQ query.