Secondary index not working for Linq

I have a “Client” table that has a string field called Name. I have created an index that seems to work vary quickly when I execute the N1QL statement 'select * from Ngage data where type=‘Client’ and name = ‘Some Client Name’.

The index creation looks like this:
CREATE INDEX ngage_client_name ON Ngage(name) WHERE (type = “Client”)

When it comes to me using a Linq call like this:
var query = from client in CacheProvider.BucketContext.Query()
where client.Name == n
select client;

The call takes forever. I have to assume that the parameter names described in the index create are not the same in the N1QL created. Can anyone please help me figure out how I should create the index to work with Linq?

With indexes in N1QL, I believe the predicate needs to match exactly to select the index. Since you indicate type client and name, your index will probably require both of those.

As you point out though, after this goes through LINQ, the statement may be different. There are a few options for observing what’s being done underneath. You can check with the REST API against the query service for completed requests. Or, you can just maybe write a small program and crank up the log level to see the statements being issued.

After that, maybe have a look at Create the Right Index, Get the Right Performance and/or post to the query category here on the forums.

Version 6.5, just released last week, has a feature called the index adviser that you may want to check out as well.

It is Index Advisor.

Your linq query must have predicate AND type = “Client” to use the ngage_client_name index


One other thing to point out, since it looks like you’re using Linq2Couchbase (although I would expect to see Query<Client>(…) instead of Query(…), but maybe the forum software ate your angle brackets)

@vsr1 said that you must add “type = ‘Client’” to your query.

Linq2Couchbase will add this for you automatically if you use a DocumentTypeFilter attribute on the class.

1 Like


After adding the DocumentTypeFilter attribute to the POCO classes it appears that the index is being used because the lag in the retrieval appears to have disappeared. Thank you for the suggestion.