DA LINQ (.NET)

LINQ, or Language Integrated Query, was a language paradigm introduced by .NET 3.5 Framework. This paradigm allows to write type-safe Query Expressions to query data as part of the primary programming language (C#, Oxygene, or other).

Data Abstract for .NET contains a LINQ query provider for Data Abstract services. This LINQ query provider can be found in the RemObjects.DataAbstract.dll assembly.

Querying the data

Data query

Accessing the data via the LINQ provider is very simple.

At first an IQueryable<> data source should be obtained via the Data Adapter's GetTable<> method. Then in can be used in the usual LINQ query:

var result = from c in dataAdapter.GetTable<Customers>()
             where c.Name == "ALFKI"
             select new { c.Id, c.Name, c.Phone };

var result := from x in dataAdapter.GetTable<Customer>
              where x.CustomerId = 'ALFKI'
              select new class(x.CustomerID, x.Name, x.ContactName);

The Customers class mentioned in the code above is called a Table Definition class (see below).

Code the sample above won't result in an actual request being sent to the Data Abstract server. The request will be sent to the server only when the query is materialized (f.e. when the result.ToList() method is called).

Materialization of a DA LINQ query can be performed synchronously or asynchronously.

The simplest way of actually loading data is to call .ToList or .ToArray method on a DA LINQ query:

var data = query.ToList();

var data := query.ToList();

The code above is performed synchronously. Data Abstract also provides a set of methods that allow to load data in asynchronous manner:

• ToListAsync<T>
• ToArrayAsync<T>
• ToBindingListAsync<T>

var data = await query.ToListAsync();

var data := await query.ToListAsync();

Inner Join query

DA LINQ also allows to query data from more than one table at once when these tables are joined via an INNER JOIN:

var result = from c in dataAdapter.GetTable<Clients>()
             join o in dataAdapter.GetTable<Jobs>()
                on c.CustomerId equals o.CustomerId
             where c.CustomerId == "ALFKI"
             orderby o.OrderDateField
             select c;
             

Left Join Query

DA LINQ also allows to query data from more than one table at once when these tables are joined via a LEFT JOIN:

var query = from o in dataAdapter.GetTable<Orders>()
            join od in dataAdapter.GetTable<OrderDetails>() on o.Id equals od.OrderId into oj
            from od in oj.DefaultIfEmpty()
            select new {Order = o, Details = new OrderDetails {Id = od.Id, OrderId = od.OrderId}};
             

Please note that in case of a LEFT JOIN query DA LINQ requires that queried fields of the joined table to be explicitly listed in the query.

Built-in functions support

DA LINQ supports a small subset of method and properties to be used in the where clause of a LINQ expression. These calls are converted into SQL statements and are executed directly on the database level.

Supported methods and properties are:

• String values

  • Trim()
  • TrimStart()
  • TrimEnd()
  • Substring() (*)
  • Contains()
  • StartsWith()
  • EndsWith()
  • ToLower() / ToLowerInvariant()
  • ToUpper() / ToUpperInvariant()
  • Length

• DateTime values

  • DateTime.Now
  • DateTime.Today
  • Second
  • Minute
  • Hour
  • Day
  • Month
  • Year

• Any nullable values

  • coalesce (in Oxygene) / ?? (in C#)

Eager Data Loading

Let's assume there are 3 tables with obvious relationships (Orders -> OrderDetails -> Products) and it is required to load some set of Orders along with their details and products data.

Doing this in the straightforward manner will result in a set of queries (query the orders, then query order details for each of these orders, then query product data...).

This is the infamous n+1 selects problem that affects all existing ORM (in this case Data Abstract works as an ORM and is also affected by this fundamental problem).

In general Data Abstract doesn’t support navigational properties or eager loading. Still it IS possible to fetch all data is several related tables using singe query, however this will require some additional code.

At first the table definition class fro the Orders table has to be expanded with an additional property that will contain the loaded details data and a pair of Equals/GetHashCode methods. The methods are necessary to allow the Orders class to be used as a key in the LinqToObjects GroupBy method.

In general Table Definition classes do not need these methods because the GroupBy is effectively translated into the GROUP BY SQL clause and is executed by the database server itself. At the same time LinqToObjects operations are performed over objects in local memory, so .NET runtime needs some help to be able to distinguish objects correctly.

Thanks to the partial modifier applied to the Orders class it can be expanded by additional code placed in a separate file. This way this custom code won't be lost when the TableDefinitions file is regenerated.

public partial class Orders
{
    [RemObjects.DataAbstract.Linq.IgnoreProperty]
    public IList<OrderDetails> Details { get; set; }

    public override bool Equals(object obj)
    {
        ...
    }

    public override int GetHashCode()
    {
        ...
    }
}

This is a relatively simple join over 2 tables:

// Aliases to simplify the query code
// These assignments do not cause any data request operations
var orders = this._dataModule.DataAdapter.GetTable<Orders>();
var orderDetails = this._dataModule.DataAdapter.GetTable<OrderDetails>();
var products = this._dataModule.DataAdapter.GetTable<Products>();

var query = orders
    .Where(o => o.OrderStatus != 1)
    .Join(orderDetails,
        o => o.Id,
        od => od.OrderId,
        (order, details) => new { Order = order, Details = details })
    .ToList()
    .Select(g => g)
    .GroupBy(g => g.Order)
    .Select(g =>
    {
        g.Key.Details = g.Select(r => r.Details).ToList();
        return g.Key;
    });

What happens here:

• A WHERE condition is defined. It is better to apply conditions earlier as this will reduce the server -> client traffic and the database workload.
• JOIN between the Orders and OrderDetails tables is defined.
• .ToList() call is issued. At this point the query will be sent to the Data Abstract server and executed. The result is a denormalized join result between the Orders and OrderDetails tables. All Linq operators below are performed over local in-memory objects, not over the database rows.
• .GropyBy operation is performed. This is the point where earlier defined Equals/GetHashCode methods are used.
• The .Select operation is performed. This operation takes the LINQ group object and converts it into a Orders object with initialized Details property.

The query below is a bit more complex because it involves more than one join:

// Aliases to simplify the query code
// These assignments do not cause any data request operations
var orders = this._dataModule.DataAdapter.GetTable<Orders>();
var orderDetails = this._dataModule.DataAdapter.GetTable<OrderDetails>();
var products = this._dataModule.DataAdapter.GetTable<Products>();

var query = orderDetails
    .Join(products,
        od => od.ProductId,
        p => p.Id,
        (od, p) => new { Detail = od, Product = p })
    .Join(orders,
        od => od.Detail.OrderId,
        o => o.Id,
        (detail, order) => new { Order = order, Detail = detail })
    .Where(o => o.Order.OrderStatus != 128)
    .ToList()
    .Select(g => g)
    .GroupBy(g => g.Order)
    .Select(g => new { Order = g.Key, Details = g.Select(r => r.Detail).ToList() });

Still this query uses the same approach:

• Fetch denormalized data
• Normalize it back client-side

Updating the data

Changes tracking

Objects loaded via DA LINQ can be changed and passed beck to the server to trigger the database update.

There are 2 kinds of changes tracking - automatic and manual ones.

Automatic one is performed when 2 conditions are met:

• DataAdapter.UseBindableClass property is set to true
• The result of the DA LINQ query is a value(s) of one the TableDefinition classes (ie when result type is not an anonymous class and the TableNameAttribute has been applied to it)

In this case the resulting data has automatic tracking changes enabled.

Note: If the data requested won’t be changed or these changes shouldn’t be persisted, consider setting DataAdapter.UseBindableClass to false to save CPU and memory

Manual changes tracking is performed via the NewRow / UpdateRow / DeleteRow methods:

var customer = new Customers();

// Set object properties
// ...
// Set object properties

// Insert data
dataAdapter.InsertRow(customer);

// Update data
customer.Name := "XXX";
dataAdapter.UpdateRow(customer);

// Delete data
dataAdapter.DeleteRow(customer);

The Customers class mentioned in the code above is called a Table Definition class (see below).

Applying Changes

Changes made to the data objects are not applied immediately to the database, regardless of the changes tracking mode used (manual or automatic one).

An explicit method call is required to trigged the data update process:

dataAdapter.ApplyChanges();

dataAdapter.ApplyChanges();

An asynchronous version of this method is available as well:

await dataAdapter.ApplyChangesAsync();

await dataAdapter.ApplyChangesAsync();

BeginUpdate / EndUpdate / CancelUpdate

These 3 methods are generated for each TableDefinition class:

• BeginUpdate creates an internal clone of the current object
• EndUpdate provides that internal clone to the Update method of the DataAdapter along with the current object values
• CancelUpdate clears that internal clone object

It is not required to call these methods explicitly, but they provide a great benefit as they allow to send reduced deltas for the data changes made.

To successfully compose a delta for an object client code requires current values stored in the object fields. The downside of this approach is that the resulting delta will contain values for all object fields. Even more, it won’t be possible to reduce it to send only changed values to the server, because there is no information about old values.

Luckily the BeginUpdate/EndUpdate method pair does provide data required to compose a reduced delta because it provides old object values.

So the general rule is:

• For reasonably-sized objects use non-reduced deltas and automatic change tracking until it becomes a bottleneck, then switch to explicit BeginUpdate/EndUpdate method calls.
• For objects that contain significant amount of data (like binary blobs) consider to always use the BeginUpdate/EndUpdate methods.

Table definition classes

DA LINQ uses auto generated plain simple classes to materialize data fetched from the Data Abstract server.

These auto generated classes are called Table Definition classes and represent Schema tables exposed by Data Abstract server. All Table Definition classes are marked as public partial so they can be extended in a separate code file(s) when needed.

Table Definition classes are generated during creating new Data Abstract client project by the New Project Wizard. Table Definition classes have to be regenerated once the server schema changes. This can be done either via context menu available for the TableDefinitions project item or via corresponding Visual Studio menu item. In Visual Studio 2019 this menu item can be found under Extensions -> Remoting SDK & Data Abstract, while in Visual Studio 2017 it is located under Tools -> Remoting SDK & Data Abstract.