Array Indexes and Linq

I’ve created a secondary index on an array and am able to query with it successfully. However, the query identifier is dependent on the the identifier defined in the index. For example, given this index:

CREATE INDEX Test ON default
((distinct array x.id for x in someArray end))
WHERE (type = ‘someType’)

The query must identify the array element as ‘x’:

SELECT * FROM default
WHERE type = ‘someType’
AND ANY x IN someArray SATISFIES (x.id = 1) END;

But when I use Linq2Couchbase, the Linq provider generates a query with different identifiers (Extent1, Extent2, etc). This prevents the array index from being used and results in an error: “No primary index on keyspace default… Use CREATE PRIMARY INDEX to create one”

Does Linq2Couchbase support these indexes? Is there a way to force the name of the identifer in the N1QL query that is generated by the Linq provider?

@drumboog -

I don’t believe the current version of the provider on NuGet supports that particular type of query. There is a commit on github that supports index hints, but I am not sure if an index hint will help here (perhaps @geraldss can confirm?).

One possible work around would be to create the index using the identifiers provided by the Linq provider in your CREATE Index statement.

-Jeff

1 Like

@jmorris, that workaround would be the next thing to try.

1 Like

@geraldss

That workaround will work, but it will only work well if you are indexing for that specific query only. The numbering of the extents will tend to change if you run other queries or if you modify the structure of the existing query. Also, the numbering pattern isn’t guaranteed to remain the same after upgrades to new versions of the LINQ provider.

I think if this is something we want to continue to support, we’ll need to look for some way to control the expression name programatically. I’ll have to look and see if there is a logical way to do this. I don’t think the UseIndex method will help, I suspect the query engine will refuse to use the index because it doesn’t think the predicate matches.

Brant

Hi @btburnett3,

I am not familiar with the details of LINQ, but ideally, the query variables would match the variables used programmatically by the developer. This would also help with debugging and analysis.

@geraldss

Unfortunately, using the query variables doesn’t really work. In the early days we used that approach. The problem is that each clause in a LINQ query (especially one built with the functional .Where(p => …) syntax) will let you reuse the same name. But N1QL tends to dislike this and throw errors. So we went with an approach that guarantees uniqueness, much like many of the other LINQ providers like Entity Framework.

Got it, thanks.

Gerald

@geraldss

I’d like to take a shot a coming up with a solution for this issue. Would you mind filing an issue at https://github.com/couchbaselabs/Linq2Couchbase/issues? That way you’ll show as the reporter and will be notified as we make progress. If you prefer not to, let me know and I’d be glad to do it for you.

Brant

I will file an issue. I was going to suggest the following. Have two modes. The default mode is to auto-generate the variables, as you do currently. The second mode is to use application variables, which means the user is explicitly choosing that mode and is taking responsibility to use unique variables.

@geraldss,

I had a conversation with @jmorris, and we came up with an approach we’d like to try. It is pretty simple and straightforward, and only needs to be applied when dealing with array indexes.

I’ve pushed up a work in progress changeset, and I was hoping to get your opinion on how it meets your needs:
https://github.com/brantburnett/Linq2Couchbase/tree/extentname

In particular, you might want to look at the example unit tests, which show the the LINQ syntax that is proposed as well as the resulting query:
https://github.com/brantburnett/Linq2Couchbase/blob/extentname/Src/Couchbase.Linq.UnitTests/QueryGeneration/ArrayOperatorTests.cs#L43-L48

Thanks in advance for your feedback.

Brant

1 Like

Variable names generated from the the linq query, post parsing, on converting to a corresponding N1QL query are not necessarily unique. Hence instead of using the item name itself, in order to ensure uniqueness, adhere to convention and to prevent N1QL injection attacks (http://blog.couchbase.com/2015/september/couchbase-and-n1ql-security-centeredgesoftware) auto generation of extent names (starting with Extent1) was added to Linq2Couchbase.

When using array indexes, since linq2couchbase previously auto generated aliases (extent names), the final n1ql query never matched the index and the index wasn’t chosen.

The above fix addresses that issue by allowing the user to manually define aliases (extent in linq terms) and hence the user can write a query that uses the index. @drumboog, Could you let us know if the above fix works for you ?

Thanks

It looks like what’s outlined in the unit test would solve the problem. I can’t verify in depth as I get a compile time exception when I pull down the code base from the link; doesn’t look like it can find the ExtentName extension method. Am I missing something?

@drumboog

Make sure you import Couchbase.Linq.Extensions

Brant

Issue created for tracking: https://github.com/couchbaselabs/Linq2Couchbase/issues/153

Pull request with potential fix: https://github.com/couchbaselabs/Linq2Couchbase/pull/154

@drumboog

The new feature to support array indexes has been merged, and should be available in the next Linq2Couchbase release. If you want to use it sooner, feel free to clone the master branch from GitHub and build it yourself.

Brant

1 Like