Multi column index search Microsoft.Isam.Esent

Question

I am facing the following issue: I have an composite index on a database index1 {binaryColumn1, binaryColumn2}. I am using the following to set the index to use:

Api.JetSetCurrentIndex(_session, _table, index1);

to create the key:

Api.MakeKey(_session, _table, binaryValue, MakeKeyGrbit.NewKey);

and than try to perform the search with:

Api.TrySeek(_session, _table, SeekGrbit.SeekEQ);

This works and seek returns true correctly if index1 is only for 1 column. If I have multiple columns and try to search the value for a single column (ex. for binaryColumn1 = {0x01, 0x23}) it always returns false.

How can I search for this one value? (ps. I cannot change the index nor create new ones.) Is this possible?

Thank you

Answer 1

What you did would only work for {0x01, 0x00}. You can't do it with a single call, because the value of the second column will be mixing up the SeekEQ grbit.

You could do a SeekGE, but then you'll need to retrieve the column to make sure the value is actually correct (and not something like {0x22, 0x23}). You'll have to do something like: SetCurrentIndex() MakeKey( ..., binaryValue1, MakeKeyGrbit.NewKey | MakeKeyGrbit.FullColumnStartLimit); // Appends the rest of the search buffer with 0x00's. Seek(GE); MakeKey(... binaryValue1, MakeKeyGrbit.NewKey | MakeKeyGrbit.FullColumnEndLimit); // Appends the rest of the search buffer with 0xff's. Api.JetSetIndexRange(); Then you can use Api.TryMoveNext() to iterate through all rows that have that column equal to binaryValue1.

Also look at the test code included with the project -- the HowDoI.cs file has lots of good examples.

Sorry for the pseudo-code; I don't have the source code handy at the moment.

-martin