LINQ vs. DataTable.Select - How can I get the same results?

Question

Hi, I'm trying to rewrite a direct (disconnected) DataSet.DataTable.Select to LINQ for a textual search:

string search = "ProductNo like '%" + searchpattern + 
    "%' OR ProductName like '%" + searchpattern + 
    "%' OR Description like '%" + searchpattern +  "%'";

DataSetProducts.sk_productsRow[] dsp = (DataSetProducts.sk_productsRow[])dsProducts.sk_products.Select(search, sort);

This works really fast. However if I re-enact it in LINQ:

productlist = from prds in dsProducts.sk_products.AsEnumerable()
    where (prds.Field<string>("productno").Contains(searchpattern) || 
           prds.Field<string>("productname").Contains(searchpattern) || 
           prds.Field<string>("description").Contains(searchpattern))
    select prds;

It becomes really slow because of the text-operations, and will return far less rows. How can I achieve same results with the same speed? I have only ~1000 records in the table.

Answer 1

As far as why it returns fewer rows, is it a case-sensitivity issue? (i.e. "like" is not case-sensitive by default; "Contains" is, by default)

If you are running within a LINQ to SQL database context, use the System.Data.Linq.SqlClient.SqlMethods.Like operation instead of String.Contains.

Answer 2

The reason you are seeing the difference in speed when you execute the LINQ query versus the disconnected dataset is because the dataset is already in memory while the LINQ query is executed at time of use.

Other than that if you haven't installed the LINQ Visualizer which will show you the query that is actually being executed.

Answer 3

Dumb question, but is the .AsEnumerable() necessary for something? I'm only experienced so far with Linq2Sql, but I would normally treat things as AsQueryable as long as possible in order to avoid the "load dataset, then immediately filter it down again" scenario.

Answer 4

I've never used it myself, but perhaps i4o - Indexed LINQ might help speed things up...