Entity Framework: combining exact and wildcard searching conditional on search term

Question

I'm creating a query to search the db using EF. TdsDb being the EF context.

string searchValue = "Widget";
TdsDb tdsDb = new TdsDb();
IQueryable<Counterparty> counterparties;

I can do exact match:

counterparties = tdsDb.Counterparties.Where(x => x.CounterpartyName == searchValue);

or wildcard match:

counterparties = tdsDb.Counterparties.Where(x => x.CounterpartyName.Contains(searchValue));

But I want to be able to do both i.e. (psudo code)

counterparties = tdsDb.Counterparties.Where(x => 
          if (searchValue.EndsWith("%")) 
                 {
                      if (searchValue.StartsWith("%"))
                          {x.CounterpartyName.Contains(searchValue)}
                      else 
                          {x.CounterpartyName.StartsWith(searchValue)}
                 }   
          else
                 {x => x.CounterpartyName == searchValue}
      );

Now clearly I can't put an if statement in the where clause like that. But I also can't duplicate the queries: shown here they are hugely dumbed down. The production query is far longer, so having multiple versions of the same long query that vary on only one clause seems very unhealthy and unmaintainable.

Any ideas?

Answer 1

You should be able to use the ternary operator:

bool startsWithWildCard = searchValue.StartsWith("%");
bool endsWithWildCard = searchValue.EndsWith("%");

counterparties = tdsDb.Counterparties.Where(x => 
      endsWithWildCard
          ? (startsWithWildCard
              ?  x.CounterpartyName.Contains(searchValue)
              : (x.CounterpartyName.StartsWith(searchValue)))
          : (x.CounterpartyName == searchValue));

Did you test btw if querying by a searchValue that has an % at the beginning or end works as you expect? It might be possible that % will be escaped as a character to query for because StartsWith and Contains will prepend/append % wildcards to the generated SQL search term anyway. In that case you need to cut off the % from the searchValue before you pass it into StartsWith or Contains.