{"id":13087,"date":"2022-04-18T11:02:34","date_gmt":"2022-04-18T18:02:34","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/?p=13087"},"modified":"2025-06-13T20:56:46","modified_gmt":"2025-06-14T03:56:46","slug":"asp-net-crud-nosql-part-2","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/asp-net-crud-nosql-part-2\/","title":{"rendered":"ASP.NET Core CRUD with NoSQL: Part 2"},"content":{"rendered":"

CRUD stands for Create, Read, Update, and Delete. In part 2, we\u2019ll look at R for Read, and build an ASP.NET Core endpoint to read data from Couchbase using SQL.<\/span><\/p>\n

Make sure to read <\/span>part 1 of this series<\/span><\/a>, which covers setup and configuration of your ASP.NET Core “wishlist” project.<\/span><\/p>\n

SQL++ to read<\/b><\/h2>\n

Couchbase is unique among NoSQL databases in that it supports a <\/span>full SQL implementation<\/span><\/a> (called SQL++, n\u00e9e N1QL) to query JSON data.<\/span><\/p>\n

SQL++ is not a watered-down “SQL-like” language. With SQL++, you have <\/span>JOINs<\/span><\/a>, <\/span>CTEs\/WITH<\/span><\/a>, <\/span>UPDATE<\/span><\/a>, <\/span>INSERT<\/span><\/a>, <\/span>DELETE<\/span><\/a>, <\/span>MERGE<\/span><\/a>, <\/span>aggregation\/GROUP BY<\/span><\/a>, <\/span>BEGIN\/COMMIT\/ROLLBACK<\/span><\/a>, and more.<\/span><\/p>\n

On top of that (the “++”), you also get features to deal with JSON data, like <\/span>MISSING<\/span><\/a>, <\/span>NEST<\/span><\/a>, <\/span>ARRAY_* functions<\/span><\/a>, <\/span>OBJECT_* functions<\/span><\/a>, and more.<\/span><\/p>\n

For this simple CRUD application, we\u2019ll use a SQL++ <\/span>SELECT<\/span> query (and index) to return <\/span>all<\/b> items from my wishlist.<\/span><\/p>\n

Writing your first SQL++ query<\/b><\/h2>\n

First, let\u2019s try writing a SQL++ query to get all wishlist items right in the <\/span>Capella Query Workbench<\/span><\/a>.<\/span><\/p>\n

To start with, try:<\/span><\/p>\n

SELECT * FROM demo._default.wishlist;<\/pre>\n
When you do this, you should get an error message. Something like:<\/span><\/p>\n
`no index available on keyspace default:demo._default.wishlist that matches your query. Use CREATE PRIMARY INDEX` ...<\/pre>\n
This is expected behavior. (Most) SQL++ queries in Couchbase will not run unless there is at least one index available for them.<\/span><\/p>\n
No problem. Create a simple <\/span>PRIMARY INDEX<\/span><\/a> with a command like this:<\/span><\/p>\n
CREATE PRIMARY INDEX `ix_wishlist` ON `demo`.`_default`.`wishlist`<\/pre>\n
Primary indexes are generally not meant to be used in a typical production environment, but they are very helpful for a development environment, since they guarantee that <\/span>any<\/b> SQL++ query will run on the indexed collection (though not as efficient as a properly indexed collection). Once you start creating more complex SQL++ queries, you can use the <\/span>“Advise” button on the Query Workbench<\/span><\/a> to get suggestions of more efficient indexes to create (and you should avoid using <\/span>SELECT *<\/span> whenever you can \ud83d\ude06).<\/span><\/p>\n
After creating the index, retry the above <\/span>SELECT<\/span> query again, and the results should look like this:<\/span><\/p>\n
[\r\n\u00a0 {\r\n\u00a0 \u00a0 \"wishlist\": {\r\n\u00a0 \u00a0 \u00a0 \"name\": \"Skyline Chili T-Shirt\"\r\n\u00a0 \u00a0 }\r\n\u00a0 },\r\n\u00a0 {\r\n\u00a0 \u00a0 \"wishlist\": {\r\n\u00a0 \u00a0 \u00a0 \"name\": \"Joey Votto jersey\"\r\n\u00a0 \u00a0 }\r\n\u00a0 }\r\n]<\/pre>\n
Almost there. Try imagining this array of objects being serialized to a C# <\/span>List<WishlistItem><\/span><\/em>. It wouldn\u2019t quite work, because the objects are nested with the collection name. So, I\u2019ve gotten into a habit of aliasing the collections, like this:<\/span><\/p>\n
SELECT w.* FROM demo._default.wishlist w;<\/pre>\n
Which produces the result:<\/span><\/p>\n
[\r\n\u00a0 {\r\n\u00a0 \u00a0 \"name\": \"Skyline Chili T-Shirt\"\r\n\u00a0 },\r\n\u00a0 {\r\n\u00a0 \u00a0 \"name\": \"Joey Votto jersey\"\r\n\u00a0 }\r\n]<\/pre>\n
Looking good, but there\u2019s still something missing. Where are those GUIDs that we used for the document keys? Couchbase doesn\u2019t store them as data; it stores them as <\/span>metadata<\/b>. SQL++ provides the <\/span>META()<\/span><\/em> function to query metadata. Use <\/span>META().id<\/span><\/em> like this:<\/span><\/p>\n
SELECT META(w).id, w.* FROM demo._default.wishlist w;<\/pre>\n
And that finally gives us a result of:<\/span><\/p>\n
[\r\n\u00a0 {\r\n\u00a0 \u00a0 \"id\": \"2dab198b-1836-4409-9bdf-17275a2b2462\",\r\n\u00a0 \u00a0 \"name\": \"Skyline Chili T-Shirt\"\r\n\u00a0 },\r\n\u00a0 {\r\n\u00a0 \u00a0 \"id\": \"31c9cc33-8dfe-440c-bd1b-bb038939d2e0\",\r\n\u00a0 \u00a0 \"name\": \"Joey Votto jersey\"\r\n\u00a0 }\r\n]<\/pre>\n
This will serialize nicely into <\/span>WishlistItem<\/span><\/em> objects, using the class created in part 1.<\/span><\/p>\n
Using SQL++ in ASP.NET Core<\/b><\/h2>\n
Let\u2019s get that SQL++ query we just wrote into an ASP.NET Core endpoint.<\/span><\/p>\n
In GiftsController<\/em>, create a endpoint called GetAll<\/em>:<\/span><\/p>\n
[HttpGet]\r\n[Route(\"api\/getall\")]\r\npublic async Task<IActionResult> GetAll()\r\n{\r\n\r\n}<\/pre>\n
To execute SQL++, we need to get an object of type <\/span>Cluster<\/span>. SQL++ runs at the cluster level (not bucket, or scope, or collection, since it may need to <\/span>JOIN<\/span>\/<\/span>UNION<\/span> between them). We could go back and add <\/span>ClusterProvider<\/span><\/a> as a constructor parameter. If this endpoint was going to only work with SQL++, that would be a good idea. However, let\u2019s stick with what we created in part 1. We have an object of type <\/span>BucketProvider<\/span><\/em>. From that object, you can get a object of type <\/span>Cluster<\/span>:<\/span><\/p>\n
[HttpGet]\r\n[Route(\"api\/getall\")]\r\npublic async Task<IActionResult> GetAll()\r\n{\r\n  var bucket = await _bucketProvider.GetBucketAsync(\"demo\");\r\n  var cluster = bucket.Cluster;\r\n\r\n  \/\/ ... snip ...\r\n}<\/pre>\n
A <\/span>cluster<\/span> object is how ASP.NET Core will interact with a Couchbase cluster in a variety of ways. For now, we\u2019re interested in its <\/span>QueryAsync<T><\/span><\/em> method:<\/span><\/p>\n
[HttpGet]\r\n[Route(\"api\/getall\")]\r\npublic async Task<IActionResult> GetAll()\r\n{\r\n  var bucket = await _bucketProvider.GetBucketAsync(\"demo\");\r\n  var cluster = bucket.Cluster;\r\n\r\n  var result = await cluster.QueryAsync<WishlistItem>(\r\n    \"SELECT META(w).id, w.* FROM demo._default.wishlist w;\"\r\n  );\r\n\r\n  return Ok(result);\r\n}<\/pre>\n
Make sure you have the following <\/span>using<\/span> statements at the top of your GiftsController.cs<\/em> file:<\/span><\/p>\n
using AspNetCoreTutorial.Models;\r\nusing Microsoft.AspNetCore.Mvc;\r\nusing Couchbase;\r\nusing Couchbase.Query;\r\nusing Couchbase.Extensions.DependencyInjection;<\/pre>\n
One more thing to note. When executing SQL++, there are a number of <\/span>(scan) consistency options<\/span><\/a>. The default is <\/span>ScanConsistency.NotBounded<\/span><\/em>. This setting means that the query engine will <\/span>not<\/b> wait on indexes to finish updating before returning results. This is the most performant option. However, in some situations, you will want stronger index consistency. Couchbase provides <\/span>RequestPlus<\/span><\/em> and <\/span>AtPlus<\/span><\/em>.<\/span><\/p>\n
Try out the ASP.NET Core Endpoint<\/b><\/h2>\n
From Visual Studio, Ctrl+F5<\/strong> will start the app. You should see an OpenAPI \/ Swagger page in your browser.<\/span><\/p>\n
\"ASP.NET<\/p>\n
(Ignore WeatherForecast<\/em>, that just came with the Visual Studio template).<\/span><\/p>\n
Click on the endpoint to try it out. There are no parameters to specify, so just click Execute<\/strong>.<\/span><\/p>\n
\"Testing<\/p>\n
You now have the “R” of CRUD in place.<\/span><\/p>\n
What\u2019s next?<\/b><\/h2>\n
The ASP.NET Core project is connected to Couchbase Capella, and it is reading data via SQL++.<\/span><\/p>\n
In the next blog post, we\u2019ll create another “read” endpoint. Instead of SQL++, we\u2019ll look at another, faster way that data can be accessed and read.<\/span><\/p>\n
In the meantime, you should:<\/span><\/p>\n