Using multiple Contains taking time to load performance issue in Linq

user_9087215 0
Viewed 297

I have more than 600,000 records and 20 columns in a table.

I would like to use a LINQ query and use a function that works as a "LIKE"; So for that I used Contains. It's taking time (or) it throws a timeout expired exception.

So can anyone suggest how to solve this issue? I need to compare more than 4 columns.

var majorAgents = new[] { "iPhone", "Android", "iPad" };


List<Person> lstperson =_context.person.where 
                      (w=>majorAgents.Any(x=>w.personLastName.contains(x))
                      || majorAgents.Any(x=>w.personFirstName.contains(x))
                      ||w.Address.Any(s=>majorAgents.Contains(s.addressProof)))//Address table referenced as a list 
                        .select(s=> new Person{
                             s.personId,
                             s.perosnLastName,
                             s.personFirstName
                    }).ToList();
linq c#
2 Answers

Maybe you should try to run the majorAgents.Any query only once for all the properties having a possibility. In order to do so, you can try concatenating the firstName, lastName and addressProof fields and checking if the majorAgent string is present in this concatenated string anywhere.

Note: I have intentionally modified the Address related condition as it seems to be irrelevant to the task at hand. In original query, it says: w.Address.Any(s=>majorAgents.Contains(s.addressProof) which means 'check in majorAgents if there is any addressProof' whereas it would be more sensible to check if addressProof of any Address has any of the majorAgents. For that I have used w.Address.SelectMany(a => a.addressProof).ToArray() along with string.Join which will give me space separated addressProof strings in which I can look for any majorAgent. In case this is not what is intended, please modify that part of address as it suits the requirement.

So, the modified query to try is:

var majorAgents = new[] { "iPhone", "Android", "iPad" };


List<Person> lstperson =_context.person.where 
                      (w=>majorAgents.Any(x=>(w.personLastName + " " + w.personFirstName + " " +string.Join(" ",w.Address.SelectMany(a => a.addressProof).ToArray())).contains(x))
                      )
                        .select(s=> new Person{
                             s.personId,
                             s.perosnLastName,
                             s.personFirstName
                    }).ToList();

Hope it helps.

user_101036070

Doing contains will be more performatic than making a 'any'. Take the following test:

var majorAgents = new[] { "iPhone", "Android", "iPad" };

List<Person> lstperson=_context.person.where 
     (c=> majorAgents.contains(c.personLastName) || majorAgents.contains(c.personFirstName))
    .select(s=> new Person{
             s.PersonId,
             s.perosnLastName,
             s.personFirstName
}).ToList();
user_44575060
Related