How to Check if row exists

Hi Guys,
I am using C# with Couchbase.
Please can someone help on how to check if row exists after executing query .

Right Now I m doing something like this

string query = $"SELECT * FROM _default where Run Date= ‘2023-10-25’;
var queryResult = await inventoryScope.QueryAsync(query);
counter = 0;
await foreach (var row in queryResult)

/// End : Logic to check that row already exists or not
if (counter == 0) // row does not exists
insert row…

The syntax is not working for me
var result = await cluster.QueryAsync<List>(query);
var list = await result.ToListAsync();

Error ToListAsync() is not found

You could just call insert without checking and ignore the error if there is one.

That will insert dulicate row as Run Date is not primary key, I just want to avoid same date data insertion.
Is there something like
await result.count()
so that I can know if that query result has some records in it or not

Frankly, given proper indices what you have written now should work. Without understanding your data structure I can’t really say why it doesn’t. But I can offer some general improvements.

You have a few different options:

SELECT RAW COUNT(*) FROM _default where Run Date= ‘2023-10-25’ will return a single row with the count using .QueryAsync<int>(...). This is probably the most efficient given the right index.

SELECT RAW * FROM _default where Run Date = ‘2023-10-25’ LIMIT 1 is basically what you’re trying to do now, but potentially a bit more efficient since the LIMIT 1 will short circuit after the first match. For this example, I’d recommend await (await scope.QueryAsync<DocType>(...)).AnyAsync() as the simplest way to test if the collection is empty rather than using a foreach loop.

Finally, you could use Linq2Couchbase to provide LINQ functionality for building and executing queries.

1 Like

Making RunDate the primary key would do that.

Please can you provide namespace for .AnyAsync() or .ToListAsync() , I am unable to find in any Couchbase library

I have already used these namespace in code
using Couchbase.Query;
using Couchbase.Query.Couchbase;
using Couchbase.Query.Couchbase.N1QL;
none of them resolve .ToListAsync() or .AnyAsync()

Those helpers are provided by the System.Linq.Async package which should already be installed along with the Couchbase SDK.

1 Like

Looking again at your original post - the code should work provided the document has been indexed.

For n1ql, you need to take into account that when a document is inserted, it will not be found by a query until it is indexed. You can use the option QueryScanConsistency = REQUEST_PLUS to block execution of the SELECT statement until everything inserted before the query was initiated has been indexed.

1 Like

The Previous version of Nudget System.Linq.Async was 4.1.1
Upgrading to System.Linq.Async 6.0.1 solved the issue and now I used

var isAlreadyExist = await (await inventoryScope.QueryAsync(query)).AnyAsync();

if (!isAlreadyExist) // row does not exists
var key = Guid.NewGuid().ToString();
await collection.InsertAsync(key, pool);

Thank you so much @btburnett3

1 Like

Hi @mreiche I am using Guid.NewGuid() as default primary key.
As per requirement I catnt set only Run-Date as Primary key in that case I needed a Composite key as combination of RunDate and 1 more coulumn so I avoided that and just used default Guid key of Couchbase.

Thank you.

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.