Autocomplete extender 'extended' across entire data table.

Question

Hello all

I have a request to create an auto complete that will search an data table. Is this achieveable quickly and simply or is it a case of writing a reasonable amount of code?

Originally, I have been using a webservice and linq to point at a single column's worth of data (IDDesc) and pull back the list of products:

Product.FinalProductsDataContext dbac = new Product.FinalProductsDataContext();

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

However, if I wish the autocomplete to look at all the columns, is it a case of repeating similar LINQ statements for each of the columns contained within the datatable or is there a 'quick fix'?

I personally don't think this is an ideal scenario but it is a request I must work towards.

Any help or advice, greatly appreciated.

Answer 1

Rather than trying to solve this entirely with LINQ (and repeating all those statements for each column in the table, as well as hitting the database repeatedly), I think I'd look to put something in the database to do the heavy lifting here.

You could create a view that takes the fields from the table and amalgamates them into one column e.g.

CREATE VIEW [dbo].[ProductView]
AS
SELECT     CAST(ProductName AS NVARCHAR(50)) AS 'ProductColumn'
FROM         dbo.Products
UNION
SELECT CAST(SupplierName AS NVARCHAR(50))
FROM dbo.Products
UNION
...

which if you added the view to your context would then allow you to modify your existing LINQ query and point it at that view e.g.:

Product.FinalProductsDataContext dbac = new Product.FinalProductsDataContext();

return dbac.ProductView
    .Where(r => r.Account== HttpContext.Current.Session["AccountKey"].ToString() && r.ProductColumn.Contains(prefixText))
    .Distinct()
    .OrderBy(r => r.ProductColumn)
    .Select(r => r.ProductColumn)
    .Take(count)
    .ToArray();