N1QL from the SDK
Edit this article in GitHub
Version 2.4

N1QL from the SDK

You can perform N1QL queries via the Couchbase .NET client.

Note: See Couchbase Developer documentation for a quick intro to N1QL.
To issue N1QL queries, you should create a QueryRequest object, and pass it to the Query(QueryRequest query) method in the CouchbaseBucket class. A few variants of such a query exist:
  • Simple queries, which are only strings and do not use placeholders;
  • Parameterized queries, which use numbered or named placeholders.
You can create each via the corresponding factory method on QueryRequest, which you can create using a Statement produced by the N1QL DSL or using the statement directly as a String.

The return value from Query(QueryRequest query) is the object QueryResult. Iterating over the QueryResult.Rows<T> property will yield the rows returned by the server for the given query (as the Type specified by T). Each row represents a row received for the query.

Note that errors returned by the N1QL service during execution are returned in the QueryResult.Errors property, rather than by throwing exceptions. Here is a list of important QueryResult properties:

  • Success: Returns true if the whole query could be executed successfully, including retrieving all the results and streaming them to the client.
  • Message: Returns a string with error information if Success is false.
  • Exception: Returns an exception if one was thrown internally. This can be re-thrown by the application level code.
  • Rows: Contains all rows returned by the query; it can be an empty list.
  • RequestId: The server-generated unique ID (Guid) for this query (useful to find associated logs on the N1QL server).
  • ClientContextId: User-provided identifier reflected in the server's response. The identifier can be useful for grouping several queries (for example, in a kind of in-house transaction) and find all associated queries.
  • Metrics: Returns a Metrics object, which contains metrics for the query (such as the number of results or processing time).
  • Errors: Returns a list of Error objects describing errors (if any occurred during execution).
  • Warnings: Returns a list of Warning objects describing errors (if any occurred during execution).
You can use N1QL placeholders in the query. Placeholders allow you to specify variable constraints for an otherwise constant query. There are two variants of placeholders: postional and named parameters. Positional parameters use an ordinal placeholder for substitution and named parameters use variables. A named or positional parameter is a placeholder for a value in the WHERE, LIMIT or OFFSET clause of a query. Here are examples of using both postional and named parameters:
N1QL Query with positional and named parameters
//Positional parameters example
var queryRequest = new QueryRequest()
    .Statement("SELECT * FROM `travel-sample` LIMIT $1")
    .AddPositionalParameter(10)
    .Metrics(false);

var result = await bucket.QueryAsync<dynamic>(queryRequest);
foreach (var row in result.Rows)
{
    ...
}

//Named parameters example
var queryRequest = new QueryRequest()
    .Statement("SELECT * FROM `travel-sample` LIMIT $limit")
    .AddNamedParameter("$limit", 10);

var result = await bucket.QueryAsync<dynamic>(queryRequest);
foreach (var row in result.Rows)
{
    ...
}

Note that named and positional parameters are optional and you could chose to construct a query string at runtime and do the substitution yourself, but that takes a bit more effort.

Building Statements with LINQ (Language Integrated Query)

The N1QL LINQ provider is a powerful way to guide you in building your statements: you obtain Type safety and auto completion of relevant methods and N1QL clauses. It implements Microsoft's language integrated query extensions to the C# language and is similar to other LINQ providers like Linq2SQL, Entity Framework and NHibernate. The difference is that it emits N1QL in place of SQL. If you have used any other LINQ provider, you will find its familiarity and consistency make it an easy transition.

The Linq2Couchbase, the LINQ provider for the Couchbase .NET SDK, is a stand-alone project which depends on Couchbase .NET SDK and can be found on NuGet and by using the NuGet package manager to include the dependency:
PM> Install-Package Linq2Couchbase
Once you have included the dependency, you can construct queries just like any other LINQ provider in a SQL-like fashion. Nearly all N1QL statements and functions are supported, for example:
  • INNER, LEFT Outer, and Index JOINs
  • Math and Date functions
  • Filtering, projections and sorting
  • Grouping and aggregations
  • META keyword, NEST and UNNEST
  • Etc.
In addition to N1QL language features, Linq2Coucbase supports ORM features like Change-tracking and mapping of JSON fields to keys and other POCO properties. The provider's core object is the BucketContext, which is a Unit of Work (UoW) type object for performing querying and mutations on a per request basis. Here is an example of using Linq2Couchbase to generate and execute a N1QL query:
N1QL query using Linq2Couchbase
ClusterHelper.Initialize();
var db = new BucketContext(ClusterHelper.GetBucket("travel-sample"));
var query = (from landmark in db.Query<Landmark>()
             where landmark.Type == "landmark"
             where landmark.City == "Gillingham"
             select new
             {
                 landmark.Name,
                 landmark.Address
             }).Take(10);

foreach (var l in query)
{
    Console.WriteLine(l.Name);
}

If you find out that the Linq2Couchbase doesn't support a particular statement, clause or keyword, you can always revert to providing your statement in plain string form. Using the string form ensures that even if LINQ provider lags behind the evolutions of the language, users will always have a means of using new language features.

Querying Asynchronously

To perform a query asynchronously, use the QueryAsync(QueryRequest query) method. The API is pretty similar except everything is returned as an Task<QueryResult> that will have to be awaited on and you'll need use the async and await keywords to execute the task asynchronously or use Task.Result() to execute the Task synchronously. For example, lets retrieve the first ten documents from the travel-sample bucket asynchronously:
Asynchronous N1QL query
public async void PrintTenAllAsync()
{
    const string query = "SELECT * FROM `travel-sample` LIMIT 10";

    var result = await _bucket.QueryAsync<dynamic>(query);
    foreach(var row in result.Rows)
    {
        Console.WriteLine(row.name);
    }
}
In the code above, we have a defined a method signature for PrintTenAllAsync that includes the async keyword, this will allow us to use the await keyword within the method to asynchronously execute the query. Within the body of the method, we create a N1QL statement which returns the first ten (10) documents from the travel-sample bucket. Then, using the await keyword, we execute the Task<QueryResult> returned from the CouchbaseBucket.QueryAsync(…) method. This will push the execution of the query to the (likely) thread pool in a non-blocking manner. Finally, we iterate through the rows returned by server in the QueryResult object.

Scan Consistency

Setting a staleness parameter for queries, with scan_consistency, enables a tradeoff between latency and (eventual) consistency. A N1QL query using the default Not Bounded Scan Consistency will not wait for any indexes to finish updating before running the query and returning results, meaning that results are returned quickly, but the query will not return any documents yet to be indexed.

With Scan Consistency set to RequestPlus, all document changes and index updates are processed before the query is run. Select this when consistency is always more important than performance. For a middle ground, AtPlus is a "read your own write" (RYOW) option, which means it just waits for the new documents that you specify to be indexed, rather than an entire index of multiple documents. See the examples for how to use AtPlus for the best performance balance for many circumstances.

Streaming Large Result Sets

By default the .NET SDK will fetch the entire result set from the server, and de-serialize the entire set in-memory on the application. For smaller result sets this is fine, however with large result sets this causes the memory footprint to grow linearly upwards. This has an impact on CLR Garbage Collection and can lead to poor application performance, and eventually an OutOfMemoryException may be thrown. To avoid this, it is suggested that large results sets be streamed by setting the UseStreaming property on the QueryRequest object:

Streaming N1QL Query
var request = new QueryRequest("SELECT * FROM `travel-sample`;").UseStreaming(true);
using (var result = _bucket.Query<DocumentContent>(request))
{
    foreach (var doc in result)
    {
        Console.WriteLine(doc);
    }
}
When streaming is enabled, the client will start a persistent connection with the server and only read the header until the Rows are enumerated; then, each row or JSON object will be de-serialized. The net effect is that the memory footprint of the application will stay a constant and not increase linearly, and the Garbage Collector will collect objects created during de-serialization in the first generation.