0

I have a list of strings that are search Queries.
I want to see if a string from the database contains anyone of those terms in the Query. I'd like to do this on one line of code, that doesn't make multiple calls to the database. This should work but I want it to be more optimized.

var queries = searchQuery.Trim().Split(' ', StringSplitOptions.RemoveEmptyEntries).Distinct();
var query = context.ReadContext.Divisions.AsQueryable();
queries.ForEach(q => { 
           query = query.Where(d => (d.Company.CompanyCode + "-" + d.Code).Contains(q)); 
});

Is there a function that can do this better or a more optimal way of writing that?

johnny 5
  • 19,893
  • 50
  • 121
  • 195
  • What output are you expecting? Do you want the database rows that contain the queries or the queries for which at least one database row exists? – RePierre Oct 27 '15 at 16:06
  • I'll Select the Id from that later, but that shouldn't matter I just want to know how to do an inverted contains from a list, – johnny 5 Oct 27 '15 at 16:22

1 Answers1

1

There are two issues with your proposed solution:

  1. Most LINQ to SQL providers don't understand string.Contains("xyz") so the provider will either throw an exception or fetch all the data to your machine. The right thing to do is to use SqlMethods.Like as explained in Using contains() in LINQ to SQL

  2. Also, the code you show will check whether the division contains all of the specified strings.

To implement the 'any' behavior you need to construct a custom expression, which will not be possible using plain C#. You would need to look at the System.Linq.Expressions namespace: https://msdn.microsoft.com/en-us/library/system.linq.expressions(v=vs.110).aspx

It is possible, but quite involved.

Community
  • 1
  • 1
lsedlacek
  • 313
  • 2
  • 6
  • 1. Entity framework does understand the contains, the code above works I was just looking to optimize it. 2. I know it filters division but I was refering only to the predicate which works on a single string at a time. 3. The any functionality does work but I don't wan't to do that because it will spawn it as multiple queries. But Plus one for effort – johnny 5 Oct 27 '15 at 17:37