none
Using Like % in Linq RRS feed

  • Question

  • H

    I am using following query to get result :

     if (sku.Equals("ALL") || sku.Equals(null))
          {
            sku = "%";
          }
    
    
          q = (from h in oList
             join f in oContext.OrderHeads on h.ORDID equals f.ID
             where h.PRODCODE.Contains(sku)
             select f).ToList();
    


    if user key in the value of SKU then the query runs fine. But if user key in "ALL" or leave the SKU blank then I want to get all the records. I tried StartWith and Contains function using % but it didnt work. Any help.

    Thanks

     

    Tuesday, July 12, 2011 10:47 AM

Answers

  • Hi,

    What Ladislav says is correct, you cannot use % in Contains, StartsWith, EndsWith etc...

    However, you can still fix this without using a if.

    You can use string.IsNullOrEmpty() inside your where statement, so you can do this like this:

     q = (from h in oList
         join f in oContext.OrderHeads on h.ORDID equals f.ID
         where string.IsNullOrEmpty(sku) == true || sku == "ALL" || h.PRODCODE.Contains(sku)
         select f).ToList();
    
    

    Hope this helps you write this without an if..else block!

     


    --Rune
    • Marked as answer by xFahadx Wednesday, July 13, 2011 2:48 PM
    Tuesday, July 12, 2011 4:52 PM

All replies

  • Hello,

    Contains, StartsWith and EndsWith don't support any wildcards. Wildcards are available only if you use ESQL LIKE directly.

    Your code can be probably changed to:

     

    if (sku.Equals("ALL") || sku.Equals(null))
    {
      q = (from h in oList
       join f in oContext.OrderHeads on h.ORDID equals f.ID   where h.PRODCODE != null   select f).ToList();
    }
    else
    {
      q = (from h in oList
       join f in oContext.OrderHeads on h.ORDID equals f.ID
       where h.PRODCODE.Contains(sku)
       select f).ToList();
    }
    


     

    Best regards,
    Ladisalv


    Tuesday, July 12, 2011 11:35 AM
  • Hi,

    Thanks for your reply , thinkg is I wan to use query out side the IF else block, as I want to use the var q out this block. The var q is implicit type variable and if I declare it as a global variable then I cant initialize it.

    Thanks

     

    Tuesday, July 12, 2011 1:42 PM
  • Hi xFahadx;

    By the looks of your code h.PRODCODE and sku are both single instance of a string type. You can accomplish what you need as follows building the query up conditionally as follows.

    ObjectContextEntities oContext = new ObjectContextEntities( );
    
    var query = from h in oContext.oList
          join f in oContext.OrderHeads on h.ORDID equals f.ID
          select new { h, f };
    
    
    if( !(sku.Equals( "All" ) || sku.Equals(String.Empty) ))
    {
      var query2 = (from h in query
             where h.h.PRODCODE == sku
             select h.f).ToList();
    
      // This is where sku has a value other then All or String.Empty
      // Return or process query2 here
    }
    else
    {
      // Process All here
      // Return or process query here
    }
    
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Tuesday, July 12, 2011 3:32 PM
  • Hi,

    What Ladislav says is correct, you cannot use % in Contains, StartsWith, EndsWith etc...

    However, you can still fix this without using a if.

    You can use string.IsNullOrEmpty() inside your where statement, so you can do this like this:

     q = (from h in oList
         join f in oContext.OrderHeads on h.ORDID equals f.ID
         where string.IsNullOrEmpty(sku) == true || sku == "ALL" || h.PRODCODE.Contains(sku)
         select f).ToList();
    
    

    Hope this helps you write this without an if..else block!

     


    --Rune
    • Marked as answer by xFahadx Wednesday, July 13, 2011 2:48 PM
    Tuesday, July 12, 2011 4:52 PM