DELETE FROM

[monodop]

Does this library support linq for DELETE FROM queries? I am having a hard time finding anything in the documentation or apis for this.

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/delete.html

[brantburnett]

Due to the nature of LINQ being a query system, not a mutation system, we don't support UPDATE or DELETE queries. The library does support deletes via change tracking: https://github.com/couchbaselabs/Linq2Couchbase/blob/master/docs/change-tracking.md

[monodop]

I'd really have to disagree with that logic. There's no technical reason you can't use linq to perform a DELETE FROM operation that's provided with a WHERE clause. The linq expression can map very nicely to the WHERE clause.

For example:

context.Delete<MyDocument>(document => document.type == "myDocument" && document.somekey == "some value");

would map to:

DELETE FROM mydb WHERE type == 'myDocument' AND somekey == 'some value'

This is a linq expression used to "query" the things that I want to delete. Why should I first have to pull them all and delete them one at a time?

[brantburnett]

@monodop

Thanks for the feedback. Interesting, I think that you're both right and wrong, depending on how you look at it.

First, let me drop a highly pedantic "well actually". Note that I'm just pointing this out for clarity of conversation, I really don't want to be the "well actually" guy.

The example you provided isn't, technically speaking, LINQ. LINQ is Language Integrated Queries, which is the query language build around IQueryable extension functions like Where, Select, GroupBy, and their C# and VB specific flavors. It is truly only for querying data, not for generating mutation queries. LINQ is a layer built on top of expression trees, which is what your example included, but there is no way in official .NET LINQ to make a query that performs a mutation.

Okay, now for how you're right. You're 100% correct that we could generate a DELETE query using the syntax you offered. Primarily we haven't offered a feature like this because it hasn't been terribly high demand. Personally, my implementations almost always have business logic involved where I need to load the object and validate things before deleting it. Additionally, doing a bulk delete via query is only recommend in Couchbase for specific use cases, because you lose features like CAS that you get via the key/value API.

It's also not very common in other similar libraries, such as Entity Framework. I'm not certain why that is, probably accepting a performance penalty in exchange for cleaner separation of concerns or something.

I'd be very interested to hear your specific use case. That might help me frame the best approach for you and for the library.

[monodop]

The example you provided isn't, technically speaking, LINQ. LINQ is Language Integrated Queries, which is the query language build around IQueryable extension functions like Where, Select, GroupBy, and their C# and VB specific flavors. It is truly only for querying data, not for generating mutation queries. LINQ is a layer built on top of expression trees, which is what your example included, but there is no way in official .NET LINQ to make a query that performs a mutation.

I mean I get that it's not a full IQueryable query, but to me, LINQ is about anything that uses an Expression. In my codebase, Expression<Func<...>> are far more common than full IQueryable queries, which is why I just refer to it all as LINQ. LINQ is, after all, what makes passing said expressions possible. But either way this doesn't really matter much, sounds like we're on the same page anyway. Sorry for any confusion.

Okay, now for how you're right. You're 100% correct that we could generate a DELETE query using the syntax you offered. Primarily we haven't offered a feature like this because it hasn't been terribly high demand. Personally, my implementations almost always have business logic involved where I need to load the object and validate things before deleting it. Additionally, doing a bulk delete via query is only recommend in Couchbase for specific use cases, because you lose features like CAS that you get via the key/value API.

It's also not very common in other similar libraries, such as Entity Framework. I'm not certain why that is, probably accepting a performance penalty in exchange for cleaner separation of concerns or something.

I'd be very interested to hear your specific use case. That might help me frame the best approach for you and for the library.

I mean yeah, that's kind of what I have to do for now as a workaround: pull x records at a time and then delete them. It works, it's just slower and more complex of a solution than I'd like. My requirement is basically to just clean up documents that are older than a specific date - there's hundreds of thousands of them and they're not needed after a period of time. I get that you can use document expirations, but doing so is hard to change after the fact without rewriting all of the documents.

At the end of the day, querying and deleting works. In fact it might be required in the future anyways if I decide I want to delete related documents as well. But being able to use DELETE FROM would be so handy for quick cleanup jobs like this.

[brantburnett]

What about just handwriting the DELETE FROM query to run against IBucket.QueryAsync?

[monodop]

Yeah, that was another option I considered. In general I don't like handwriting queries because I like the type safety of linq and the ability to transform the queries before they get to couchbase. In this specific case it'd probably be fine, just I would have an allergic reaction to doing it 🙂