none
Dynamic conditions in WHERE query RRS feed

  • Question

  • Hi, I am just start to study LINQ..but I got heavy headache on convert from my old sql query.

    This is original sql query.

    Dim sql As String = "select CustomerID,CustomerNum,CustomerName,CASE IsLock WHEN 0 THEN 'Active' WHEN 1 THEN 'Locked' END as Status,Memo,LastOrderDate"
            sql += " from Customer where 1=1"

            Select Case ComboType.SelectedIndex
                Case 0
                    sql += " and ID='" & txtStr.Text & "'"
                Case 1
                    sql += " and EmailAddress='" & txtStr.Text & "'"
                Case 2
                    sql += " and CustomerNum='" & txtStr.Text & "'"
                Case 3
                    sql += " and CustomerName like '%" & txtStr.Text & "%'"
                Case 4
                    sql += " and Memo like '%" & txtStr.Text & "%'"
            End Select
            sql += " order by CustomerName"

    How should I set the dynamic conditions by LINQ to SQL?

    Thanks

    Thursday, March 18, 2010 2:48 PM

Answers

  • Usually if I have to build a dynamic query based on input parameter flags, I build a base query and then start applying filters based on the flag, and as a last step execute the ToList() to query the Database. Since LINQ delays execution till the very end, it serves as you very well. Let me explain.

     

    if you have a function fnFilterLINQ(int flag) where if flag 1 you execute filter 1 else if flag 2 you execute filter 2 and so on, I would build a dynamic query like this:

     

    List<Customer> fnFilterLINQ(int flag)

    {

    var query = from x in dbContext.Customers

    where x.Name.Contains("John") select x;

    switch(flag)

    {

    case 1:

    query = query.Where(a => a.Region == "London");

    break;

    case 2:

    query = query.Where(a => a.Region == "California" && a.Age > 45);

    break;

    }

    return query.ToList();

    }

     

    Hope this helps.

    Friday, March 19, 2010 3:21 PM

All replies

  • You add multiple where clauses which handle the possibility of the data not being there. NOte the ContainsText is an extension method I wrote and is the same as (string.IsNullOrEmpty( xxx ) == false) (C#: Does the String Have Content – My Newest Favorite Extension Method )


    where ( tbLogNumber.Text.ContainsText()  ? pr.Proposal_Attribute.ProgramID == tbLogNumber.Text : true )
    where ( tbUserName.Text.ContainsText()   ? pr.Proposal_Attribute.Name == tbLogName.Text : true )

    The :true will And the conditions together and using :false instead will act as an or.

    HTH



    William Wegerson (www.OmegaCoder.Com )
    Thursday, March 18, 2010 3:41 PM
    Moderator
  • Thanks for response.

    Your answer did solve my another issue but this.

    My original SQL query was for user to search customer by specific field

    According to what he select from ComboType (a Combobox), there is just one condition will be inside the query

    I don't know which type will be selected by user until he click search, in this case, I need to use "Select Case ..." or "Switch .. in c#" to combine the query according to ComboType.SelectedIndex

    My question is, how do I set LINQ query like this?


    Dim customer = From cust in C.Customer

    Select Case ComboType.SelectedIndex
                Case 0
                    customer = WHERE cust.ID = txtStr.Text
                Case 1
                    customer = WHERE cust.EmailAddress = txtStr.Text 
                Case 2
                    customer = WHERE cust.CustomerNum =  txtStr.Text 
                Case 3
                    customer = WHERE cust.CustomerName = txtStr.Text 
                Case 4
                    customer = WHERE cust.Memo = txtStr.Text 
            End Select

    customer = Select ......

    Of course it's incorrect sytanx, but I am wondering what's the right way in LINQ?

    Thursday, March 18, 2010 5:36 PM
  •   where ( ( ddlStatus.SelectedIndex == 1  )     ? pr.Proposal_Attribute.ID_Proposal_Status == DDLGetSelection( ddlStatus ) : true )
      where ( ( ddlStatus.SelectedIndex == 2  )     ? pr.EmailAddy == tbEmailAddy.Text : true )
    William Wegerson (www.OmegaCoder.Com )
    Thursday, March 18, 2010 6:49 PM
    Moderator
  • Hi William,

    After I tested your solution, unfortunately the result is not what I want.

    Your way generate the SQL like following (for instance, the ComboType.SelectedIndex=1)

    "select XXX from Customer where ID=txtStr.Text and EMailAddress=txtStr.Text or CustomerNum=txtStr.Text or CustomerName=txtStr.Text or Memo=txtStr.Text"

    Which cause the result is not in expectation.

    And what I expect is the query like this

    "select XXX from Customer where EMailAddress=txtStr.Text " <- I need only one condition depend on ComboType.SelectedIndex

    Friday, March 19, 2010 8:27 AM
  • Usually if I have to build a dynamic query based on input parameter flags, I build a base query and then start applying filters based on the flag, and as a last step execute the ToList() to query the Database. Since LINQ delays execution till the very end, it serves as you very well. Let me explain.

     

    if you have a function fnFilterLINQ(int flag) where if flag 1 you execute filter 1 else if flag 2 you execute filter 2 and so on, I would build a dynamic query like this:

     

    List<Customer> fnFilterLINQ(int flag)

    {

    var query = from x in dbContext.Customers

    where x.Name.Contains("John") select x;

    switch(flag)

    {

    case 1:

    query = query.Where(a => a.Region == "London");

    break;

    case 2:

    query = query.Where(a => a.Region == "California" && a.Age > 45);

    break;

    }

    return query.ToList();

    }

     

    Hope this helps.

    Friday, March 19, 2010 3:21 PM
  • Thanks for help, this is what exactly I need :)
    Saturday, March 20, 2010 5:00 AM
  • You add multiple where clauses which handle the possibility of the data not being there. NOte the ContainsText is an dvd extension method I wrote and is the same as (string.IsNullOrEmpty( xxx ) == false) (C#: Does the String Have Content – My Newest Favorite Extension Method )


    where ( tbLogNumber.Text.ContainsText() ? pr.Proposal_Attribute.ProgramID == tbLogNumber.Text : true )where ( tbUserName.Text.ContainsText()  ? pr.Proposal_Attribute.Name == tbLogName.Text : true )

    The :true will And the conditions together and using :false instead will act as an or.

    HTH



    William Wegerson (www.OmegaCoder.Com )

    Thanks for sharing. It is very useful. I'll take a try right now.
    Friday, July 23, 2010 4:28 AM