CODEWITHSUNDEEP
c#sql-serverlinq
Ian Vink
Ian Vink

Reputation: 68830

Linq with multiple contains

In our UI the users can free text a search that is applied to a number of fields.

            q = q.Where(p => p.Account.Contains(query)
                             || p.AccountName.Contains(query)
                             || p.AccountAKA.Contains(query)
                             || p.AccountRef.Contains(query));

This translates into SQL. Is there a more optimal way to querying, as this is slow.

There are about 20,000 rows. Database disk size doesn't matter, memory usage does.

Upvotes: 1

Views: 1266

Answers (2)

T McKeown
T McKeown

Reputation: 12857

A better solution would be to change your data model and use a Description column that has ALL the account name info in it so you can do your query against a single column. Updates to the record result in an update to this Description column.

Upvotes: 1

krivtom
krivtom

Reputation: 24916

Since all these are text fields each of the values translates to Account LIKE '%'+ query+ '%'. All queries with wildcards on both sides will be slow, unfortunately there is not too much that can be done.

Maybe it is possible to use StartsWith() instead of Contains()? This would translate to `LIKE query + '%' which is generally much faster?

Upvotes: 1

Related Questions