Autocomplete Extender not sorting alphabetically

Question

Hello everyone.

I am using LINQ within a webservice that creates an autocomplete function on a text box. I've got it to work but unfortunately the results are not being populated in the order I expect, for example, if I was search for drinks beginning with "carl" I'd expected "carling" first and then "carlsberg" but this is not the case.

The webservice being used is:

public string[] GetProdDesSearch(string prefixText, int count)
    {

        try
        {
            ReportingService.ProductsDataContext dbac = new  ReportingService.FinalProductsDataContext();

            return dbac.FINALPRODUCTSNEWDEMOs
                .Where(r => r.MemberId == HttpContext.Current.Session["MemberKey"].ToString() && r.IDDesc.Contains(prefixText))
                .OrderBy(r => r.UnitDescription)
                .Select(r => r.IDDesc)
                .Distinct()
                .Take(count)
                .ToArray();

        }

        catch (Exception)
        {
            return null;
        }
    }

    }

The .net detail is as follows:

 <asp:TextBox ID="tbxProdAC" runat="server"  
    style="z-index: 1; left: 200px; top: 460px; position: absolute; height: 20px; width: 345px;" 
    CssClass="completionList2" AutoPostBack="True" 
     ontextchanged="tbxProdAC_TextChanged"></asp:TextBox>   


<cc1:AutoCompleteExtender ID="tbxProdAC_AutoCompleteExtender" runat="server" 
    DelimiterCharacters="" Enabled="True" 
    ServicePath="~/Reporting/GetProd.asmx" 
    ServiceMethod="GetProdDesSearch" 
    TargetControlID="tbxProdAC" 
    CompletionInterval="50" CompletionSetCount="50" 
    MinimumPrefixLength="3"
    onclientpopulating="ShowImage"
    onclientpopulated="HideImage" 
    CompletionListCssClass="completionList2">
</cc1:AutoCompleteExtender>

.completionList2 {font-family: Trebuchet MS;font-size:11px; border:solid 1px #444444;margin:0px;padding:2px;height: 395px;
            overflow:auto; background-color:White;
          z-index: 1;
          left: 200px;
          top: 310px;
          position: absolute;
          width: 1496px;

      }

If someone can point out the error in my ways, I would be extremely grateful.

Answer 1

You're sorting by UnitDescription but both the constraint on prefixText and the finally selected item is IDDesc. It's not obvious which you should be using, but I think you should be consistent - either order by IDDesc, or make your Contains and Select calls use UnitDescription too.

Answer 2

I've found the answer.

The distinct should be placed BEFORE the orderby in my LINQ query within the webservice:

return dbac.FINALPRODUCTSNEWDEMOs
                .Where(r => r.MemberId == HttpContext.Current.Session["MemberKey"].ToString() && r.IDDesc.Contains(prefixText))
                .Distinct()
                .OrderBy(r => r.IDDesc)
                .Select(r => r.IDDesc)
                .Take(count)
                .ToArray();