How to Check if row exists

[rahul.sonawane.1985]

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)
{
counter++;
}

/// 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

[mreiche]

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

[rahul.sonawane.1985]

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

[btburnett3]

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.

[mreiche]

@rahul.sonawane.1985

Run Date is not primary key, I just want to avoid same date data insertion.

Making RunDate the primary key would do that.

[rahul.sonawane.1985]

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

[rahul.sonawane.1985]

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()

[btburnett3]

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

System.Linq.Async 6.0.1

[mreiche]

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.

[rahul.sonawane.1985]

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

[rahul.sonawane.1985]

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.