This ASP.NET Core CRUD series is coming to end. We’ve covered setup (part 1), reading with SQL++ (part 2), reading with key-value (part 3), and creating/updating (part 4). In this last post, we’ll look at the D in CRUD: deleting.

Deleting with SQL++ or Key-Value

Hopefully you’re noticing a pattern. Just as with reading, creating, and updating, you’ve got multiple paths for deleting. You can use a SQL++ DELETE statement:

Or you can use a key-value delete operation (aka “Remove“):

And as before, here are the very similar guidelines to help you decide which one to use:

Use case Key-value? Why or why not?
Delete a user with key “73892” Yes Direct access
Delete a group of users with keys “73892”, “47212”, and “90491” Yes This may require multiple key-value operations, but this still may be faster than using a SQL DELETE …​ WHERE …​ IN query.
Delete every user from “Ohio” No User’s state is likely a “secondary” attribute, not a key (multiple users can be from Ohio). This is a good use case for a SQL++ DELETE

A Delete endpoint for ASP.NET Core

With that in mind, create a delete endpoint like so:

Try this endpoint with OpenAPI / Swagger, and it will behave how you’d expect.

Swagger API view of Couchbase ASP.NET endpoints

Should I really delete?

In many use cases, you may not want to actually 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.

It’s also a great opportunity to show the flexibility of a JSON NoSQL database.

Soft-deleting

Let’s 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.

In a relational database, this would likely require an ALTER. 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’t need to tell the database anything about a new field.

Let’s use the subdocument API to add a “deleted” field to the document. Sub-document means that we will only operate on a portion of the JSON, and leave the rest alone.

(Make sure using Couchbase.KeyValue; is at the top of your GiftsController file.)

This code sends a command to Couchbase: for the document with such-and-such ID, upsert a field called “deleted” and give it the current date/time as a value.

Notice that with sub-document, we didn’t have to first load the existing document, and we didn’t have to send the entire modified document back over the wire.

Soft-deleted data

The end result will be a document that looks like this:

The other documents in my wishlist do not have a deleted field. They still look like:

and

Note that they don’t have a “deleted”: null field; they don’t have a deleted field at all.

Soft-deleted SELECT

The data is marked as deleted, but it’s still in the database. We need to modify the GetAll endpoint (see part 2 for more about GetAll) to take this into account:

I’ve introduced some more SQL++ syntax here: MISSING. This is a concept that doesn’t exist in relational databases. In relational, any column specified in the query must be defined and must have a value (even if it’s null). With a JSON NoSQL document database, there is no such constraint.

Improving the index

One last point to discuss is indexing. Back in part 2, we created a primary index just to get started. However, that index will rarely be the most efficient. Creating and tuning indexes is a deep topic, just as it is in the relational database world.

Fortunately, Couchbase Capella has a built-in Advise tool that can recommend better indexes. Just click Advise in the Query Workbench (or you can use ADVISE syntax).

Index adviser recommendations in Couchbase

In this case, it gives the following recommendation:

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.

The end of CRUD

We’ve reached the end of creating a very simple ASP.NET Core CRUD application with Couchbase. The final API surface looks like:

Full API example of Couchbase ASP.NET app

Here is a breakdown of the concepts in this series, with links to documentation to dive deeper:

The complete source code for this series is available on GitHub.

What’s next?

Sign up for a Capella free-trial. The Couchbase Capella DBaaS is the easiest way to get started with Couchbase, and no credit card is required.

Check out the Couchbase Playground for .NET examples that you can run right in the browser.

Join the Couchbase Discord to ask questions and exchange comments with Couchbase engineers and with other members of the Couchbase community.

Author

Author

Posted by Matthew Groves

Matthew D. Groves is a guy who loves to code. It doesn't matter if it's C#, jQuery, or PHP: he'll submit pull requests for anything. He has been coding professionally ever since he wrote a QuickBASIC point-of-sale app for his parent's pizza shop back in the 90s. He currently works as a Senior Product Marketing Manager for Couchbase. His free time is spent with his family, watching the Reds, and getting involved in the developer community. He is the author of AOP in .NET, Pro Microservices in .NET, a Pluralsight author, and a Microsoft MVP.

Leave a reply