Columns in a primary key on a datatable being re-ordered automatically?

Question

I have a table named Permissions which is defined in a dataset named _permissionsSet and it has 3 columns: PermissionGroup, Permission, PermissionLevel (in that order)

I set the primary key on the table to include all 3 columns like this

        DataColumn[] keys = new DataColumn[3];
        keys[0] = _permissionsSet.Permissions.Columns[0];
        keys[1] = _permissionsSet.Permissions.Columns[1];
        keys[2] = _permissionsSet.Permissions.Columns[2];

        _permissionsSet.Permissions.PrimaryKey = keys;

When I look at the values in "keys" they are as expected:

        ? keys
        {System.Data.DataColumn[3]}
        [0]: {PermissionGroup}
        [1]: {Permission}
        [2]: {PermissionLevel}

But when I look at the primary key the order has changed!

        ? _permissionsSet.Permissions.PrimaryKey
        {System.Data.DataColumn[3]}
        [0]: {PermissionGroup}
        [1]: {PermissionLevel}
        [2]: {Permission}

This has obvious implications when using the Rows.find method in my datatable.

Does anyone know why this is happening?

Answer 1

The DataTable.PrimaryKey property returns a simple unsorted array of DataColumn objects. If you want to sort them, take a look at Array.Sort. You'll need to implement an IComparer for the DataColumn type that compares instances by their .Ordinal property.

Answer 2

Maybe look at the constraints collection on your data table. If there's already a unique constraint on those three columns, when you set the PrimaryKey property, the DataTable will reuse that (since logically, the order of columns in a Primary Key, as elsewhere in relational data, is meant to be irrelevant).

If you have no control over that other constraint existing, you'll need to read the Primary Key property after setting it, and re-order your Find arguments appropriately.

Edit

Ideally, someone would write an extension method which would look like find, but take Name/Value pairs, and work out the correct order based on the current value of the PrimaryKey property. There again, it brings up the question of whether searching in the database would be more appropriate.