Tutorial :LINQ to DataTable Results Filtering



Question:

I am trying to get this LINQ query to return exact matches if they exist or the "startswith" result if not. Right now it returns both.

example

SearchParam = "mundt" Results = Mundt, Mark | Mundt, Chris | Mundth, Lori

public static DataTable SearchPerson(string SearhParam)      {          var context = new ConnectDataContext(Properties.Settings.Default.ConnectConnectionString);          var myQuery = (from person in context.tblPersons                            where person.LastName.StartsWith(SearhParam) || person.LastName == SearhParam                            orderby person.LastName                            select new { person.PersonID, person.LastName, person.FirstName, person.SSN });            var dataTable = myQuery.CopyLinqToDataTable();            return dataTable;      }  


Solution:1

Try:

        var persons = (from person in context.tblPersons                            orderby person.LastName                            select new { person.PersonID, person.LastName, person.FirstName, person.SSN });          var filteredPersonsList = persons.Where(p=>p.LastName == SearhParam).ToList();          if( filteredPersons.Count == 0 )               filteredPersonList = persons.Where(p=>p.LastName.StartsWith(SearhParam))                         .ToList();          var dataTable = filteredPersonsList.CopyLinqToDataTable();            return dataTable;  

Note: it does 2 hits to the db, one to look for exact match, and one for the the startswith (if it didn't find the first).

Another option, is to grab them both, and then re-filter in memory.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »