实体框架：根据搜索项组合精确和通配符搜索 [英] Entity Framework: combining exact and wildcard searching conditional on search term

问题描述

我正在使用EF创建一个查询来搜索数据库。 TdsDb是EF上下文。

  string searchValue =Widget; 
 TdsDb tdsDb = new TdsDb（）; 
 IQueryable< Counterparty>对手; 
  

我可以做完全匹配：

 对手方= tdsDb.Counterparties.Where（x => x.CounterpartyName == searchValue）; 
  

或通配符匹配：

 对手方= tdsDb.Counterparties.Where（x => x.CounterpartyName.Contains（searchValue））; 
  

但是我想要能够同时执行ie（psudo代码）

 交易对手= tdsDb.Counterparties.Where（x => 
 if（searchValue.EndsWith（％））
 {
 if（searchValue.StartsWith（％））
 {x.CounterpartyName.Contains（searchValue）} 
 else 
 {x.CounterpartyName.StartsWith（searchValue）} 
} 
 else 
 {x => x.CounterpartyName == searchValue} 
）; 
  

现在显然我不能在where子句中放置if语句。但是我也不能复制这些查询：在这里显示的是它们被大量的抛弃。生产查询的时间要长得多，所以有多个版本的长查询只能在一个子句上变化，这似乎非常不健康，不可维护。

任何想法？

解决方案

您应该可以使用三元运算符：

 code> bool startsWithWildCard = searchValue.StartsWith（％）; 
 bool endsWithWildCard = searchValue.EndsWith（％）; 
 
交易对手= tdsDb.Counterparties.Where（x => 
 endsWithWildCard 
？（startsWithWildCard 
？x.CounterpartyName.Contains（searchValue）
： （x.CounterpartyName.StartsWith（searchValue）））
：（x.CounterpartyName == searchValue））;如果通过 searchValue 在开始或结束时有一个％按照您的期望工作？可能％将被转义为要查询的字符，因为 StartsWith 和包含将会为生成的SQL搜索字词添加/追加％通配符。在这种情况下，您需要将％从 searchValue 中删除​​，然后再传递到开始使用或包含。
 
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?
解决方案
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.

                        
这篇关于实体框架：根据搜索项组合精确和通配符搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！