Query Records

Using Magentrix Query language (Linq Query), you can query the database and access Entity records.

In order to interact with the Magentrix cloud database, use the Database class. The database class allows you to query, insert, update or delete records. Similarly to Linq statements in C#.Net, you can write commands that will construct a query for the database. Within these statements, you can define which fields to be queried, specify filter conditions, sorting, pagination and much more. These queries are highly robust and enable issues to be debugged quickly, since the statements are compiled and maintain the dependencies within the system.
 

Queries and security

All queries in Magentrix run in the context of the current user's permissions- unless the developer runs the query in system mode, which uses the administrator privileges. 

Constructing queries

In order to construct queries, you need to understand Magentrix Entities and different field types. Using the Database class's "Query" method, you can access the Magentrix Query language:

// query all account (up to 10,000 records), 
// it is best to use .Limit() to limit the number of rows.
List<Account> accounts = Database.Query<Account>()
 .Select(f => new { f.Id,f.Name })
 .ToList();

// query certain accounts (filter by name)
List<Account> accounts = Database.Query<Account>()
  .Where(f => f.Name.Contains("media"))
  .Select(f => new { f.Id,f.Name })
  .ToList();

// query accounts that has parent (where parent field is not null)
List<Account> accounts = Database.Query<Account>()
  .Where(f => !string.IsNullOrEmpty(f.ParentId))
  .Select(f => new { f.Id,f.Name })
  .ToList();

// query the first match (single record)
var account = Database.Query<Account>()
  .Where(f => f.Name.Contains("media"))
  .Select(f => new { f.Id,f.Name })
  .First();

// query by Id
var account = Database.Retrieve("00200000000003q0341");

Selecting fields

You can limit the fields being loaded and select only those you need to use in order to optimize performance.
If you do not apply a "Select" call to your query, all fields of the Entity will be queried (similar to Select * FROM).

//query all fields (be aware of performance impacts)
List<Account> accounts = Database.Query<Account>()
  .Limit(10)
  .ToList();

//query specific fields (optimized performance)
List<Account> account = Database.Query<Account>()
  .Select(f => new { f.Id, f.Name, f.Industry })
  .Limit(10)
  .ToList();

Note: In order to perform update operations, all fields should be queried.
 

Sorting the results

// Order by Ascending
var accounts = Database.Query<Account>()
   .OrderBy(f => f.Name)
   .ToList();

// Order by Descending
var accounts = Database.Query<Account>()
   .OrderByDescending(f => f.Name)
   .ToList();

// Order by more than one field
var accounts = Database.Query<Account>()
   .OrderBy(f => f.Name)
   .ThenBy(f => f.Type)
   .ToList();

// Mixing Asending and Descending 
​var account = Database.Query<Account>()
   .OrderBy(f => f.Name)
   .ThenByDescending(f => f.Type)
   .ToList();

Limiting the results - applying pagination

// Limit the results to top 1000
var accounts = Database.Query<Account>()
   .Limit(1000)
   .ToList();

// Apply pagination using Skip and Take - Load 20 records and skip 10.
var accounts = Database.Query<Account>()
  .Limit(20,10)
  .ToList();

Eager-Load related Entities

Related Entities are defined via Lookup and Master-Detail fields. For example, a "Contact" entity that acts as a lookup to an "Account" entity via "AccountId" field establishes the relationship between a person and his/her company. This relationship is structured on the Magentrix Model as shown below:

public class Contact
{
    ...
    
    //foreign key which hold the ID of the Account record
    public string AccountId { get; set; }
    
    //reference field that allows accessing the Account record info from Contact
    public Account Account { get; set; }
}

When querying the "Contact" entity, you can ask the "Database" to "Query" the related "Account" as well. The query below loads all "Account" fields along with the "Contact" record. While this is very convenient and sometimes necessary, please note that this method may have an impact on performance as the system is required to load all the fields from the Account entity.

var contacts = Database.Query<Contact>()
    .Include(r => r.Account)
    .ToList();

Furthermore, you can fine-tune your query and optimize the performance by specifying only the fields you need:

var contacts = Database.Query<Contact>()
   .Include(r => r.Account, f => new { f.Id, f.Name, f.Type })
   .ToList();

More examples:

// eager-load all relationships - all fields (be aware of performance impacts)
var contacts = Database.Query<Contact>()
   .IncludeAll(Fields.All)
   .ToList();

// eager-load all relationships - only their Id and Name fields (has slightly better performance)
var contacts = Database.Query<Contact>()
   .IncludeAll(Fields.IdAndName)
   .ToList();

// eager-load one relationship - only Id and Name fields (best performance performance)
var contacts = Database.Query<Contact>()
  .IncludeIdAndName(r => r.Account)
  .ToList();

Running Queries in System Mode

All queries are executed in the user's context and the only records that are retrieved are the ones that the user is allowed to access. In certain scenarios, the developer will need to access the data in System Mode regardless of who is executing the code. To do so, follow the example below:

// query all customer contacts in system mode
var contacts = Database.Query<Contact>()
   .Where(f => f.Type == "Customer")
   .ToListAsAdmin();

// query the first match in system mode
var contact = Database.Query<Contact>()
    .Where(f => f.Type == "Customer")
    .FirstAsAdmin();