How to implement server side paging query in ArangoDB database

 

While reading data from arangodb database if you have large dataset returned from your query result you will be unable to read data from arangodb. In this case you have to use limit operation to limit results in you dataset.  The LIMIT operation allows you to reduce the number of results.

 

Syntax: Two general forms of LIMIT are:

LIMIT count
LIMIT offset, count

 

Example query:

For a1 IN Asset_Envelop
 FILTER a1.updatedDate<@a1_updatedDate
 LIMIT 0, 100
 RETURN {"assetid":a1.`assetId`, "assetcategorylevel2":a1.`assetCategoryLevel2`, "assetcategorylevel3":a1.`assetCategoryLevel3`, "modelid":a1.`modelId`, "serialno":a1.`serialNo`, "manufacturer":a1.`

manufacturer`, "assetcategorylevel4":a1.`assetCategoryLevel4`, "locationid":a1.`locationId`, "thirdpartyid":a1.`thirdPartyId`, "measureid":a1.`measureId`, "inventoryyear":a1.`inventoryYear`, "manufacturedate":a1.`manufactureDate`, "location":a1.`location`, "count":a1.`count`, "sizelength":a1.`sizeLength`, "sizewidth":a1.`sizeWidth`, "sizeunit":a1.`sizeUnit`, "installdate":a1.`installDate`, "assetstatus":a1.`assetStatus`, "assetcondition":a1.`assetCondition`, "assetname":a1.`assetName`, "assetmaterial":a1.`assetMaterial`, "insulationlocation":a1.`insulationLocation`, "insulationtype":a1.`insulationType`, "insulationcondition":a1.`insulationCondition`, "glazingtype":a1.`glazingType`, "caulkingtype":a1.`caulkingType`, "caulkingcondition":a1.`caulkingCondition`, "weatherstrippingtype":a1.`weatherstrippingType`, "weatherstrippingcondition":a1.`weatherstrippingCondition`, "frametype":a1.`frameType`, "framecondition":a1.`frameCondition`, "additionalconditioncomments":a1.`additionalConditionComments`, "warranty":a1.`warranty`, "warrantystartdate":a1.`warrantyStartDate`, "warrantyenddate":a1.`warrantyEndDate`, "did":a1.`did`}

 

For a1 IN Asset_Envelop
 FILTER a1.updatedDate<@a1_updatedDate
 LIMIT 200, 100
 RETURN {"assetid":a1.`assetId`, "assetcategorylevel2":a1.`assetCategoryLevel2`, "assetcategorylevel3":a1.`assetCategoryLevel3`, "modelid":a1.`modelId`, "serialno":a1.`serialNo`, "manufacturer":a1.`

manufacturer`, "assetcategorylevel4":a1.`assetCategoryLevel4`, "locationid":a1.`locationId`, "thirdpartyid":a1.`thirdPartyId`, "measureid":a1.`measureId`, "inventoryyear":a1.`inventoryYear`, "manufacturedate":a1.`manufactureDate`, "location":a1.`location`, "count":a1.`count`, "sizelength":a1.`sizeLength`, "sizewidth":a1.`sizeWidth`, "sizeunit":a1.`sizeUnit`, "installdate":a1.`installDate`, "assetstatus":a1.`assetStatus`, "assetcondition":a1.`assetCondition`, "assetname":a1.`assetName`, "assetmaterial":a1.`assetMaterial`, "insulationlocation":a1.`insulationLocation`, "insulationtype":a1.`insulationType`, "insulationcondition":a1.`insulationCondition`, "glazingtype":a1.`glazingType`, "caulkingtype":a1.`caulkingType`, "caulkingcondition":a1.`caulkingCondition`, "weatherstrippingtype":a1.`weatherstrippingType`, "weatherstrippingcondition":a1.`weatherstrippingCondition`, "frametype":a1.`frameType`, "framecondition":a1.`frameCondition`, "additionalconditioncomments":a1.`additionalConditionComments`, "warranty":a1.`warranty`, "warrantystartdate":a1.`warrantyStartDate`, "warrantyenddate":a1.`warrantyEndDate`, "did":a1.`did`}

 

 The query performs paged query on database and returns limited results which works fine with large dataset also.

 

 

A brief introduction to ArangoDB, its data models and use cases

 

What is ArangoDB?
ArangoDB is an open-source, NoSQL, multi-model database system. It was designed to support multiple data models (key-value, document, graph) within a single database engine. This versatility allows developers to efficiently manage and query data using different paradigms without needing to integrate multiple specialized databases. It is a scalable, fully managed graph database, document store and search engine in one place.

Data Models of ArangoDB: 

ArangoDB supports three primary data models: key-value, document, and graph.

Key-Value Model: In this model, data is stored as key-value pairs, where each key is associated with a value. It's a simple and efficient way to store and retrieve data when you don't require complex relationships or querying capabilities.

Document Model: ArangoDB's document model is similar to JSON or BSON documents. Documents are stored in collections, and each document can have different attributes and structures. This flexibility is useful for handling semi-structured or variable data.

Graph Model: ArangoDB provides robust support for graph databases, allowing you to represent and traverse complex relationships between data entities. This is particularly beneficial for applications like social networks, recommendation engines, and fraud detection.

Key features of ArangoDB include:

Multi-Model Support: ArangoDB can store and query data in three different models: key-value, document, and graph. This flexibility is useful when dealing with diverse data types and relationships.

Native Graph Processing: ArangoDB supports graph databases, making it easy to model, query, and analyze data with complex relationships. It provides efficient graph traversal capabilities.

Joins and Transactions: ArangoDB supports ACID transactions and allows for complex joins between collections, even across different data models. This is particularly valuable when working with interconnected data.

Flexible Query Language(AQL): ArangoDB uses a query language called AQL (ArangoDB Query Language) that combines the strengths of SQL and other query languages. It supports complex queries, joins, and filtering.

Storage Engine: ArangoDB employs a storage engine that's optimized for modern hardware, ensuring good performance for read and write operations.

Replication and Sharding: ArangoDB supports data replication for high availability and automatic failover. It also provides sharding capabilities for distributing data across nodes in a cluster.

Full-Text Search: ArangoDB offers full-text search capabilities, allowing you to search for words or phrases across large datasets.

Schema-Free: While you can define a schema for your data, ArangoDB is also schema-free, allowing you to store and manage data without predefined structures.

Community and Enterprise Editions: ArangoDB comes in both open-source Community and commercial Enterprise editions. The Enterprise edition offers additional features and support for production environments.

 

Use cases of ArangoDB:

 ArangoDB's flexibility as a multi-model database makes it suitable for various use cases that involve diverse data types and complex relationships. Here are some common use cases where ArangoDB can shine:

1. Graph Applications:
   ArangoDB's native graph database capabilities make it an excellent choice for applications that heavily rely on analyzing and traversing complex relationships, such as social networks, recommendation engines, fraud detection, and network analysis.

2. Content Management Systems (CMS):
   ArangoDB can be used to build content management systems where structured data (like user profiles and settings) and unstructured data (like articles, images, and documents) need to coexist in the same database.

3. E-Commerce Platforms:
   E-commerce applications often deal with product catalogs, user profiles, order histories, and recommendations. ArangoDB's multi-model nature allows developers to manage both structured and relationship-rich data efficiently.

4. Internet of Things (IoT):
   IoT applications involve a wide variety of data sources and sensor readings. ArangoDB's ability to store and query different data models can help manage sensor data, device information, user profiles, and more.

5. Geospatial Applications:
   For applications that deal with geographic data, like location-based services, mapping, and geospatial analysis, ArangoDB's graph capabilities can help represent and analyze geographical relationships effectively.

6. Collaboration Platforms:
   Platforms that facilitate collaboration among users, like project management tools or document sharing systems, can benefit from ArangoDB's support for documents, user profiles, and relationships.

7. Knowledge Graphs:
   Building knowledge graphs involves representing concepts, entities, and relationships between them. ArangoDB's graph model is well-suited for constructing and querying such knowledge representations.

8. Fraud Detection and Risk Management:
   Applications that need to identify patterns of fraudulent activities can utilize ArangoDB's graph capabilities to model and analyze intricate relationships between entities involved in fraudulent behavior.

9. Real-Time Analytics:
   ArangoDB can serve as a backend for real-time analytics applications, combining different data models to store user profiles, event data, and relationships for generating insights.

10. Hybrid Applications:
    Many applications require different data models at different stages or components. ArangoDB's ability to seamlessly integrate key-value, document, and graph models can simplify development in such cases.

11. Personalization and Recommendation Systems:
    ArangoDB can store user preferences, behaviors, and item data, allowing developers to create personalized recommendations and improve user experiences.

12. Time Series Data:
    With the right data modeling, ArangoDB can be used to store and analyze time series data, which is common in applications like monitoring, logging, and IoT.

These are just a few examples, and ArangoDB's versatility opens up possibilities for even more use cases. However, it's important to assess the specific requirements of your application to determine whether ArangoDB is the right fit based on factors like data structure, relationships, and query patterns.

 

How to write async C# crud methods for Employee model from ArangoDB

Using Linq:

 

using System;

using System.Collections.Generic;

using System.Linq.Expressions;

using System.Threading.Tasks;

using ArangoDB.Client;

using ArangoDB.Client.Linq;

using ArangoDB.Client.Query;

public class EmployeeRepository

{

    private readonly string _databaseName;

    private readonly ArangoDatabase _database;

    public EmployeeRepository(string url, string databaseName, string username, string password)

    {

        var connection = new ConnectionSettings(url, databaseName)

        {

            Database = databaseName,

            Credential = new NetworkCredential(username, password)

        };

        var database = new ArangoDatabase(connection);

        _databaseName = databaseName;

        _database = database;

    }

    public async Task<string> CreateEmployeeAsync(Employee employee)

    {

        var collection = _database.Collection<Employee>();

        await collection.InsertAsync(employee);

        return employee.Id;

    }

    public async Task<IEnumerable<Employee>> GetEmployeesAsync()

    {

        var collection = _database.Collection<Employee>();

        return await collection.ToListAsync();

    }

    public async Task<Employee> GetEmployeeByIdAsync(string id)

    {

        var collection = _database.Collection<Employee>();

        var query = from employee in collection.Linq()

                    where employee.Id == id

                    select employee;

        return await query.FirstOrDefaultAsync();

    }

    public async Task<bool> UpdateEmployeeAsync(Employee employee)

    {

        var collection = _database.Collection<Employee>();

        var result = await collection.UpdateAsync(employee.Id, employee);

        return result.Updated == 1;

    }

    public async Task<bool> DeleteEmployeeAsync(string id)

    {

        var collection = _database.Collection<Employee>();

        var result = await collection.RemoveByIdAsync<Employee>(id);

        return result.Removed == 1;

    }

}

 

Using AQL:

 

using System;

using System.Collections.Generic;

using System.Linq.Expressions;

using System.Threading.Tasks;

using ArangoDB.Client;

using ArangoDB.Client.Query;

public class EmployeeRepository

{

    private readonly string _databaseName;

    private readonly ArangoDatabase _database;

    public EmployeeRepository(string url, string databaseName, string username, string password)

    {

        var connection = new ConnectionSettings(url, databaseName)

        {

            Database = databaseName,

            Credential = new NetworkCredential(username, password)

        };

        var database = new ArangoDatabase(connection);

        _databaseName = databaseName;

        _database = database;

    }

    public async Task<string> CreateEmployeeAsync(Employee employee)

    {

        var query = new AqlQuery()

        {

            Query = $"INSERT {employee.ToJson()} INTO employees RETURN NEW._key",

        };

        var result = await _database.QueryAsync<string>(query);

        return result.FirstOrDefault();

    }

    public async Task<IEnumerable<Employee>> GetEmployeesAsync()

    {

        var query = new AqlQuery()

        {

            Query = "FOR employee IN employees RETURN employee",

        };

        var result = await _database.QueryAsync<Employee>(query);

        return result.ToList();

    }

    public async Task<Employee> GetEmployeeByIdAsync(string id)

    {

        var query = new AqlQuery()

        {

            Query = $"FOR employee IN employees FILTER employee._key == '{id}' RETURN employee",

        };

        var result = await _database.QueryAsync<Employee>(query);

        return result.FirstOrDefault();

    }

    public async Task<bool> UpdateEmployeeAsync(Employee employee)

    {

        var query = new AqlQuery()

        {

            Query = $"UPDATE '{employee.Id}' WITH {employee.ToJson()} IN employees RETURN NEW._id",

        };

        var result = await _database.QueryAsync<string>(query);

        return result.Any();

    }

    public async Task<bool> DeleteEmployeeAsync(string id)

    {

        var query = new AqlQuery()

        {

            Query = $"REMOVE '{id}' IN employees RETURN OLD._id",

        };

        var result = await _database.QueryAsync<string>(query);

        return result.Any();

    }

}