none
[EF 4.0]Query for pseudocode WHERE field1.contains(valueString) OR field2.contains(valueString) RRS feed

  • Question

  • Hi everyone!  I am fairly new to LINQ - but especially to the subset of LINQ to Entities.  I am having trouble forming the LINQ version for certain queries, as it seems like it is limited in the query language and is not as flexible as straight SQL. 

    I currently am creating a search where the users enter keyword(s) that they want to find in the records.  The search should check if any of the fields in the entity contain the keyword (or a combination of keywords).  I think I know how to get the logic together of searching against the keywords; however, I can't seem to translate the equivalent SQL into a valid LINQ to Entities query.

    A stripped down version of the code that I have tried is as follows:

    IQueryable<EntityName> searchResults = 
    context.EntityName.Where
    ((s => s.MasterITServices.Contains(value) == true) || 
    (s=> s.Name.Contains(value) == true));

    I also tried the following:

    IQueryable<EntityName> searchResults = 
    from e in context.Entity
    where (e.Field1.Contains(value) ||
    e.Field2.Contains(value))
    select e;

    If any of you could provide some guidance of this, I would very much appreciate it.  Thank you in advance for your help!
    • Edited by Laura Kay Wednesday, September 19, 2012 2:43 PM Added EF version
    Wednesday, September 19, 2012 2:39 PM

Answers

  • Well, I figured out the solution.  It turns out that I forgot that the second field was an integer, and I was trying to do a 'Contains' on the integer field. The syntax of contains.("") || contains.("") works if the variable types are correct.  

    Thanks for your help!

    Wednesday, September 19, 2012 3:30 PM

All replies

  • Hmm, usually EF can recognize Contains(). Is there some exception or unexpected result in your case?

    Wednesday, September 19, 2012 3:02 PM
  • Contains() does work correctly; I receive the expected result if I only have one where condition.  The problem occurs when I add the 'OR' condition.  

    The error I get is "The best overloaded method for List<EntityName>.Contains(Field2) has some invalid arguments." where EntityName is the entity name and field2 is the column.

    Wednesday, September 19, 2012 3:13 PM
  • Well, I figured out the solution.  It turns out that I forgot that the second field was an integer, and I was trying to do a 'Contains' on the integer field. The syntax of contains.("") || contains.("") works if the variable types are correct.  

    Thanks for your help!

    Wednesday, September 19, 2012 3:30 PM
  • You need to write condition per property. EF cannot search for all properties of entity, it can only translate search condition into TSQL LIKE for each specified property.

    Consider following sample:

    var keyword = "test";
    var articles = context.Articles.Where(a => a.Title.Contains(keyword) || a.Teaser.Contains(keyword)).ToList();

    If you have several keywords, Union will be probably a workaround. This way you can merge queries for each keyword.

    Depending on count of keywords, properties and database size it could be better for performance to use SQL Server full text search or add some table of tags and search tags in this table.

    Wednesday, September 19, 2012 3:48 PM