Like with or conditon in Linq

Question

I have a scenario where i want to search multiple values and with like. For example: If i have a sequence like

string[] codeValues={"Anthrax","anth","coliblex"};

And i want to search like '%Anthrax%' or "%anth%" or "%coliblex%". Currently i am having this code which only returns the values that will match with the exact search value.

string[] codeValues = {"anthrax"};
var obj=(from icdcodes in M_ICD_CPT_CODEs
where icdcodes.ROW_STATUS==1
      && icdcodes.FK_USER_ID==0
      && icdcodes.FK_CODE_TYPE_ID==240000     
      && codeValues.Contains(icdcodes.CHAR255)
      && icdcodes.MODULE_PHYS_PREF==0
select new 
{
  PkId=icdcodes.PK_ICD_CPT_CODE_ID,
  CodeValue=icdcodes.CODE_VALUE,
  Narrative = icdcodes.CHAR255
}).ToList();

Console.Write(obj);

Is there any solution that will filter with like. Equivalent sql will be
where CHAR255 LIKE '%anthrax%' or CHAR255 LIKE '%someothervalue%'..etc

Thanks

What database are you using? If you are using SQL Server, you can use a SqlFunctions.PatIndex expression to have the "like" performed at the database directly from your linq expression (i.e. the EF linq provider knows how to do that translation). If you are using a different database, it most likely also has a native "LIKE" clause, but EF will not know how to translate to it. A solution then is to create a stored procedure specific for your database that performs the "LIKE" instruction. — Alex, Aug 20 '13 at 14:43
Thanks Alex.I am using SqlServer 2008.Can SqlFunctions.PatIndex filter for multiple values ? — mns, Aug 20 '13 at 14:47
Also available is `SqlFunctions.Like`. Both `Like` and `PatIndex` allow multiple matches, wildscards, etc, but constructing the right filter expression may not be that trivial/possible if you try to match a list of keywords. I am not an expert on that subject. I would advise you to check online examples and SQL Server technet docs (http://technet.microsoft.com/en-us/library/ms179859.aspx, http://technet.microsoft.com/en-us/library/ms188395.aspx) to see if that will work in your case — Alex, Aug 20 '13 at 15:02

score 1 · Answer 1 · answered Aug 20 '13 at 14:46

1

You need to use AnyandContains.

string[] codeValues = {"anthrax"};
var obj=(from icdcodes in M_ICD_CPT_CODEs
where icdcodes.ROW_STATUS==1
      && icdcodes.FK_USER_ID==0
      && icdcodes.FK_CODE_TYPE_ID==240000     
      && codeValues.Any(cv => icdcodes.CHAR255.Contains(cv))
      && icdcodes.MODULE_PHYS_PREF==0
select new 
{
  PkId=icdcodes.PK_ICD_CPT_CODE_ID,
  CodeValue=icdcodes.CODE_VALUE,
  Narrative = icdcodes.CHAR255
}).ToList();

answered Aug 20 '13 at 14:46

It'sNotALie.

22,289
12
68
103

Thanks It'sNotALie for the suggestion.I have tried with this .It can work. – mns Aug 20 '13 at 15:57

score 1 · Answer 2 · edited May 23 '17 at 10:25

1

Try this:

string[] codeValues = {"anthrax"};
var obj=(from icdcodes in M_ICD_CPT_CODEs
where icdcodes.ROW_STATUS==1
  && icdcodes.FK_USER_ID==0
  && icdcodes.FK_CODE_TYPE_ID==240000     
  && codeValues.Any(val => icdcodes.CHAR255.Contains(val))
  && icdcodes.MODULE_PHYS_PREF==0
select new 
{
  PkId=icdcodes.PK_ICD_CPT_CODE_ID,
  CodeValue=icdcodes.CODE_VALUE,
  Narrative = icdcodes.CHAR255
}).ToList();

LINQ: Entity string field contains any of an array of strings

edited May 23 '17 at 10:25

Community

1
1

answered Aug 20 '13 at 14:47

Przemysław Kalita

1,977
3
18
28

Thanks Przemyslaw Kalita. – mns Aug 20 '13 at 15:56

Like with or conditon in Linq

2 Answers2