none
Conditional Where Clause RRS feed

  • Question

  • Hi,

    I would like to know how to do something like this on linq:

    var _query = from o in _database.table

                    where 

                    if (!string.IsNullOrEmpty(str_FirstName))

                        SqlMethods.Like(o.FirstName, "%" + str_FirstName.Trim() + "%") &&

                    if (!string.IsNullOrEmpty(str_LastName))

                       SqlMethods.Like(o.LastName,  "%" + str_LastName.Trim() + "%") 

                    select o;

    Any suggestion on how to do this?
    Thanks in advance


    Duan Brito - Analista de Sistemas/Programador
    Tuesday, May 31, 2011 9:12 PM

Answers

  • var _query = from o in _database.table   select o;
       if (!string.IsNullOrEmpty(str_FirstName))
    {
       _query =_query.Where(o=>SqlMethods.Like(o.FirstName, "%" + str_FirstName.Trim() + "%"));
    }
      if (!string.IsNullOrEmpty(str_LastName))
    {
      _query =_query.Where(o=>SqlMethods.Like(o.LastName,  "%" + str_LastName.Trim() + "%") );
    }
             

    Best Regards,
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Wednesday, June 1, 2011 5:47 AM

All replies

  • Hi Duan;

    The Linq "where" clause expects a boolean, true or false, as a result to the condition. So using if statement in the where will not work. The other thing is that Linq to SQL needs to create an SQL query that it sends to the server so what ever we put in a Linq to SQL query must be translatable to SQL because our queries will be executed on the server.

    From your code it is not clear to me what problem you are trying to solve. Can you explain, maybe there is another way.

     


    Fernando

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Wednesday, June 1, 2011 3:01 AM
  • Hi,

    It looks you want to filter your query based on user selected values in your string variables. Here's a post that describes such situation: Filtering a single table with multiple (and optional) where conditions

     

    Regards,


    Syed Mehroz Alam
    My Blog | My Articles
    Wednesday, June 1, 2011 5:09 AM
  • var _query = from o in _database.table   select o;
       if (!string.IsNullOrEmpty(str_FirstName))
    {
       _query =_query.Where(o=>SqlMethods.Like(o.FirstName, "%" + str_FirstName.Trim() + "%"));
    }
      if (!string.IsNullOrEmpty(str_LastName))
    {
      _query =_query.Where(o=>SqlMethods.Like(o.LastName,  "%" + str_LastName.Trim() + "%") );
    }
             

    Best Regards,
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Wednesday, June 1, 2011 5:47 AM