{"id":13618,"date":"2022-08-02T09:46:48","date_gmt":"2022-08-02T16:46:48","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/?p=13618"},"modified":"2025-06-13T20:56:42","modified_gmt":"2025-06-14T03:56:42","slug":"asp-net-crud-nosql-part-5","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/ko\/asp-net-crud-nosql-part-5\/","title":{"rendered":"NoSQL\uc744 \uc0ac\uc6a9\ud55c ASP.NET Core CRUD: 5\ubd80"},"content":{"rendered":"

This ASP.NET Core CRUD series is coming to end. We\u2019ve covered <\/span>setup (part 1)<\/span><\/a>, <\/span>reading with SQL++ (part 2)<\/span><\/a>, <\/span>reading with key-value (part 3)<\/span><\/a>, and <\/span>creating\/updating (part 4)<\/span><\/a>. In this last post, we\u2019ll look at the D<\/strong> in CRUD<\/strong>: deleting<\/em>.<\/span><\/p>\n

Deleting with SQL++ or Key-Value<\/b><\/h2>\n

Hopefully you\u2019re noticing a pattern. Just as with reading, creating, and updating, you\u2019ve got multiple paths for deleting. You can use a SQL++ <\/span>DELETE<\/span><\/em> statement:<\/span><\/p>\n

DELETE FROM demo._default.wishlist w\r\nWHERE META(w).id = \"1c3de2e7-70ea-4ee2-803b-425bbf6251cb\"<\/pre>\n
Or you can use a key-value delete operation (aka “Remove<\/em>“):<\/span><\/p>\n
await collection.RemoveAsync(\"1c3de2e7-70ea-4ee2-803b-425bbf6251cb\");<\/pre>\n
And as before, here are the very similar guidelines to help you decide which one to use:<\/span><\/p>\n\n\n\n\n\n\n
Use case<\/strong><\/td>\nKey-value?<\/strong><\/td>\nWhy or why not?<\/strong><\/td>\n<\/tr>\n
Delete a user with key “73892”<\/span><\/td>\nYes<\/span><\/td>\nDirect access<\/span><\/td>\n<\/tr>\n
Delete a group of users with keys “73892”, “47212”, and “90491”<\/span><\/td>\nYes<\/span><\/td>\nThis may require multiple key-value operations, but this still may be faster than using a SQL <\/span>DELETE \u2026\u200b WHERE \u2026\u200b IN<\/span><\/em> query.<\/span><\/td>\n<\/tr>\n
Delete every user from “Ohio”<\/span><\/td>\nNo<\/span><\/td>\nUser\u2019s state is likely a “secondary” attribute, not a key (multiple users can be from Ohio). This is a good use case for a SQL++ <\/span>DELETE<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
A Delete endpoint for ASP.NET Core<\/b><\/h2>\n
With that in mind, create a delete endpoint like so:<\/span><\/p>\n
[HttpDelete]\r\n[Route(\"api\/delete\")]\r\npublic async Task<IActionResult> Delete(Guid id)\r\n{\r\n\u00a0 \u00a0 var bucket = await _bucketProvider.GetBucketAsync(\"demo\");\r\n\u00a0 \u00a0 var collection = await bucket.CollectionAsync(\"wishlist\");\r\n\r\n\u00a0 \u00a0 await collection.RemoveAsync(id.ToString());\r\n\r\n\u00a0 \u00a0 return Ok(new { success = true });\r\n}<\/pre>\n
Try this endpoint with OpenAPI \/ Swagger, and it will behave how you\u2019d expect.<\/span><\/p>\n
\"Swagger<\/p>\n
Should I really delete?<\/b><\/h2>\n
In many use cases, you may not want to <\/span>actually<\/b> delete data. You may want to do something called a “soft” delete. This involves moving or marking the data in such a way that it still exists in the database, but it is no longer shown to the end user. This has the benefit of being discoverable, recoverable, and reportable.<\/span><\/p>\n
It\u2019s also a great opportunity to show the flexibility of a JSON NoSQL database.<\/span><\/p>\n
Soft-deleting<\/b><\/h2>\n
Let\u2019s introduce a “deleted” field to wishlist items. This field will contain a date\/time that the item was deleted. If this field exists, the rest of the endpoint should consider this item to be deleted. However, if we need to, we can still query it, report on it, and recover it.<\/span><\/p>\n
In a relational database, this would likely require an <\/span>ALTER<\/span><\/em>. Depending on the size of your data, it could require some downtime, or possibly a lot of NULL values. In a JSON database, we don\u2019t need to tell the database anything about a new field.<\/span><\/p>\n
Let\u2019s use the <\/span>subdocument API<\/span><\/a> to add a “deleted<\/em>” field to the document. Sub-document means that we will only operate on a <\/span>portion<\/b> of the JSON, and leave the rest alone.<\/span><\/p>\n
[HttpDelete]\r\n[Route(\"api\/softDelete\")]\r\npublic async Task<IActionResult> SoftDelete(Guid id)\r\n{\r\n\u00a0 \u00a0 var bucket = await _bucketProvider.GetBucketAsync(\"demo\");\r\n\u00a0 \u00a0 var collection = await bucket.CollectionAsync(\"wishlist\");\r\n\r\n\u00a0 \u00a0 await collection.MutateInAsync(id.ToString(),\r\n\u00a0 \u00a0 \u00a0 \u00a0 options => options.Upsert(\"deleted\", DateTime.Now));\r\n\r\n\u00a0 \u00a0 return Ok(new { success = true });\r\n}<\/pre>\n
(Make sure <\/span>using Couchbase.KeyValue;<\/em><\/span> is at the top of your GiftsController<\/em> file.)<\/span><\/p>\n
This code sends a command to Couchbase: <\/span>for the document with such-and-such ID, upsert a field called “deleted<\/em>” and give it the current date\/time as a value<\/span>.<\/span><\/p>\n
Notice that with sub-document, we didn\u2019t have to first load the existing document, and we didn\u2019t have to send the entire modified document back over the wire.<\/span><\/p>\n
Soft-deleted data<\/b><\/h2>\n
The end result will be a document that looks like this:<\/span><\/p>\n
{\r\n\u00a0 \"name\": \"Digital Photo Frame\",\r\n\u00a0 \"deleted\": \"2022-04-21T11:05:26.1766248-04:00\"\r\n}<\/pre>\n
The other documents in my wishlist do <\/span>not<\/b> have a <\/span>deleted<\/span> field. They still look like:<\/span><\/p>\n
{\r\n\u00a0 \"name\": \"Skyline Chili 2XL T-Shirt\"\r\n}<\/pre>\n
and<\/span><\/p>\n
{\r\n\u00a0 \"name\": \"Joey Votto jersey\"\r\n}<\/pre>\n
Note that they don\u2019t have a <\/span>“deleted”: null<\/span><\/em> field; they don\u2019t have a <\/span>“deleted<\/em>“<\/span> field at all.<\/span><\/p>\n
Soft-deleted SELECT<\/b><\/h2>\n
The data is <\/span>marked<\/b> as deleted, but it\u2019s still in the database. We need to modify the <\/span>GetAll<\/span><\/em> endpoint (<\/span>see part 2 for more about <\/span>GetAll<\/span><\/a>) to take this into account:<\/span><\/p>\n
SELECT META(w).id, w.*\r\nFROM demo._default.wishlist w\r\nWHERE w.deleted IS MISSING<\/pre>\n
I\u2019ve introduced some more SQL++ syntax here: <\/span>MISSING<\/span><\/em>. This is a concept that doesn\u2019t exist in relational databases. In relational, any column specified in the query must be defined and must have a value (even if it\u2019s null). With a JSON NoSQL document database, there is no such constraint.<\/span><\/p>\n
Improving the index<\/b><\/h2>\n
One last point to discuss is indexing. Back in <\/span>part 2<\/span><\/a>, we created a primary index just to get started. However, that index will rarely be the most efficient. <\/span>Creating and tuning indexes<\/span><\/a> is a deep topic, just as it is in the relational database world.<\/span><\/p>\n
Fortunately, Couchbase Capella has a built-in <\/span>Advise<\/strong> tool<\/span><\/a> that can recommend better indexes. Just click Advise<\/strong>\u00a0in the Query Workbench (or you can use <\/span>ADVISE<\/span><\/em> syntax).<\/span><\/p>\n
\"Index<\/p>\n
In this case, it gives the following recommendation:<\/span><\/p>\n
\"recommended_indexes\": {\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \"indexes\": [\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 {\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \"index_statement\": \"CREATE INDEX adv_deletedISMISSING ON `default`:`demo`.`_default`.`wishlist`(`deleted` IS MISSING) WHERE `deleted` IS MISSING\",\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \"index_statement_relative\": \"CREATE INDEX adv_deletedISMISSING ON `wishlist`(`deleted` IS MISSING) WHERE `deleted` IS MISSING\",\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \"keyspace_alias\": \"wishlist_w\",\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \"query_context\": \"default:demo._default\",\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \"recommending_rule\": \"Index keys follow order of predicate types: 2. equality\/null\/missing.\"\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 }\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ]\r\n\u00a0 \u00a0 \u00a0 \u00a0 }<\/pre>\n
In the case of our very small amount of wishlist data, this index is probably not worth creating. However, if we were managing the wishlists of an entire eCommerce site (for example), this index would be a good starting point.<\/span><\/p>\n
The end of CRUD<\/b><\/h2>\n
We\u2019ve reached the end of creating a very simple ASP.NET Core CRUD application with Couchbase. The final API surface looks like:<\/span><\/p>\n
\"Full<\/p>\n
Here is a breakdown of the concepts in this series, with links to documentation to dive deeper:<\/span><\/p>\n