1

I have a web part, and I am trying to populate a drop-down control with the unique/distinct values from a particular field in a list.

Unfortunately, due to the nature of the system, it is a text field, so there is no other definitive source to get the data values (i.e., if it were a choice field, I could get the field definition and just get the values from there), and I am using the chosen value of the drop-down in a subsequent CAML query, so the values must be accurate to what is present on the list items. Currently the list has arpprox. 4K items, but it is (and will continue) growing slowly.

And, it's part of a sandbox solution, so it is restricted by the user code service time limit - and it's timing out more often than not. In my dev environment I stepped through the code in debug, and it seems like the line of LINQ where I actually get the distinct values is the most time consuming, and I then commented out the call to this method entirely, and the timeouts stop, so I am fairly certain this is where the problem is.

Here's my code:

private void AddUniqueValues(SPList list, SPField filterField, DropDownList dropDownControl)
{
    SPQuery query = new SPQuery();
    query.ViewFields = string.Format("<FieldRef Name='{0}' />", filterField.InternalName);
    query.ViewFieldsOnly = true;

    SPListItemCollection results = list.GetItems(query); // retrieves ~4K items 

    List<string> uniqueValues = results.Cast<SPListItem>().Select(item => item[filterField.Id].ToString()).Distinct().ToList(); // this takes too long with 4K items 

    uniqueValues.Sort();

    dropDownControl.Items.AddRange(uniqueValues.Select(itm => new ListItem(itm)).ToArray());
}

As far as I am aware, there's no way to get "distinct" values directly in a CAML query, so how can I do this more quickly? Is there a way to restructure the LINQ to run faster?

Is there an easy/fast way to do this from the client side? (REST would be preferred, but I'd do JSOM if necessary).

  • 1
    I would suggest you instead of drop down, you should opt for Auto complete text box, and while typing the user can get suggestion.. You can keep it till 3 characters.. it will help in performance – Arsalan Adam Khatri Nov 30 '16 at 14:31
  • @ArsalanAdamKhatri How would that work? I still need to know the existing field values, as those are the valid choices, and the only way to get those values is to query the list. Are you suggesting making some kind of client-side query to the list after the third keystroke, just to see if there are any items whose value for that field starts with those three characters? – Dylan Cristy Nov 30 '16 at 14:38
  • exactly, in auto complete text box you don't prefil items and on third key stroke a rest call, client-side query will get the items that start with those three letters. After that you can return distinct items which will work faster I guess. – Arsalan Adam Khatri Nov 30 '16 at 14:44
  • @Arsalan I'd upvote that if it were made into an answer. Using JQuery UI autocomplete it would be not too difficult to implement. It would still likely perform horribly but better and less complex then the sandbox code. – Derek Gusoff Nov 30 '16 at 14:46
  • @DerekGusoff done ! – Arsalan Adam Khatri Nov 30 '16 at 14:47
1

I would suggest you instead of drop down, you should opt for Auto complete text box, and while typing the user can get suggestion.. You can keep it till 3 characters.. it will help in performance

As Derek suggested, using JQuery UI Autocomplete it won't be that difficult to implement.

  • 1
    Thanks for your suggestion, but I'm going with another technique that was posted as an answer on S.O. Wanted to let you know, though, that a technique that you posted in another answer here using a DataTable/DataView conversion actually turned out to be the fastest. Much, much faster than LINQ. – Dylan Cristy Dec 1 '16 at 15:30
1

I asked this same question over on StackOverflow (because it's *cough* technically off-topic here... shhh...) and I am going with one of the answers over there, but I thought I'd post it over here too since it is a good technique.

To quote that answer:

An alternative approach would be to perform multiple CAML queries against SharePoint; this would result in one query per unique value (plus one final query that returns no results).

  1. Make sure your list has column indexing applied to the field whose values you want to enumerate.
  2. In your initial CAML query, sort by the field you want to enumerate and impose a row limit of one item.
  3. Get the value of the field from the item returned by that query and add it to your collection of unique values.
  4. Query the list again, sorting by the field and imposing a row limit of 1, but this time add a filter condition such that it only retrieves items where the field value is greater than the field value you just detected.
  5. Add the value of the field in the returned item to your collection of unique values.
  6. Repeat steps 4 and 5 until the query returns an empty result set, at which point your collection of unique values should contain all current values of the field (assuming more haven't been added since you started).

That technique deals nicely with the case where there may be more items than the list view threshold, since you only retrieve one item at a time, and not all the items.

And, as an interesting side note, there is another technique using a DataTable / DataView conversion that was mentioned in this answer here on SP.SE that actually turned out to be the fastest way to get distinct field values from an SPListItemCollection (at least from my testing).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.