0

I am working on the search using LINQ in C#.

Basically in SQL, it is

SELECT *
FROM myDB
WHERE searchTerm IN (col1, col2, . . . );

As I cannot find a way to do it in LINQ, this is what I did.

var v = (from item in myDB
    where item.COL1.Contains(searchTerm) 
    || item.COL2.Contains(searchTerm) 
    || item.COL3.Contains(searchTerm) 
    ...
    select item);

Is there smarter way to do it?

Petronius
  • 428
  • 4
  • 12
  • Those are not the same. Your SQL will only find places where the search term matches one of the columns exactly, where your LINQ will find places where any of the columns contain the search term. Which behavior do you want? – StriplingWarrior May 31 '22 at 16:03
  • something like `new[] {item.COL1, item.COL2, item.COL3}.Contains(searchTerm)`? Or just `item.COL1 == searchTerm || ...` – juharr May 31 '22 at 16:08
  • @StriplingWarrior Thank you. I thought "IN" acts like "LIKE". I need the latter behavior. – Petronius Jun 01 '22 at 11:42
  • @Petronius: then use my answer, but replace `== searchTerm` with `.Contains(searchTerm)` – StriplingWarrior Jun 04 '22 at 22:42

1 Answers1

1

The equivalent of your SQL code would be:

    from item in myDB
    where item.COL1 == searchTerm
    || item.COL2 == searchTerm 
    || item.COL3 == searchTerm
    ...
    select item

Or:

myDB.Where(item => item.COL1 == searchTerm
    || item.COL2 == searchTerm
    || item.COL3 == searchTerm
    ...)

Since you're working with a finite and hard-coded list of columns, it's probably fine leaving a little repetition in the code this way. If you needed the list of columns to be dynamic, you could build a list of criteria and use code like this to "OR" those criteria together.

StriplingWarrior
  • 151,543
  • 27
  • 246
  • 315
  • I think this even makes a better query plan. I didn't check but I bet that the `IN` version can't do anything with indexes. – Gert Arnold May 31 '22 at 18:47