N1QL from the SDK
Edit this article in GitHub
Version 2.3

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. 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.