none
Build Dynamic LINQ depends on value on the form. RRS feed

  • Question

  • Hi

    Can someone guide me of how to such dynamic query in LINQ

    ' search between two ticket id's if they are not blank
    Dim query = "select * from mytable where 1=1"
    If Not String.IsNullOrEmpty(txt_ticket_id_1.Text) and Not String.IsNullOrEmpty(txt_ticket_id_2.Text)
        query += " AND mytable.id >=" & txt_ticket_id_1.text
        query += " AND mytable.id <=" & txt_ticket_id_2.text
    End If
    ' search for ticket that has specific receipt number, receipt is nchar(10)
    If Not String.IsNullOrEmpty(txt_receiptno.text) then
        query += " AND mytable.receipt_no = " & txt_receiptno.Text
    Else
        query += " AND mytable.receipt_no <> ''"
    End If
    MsgBox(query)


    Saturday, March 26, 2011 4:05 PM

Answers

  • Hi,

    The techniques described in this post might be helpful: Filtering a single table with multiple (and optional) where conditions

     

    Regards,


    Syed Mehroz Alam
    My Blog | My Articles
    • Marked as answer by Samir Ibrahim Wednesday, March 30, 2011 4:31 PM
    Monday, March 28, 2011 7:02 AM
  • Hi

    Can someone guide me of how to such dynamic query in LINQ

    ' search between two ticket id's if they are not blank
    
    
    
    
    
    
    
    Dim query = "select * from mytable where 1=1"
    
    
    
    
    
    
    
    If Not String.IsNullOrEmpty(txt_ticket_id_1.Text) and Not String.IsNullOrEmpty(txt_ticket_id_2.Text)
    
    
    
    
    
    
    
        query += " AND mytable.id >=" & txt_ticket_id_1.text
    
    
    
    
    
    
    
        query += " AND mytable.id <=" & txt_ticket_id_2.text
    
    
    
    
    
    
    
    End If
    
    
    
    
    
    
    
    ' search for ticket that has specific receipt number, receipt is nchar(10)
    
    
    
    
    
    
    
    If Not String.IsNullOrEmpty(txt_receiptno.text) then
    
    
    
    
    
    
    
        query += " AND mytable.receipt_no = " & txt_receiptno.Text
    
    
    
    
    
    
    
    Else
    
    
    
    
    
    
    
        query += " AND mytable.receipt_no <> ''"
    
    
    
    
    
    
    
    End If
    
    
    
    
    
    
    
    MsgBox(query)



     

    Hi Samir,

    Long time no see:)

    In Linq the 'query' is a like a variable that you can pass here and there and later append clauses to it. It is like VFP's SQLExec(.. lcSQL )  where the content is lcSQL is not evaluated where it is written but only when SQLExec()ed. Actually Allan gave the answer. Here is an expanded version:

    MyDataContext db = new MyDataContext();
    
    
    
    IQueryable<MyTable> query = db.MyTable;
    
    
    
    if (!String.IsNullOrEmpty(txt_ticket_id_1.Text) and !String.IsNullOrEmpty(txt_ticket_id_2.Text))
    
    
    
    {
    
    
    
     int minTicketId = Int32.Parse(txt_ticket_id_1.Text);
    
    
    
     int maxTicketId = Int32.Parse(txt_ticket_id_2.Text);
    
    
    
     query = query.Where(r => r.id >= minTicketId && r.id <= maxTicketId);
    
    
    
    }
    
    
    
    if (!String.IsNullOrEmpty(txt_receiptno.Text))
    
    
    
    {
    
    
    
     int receiptNo = Int32.Parse(txt_receiptno.Text);
    
    
    
     query = query.Where(r => r.receipt_no = receiptNo);
    
    
    
    }	
    
    
    
    else
    
    
    
    {
    
    
    
     query = query.Where(r => r.receipt_no == null || r.receipt_no == 0);
    
    
    
    }
    
    
    
    
    
    
    
    

     PS: No error checking such as TryParse instead of Parse. Also I have previously gave a more detailed sample for this very same question on Turkish MSDN forum in VB. Take a look at there, althoufgh it is in Turkish code is in VB, you would quickly see the progressive (ad hoc) pattern:)

    http://social.msdn.microsoft.com/Forums/tr-TR/vbnettr/thread/965e737e-1c75-49cb-8d19-a5be0eb07256

     

    Update: In that forum there is DataSet usage but that is just because the OP was explicitly asking about DataSets.

    • Marked as answer by Samir Ibrahim Wednesday, March 30, 2011 3:39 PM
    Wednesday, March 30, 2011 12:28 PM

All replies

  • Hi Samir,

     

    LINQ uses something called lazy evaluation. When you specify your query it does not execute. When you access the result of your query, that is when the query executes, not when you define it.

    As a result the code is very similar (in C#):

     NOTE: I left out some details from your query...converted enough to illustrate the process.

     

    var query = from rows in dbcontext.mytable select rows;

    if (!string.IsNullOrEmpty(txt_ticket_id_1.Text))

    {

       query = from rows in query where rows.id >= Convert.ToInt32(txt_ticket_id_1.Text) select rows;

    }

    MessageBox.Show(query.ToList()) //ToList forces the query to be evaluated and return a list.


    Allan Merolla | BEng, JD, MCP | .NET/Sharepoint | My Blog at http://www.parallelfun.com/
    Monday, March 28, 2011 3:47 AM
  • Hi,

    The techniques described in this post might be helpful: Filtering a single table with multiple (and optional) where conditions

     

    Regards,


    Syed Mehroz Alam
    My Blog | My Articles
    • Marked as answer by Samir Ibrahim Wednesday, March 30, 2011 4:31 PM
    Monday, March 28, 2011 7:02 AM
  • Hi Allan

    Thank you for your reply, and sorry for my late.

     

    I have in my form about 10 texboxes and 2 combo which are the criteria for the search.

    What i am looking for actually is how to made the second WHERE or added to previously one?

    I looked to example posted in here http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx but it is always give me an error saying Leading '.' or '!' can only appear inside a 'With' statement.

    I wonder if that only work for .ASP?

    Hi Syed,

    Your link also shows an example of showing one WHERE but depends on single field, what I am looking for is one WHERE multiple field.

    ofcource I can use IF and if the condition is true I had to write the whole query that meet what had been chosen from my form, but that is not decent way of doing it.

    Thanks.

     

    Tuesday, March 29, 2011 9:15 AM
  •  

    I looked to example posted in here http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx but it is always give me an error saying Leading '.' or '!' can only appear inside a 'With' statement.

    I wonder if that only work for .ASP?

    Hello Samir,

     

    Thanks for your post!

    According to what you said, you always got an error, if you have any time could you please show us your code? I think that's helpful to find the reason.

    Have a ncie day!

    Thanks,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, March 30, 2011 8:45 AM
    Moderator
  • Hello Jackie

    well, here is my trials

     

    Dim db As New dal.dx_project(myconnectionstring)
    
    ' dal is my project refrence where the .dbml is stored.
    
    '#1
    Dim lq_query = db.tickets.Where("1=1")
    '#2
    Dim lq_query = From t In db.tickets.Where("1=1") ' no space
    '#3
    Dim lq_query = From t In db.tickets .Where("1=1") '<- a space between .Where and tickets
    
    

    in #1,#2,#3 : Overload resolution failed because no accessible 'Where' can be called with these arguments:
           
    #4
    Dim lq_query = db.tickets
        .where("1=1") '<-- here i put the .where("1=1") on new line
    

    Leading '.' or '!' can only appear inside a 'With' statement.

     

     

     


    Wednesday, March 30, 2011 12:07 PM
  • Hi

    Can someone guide me of how to such dynamic query in LINQ

    ' search between two ticket id's if they are not blank
    
    
    
    
    
    
    
    Dim query = "select * from mytable where 1=1"
    
    
    
    
    
    
    
    If Not String.IsNullOrEmpty(txt_ticket_id_1.Text) and Not String.IsNullOrEmpty(txt_ticket_id_2.Text)
    
    
    
    
    
    
    
        query += " AND mytable.id >=" & txt_ticket_id_1.text
    
    
    
    
    
    
    
        query += " AND mytable.id <=" & txt_ticket_id_2.text
    
    
    
    
    
    
    
    End If
    
    
    
    
    
    
    
    ' search for ticket that has specific receipt number, receipt is nchar(10)
    
    
    
    
    
    
    
    If Not String.IsNullOrEmpty(txt_receiptno.text) then
    
    
    
    
    
    
    
        query += " AND mytable.receipt_no = " & txt_receiptno.Text
    
    
    
    
    
    
    
    Else
    
    
    
    
    
    
    
        query += " AND mytable.receipt_no <> ''"
    
    
    
    
    
    
    
    End If
    
    
    
    
    
    
    
    MsgBox(query)



     

    Hi Samir,

    Long time no see:)

    In Linq the 'query' is a like a variable that you can pass here and there and later append clauses to it. It is like VFP's SQLExec(.. lcSQL )  where the content is lcSQL is not evaluated where it is written but only when SQLExec()ed. Actually Allan gave the answer. Here is an expanded version:

    MyDataContext db = new MyDataContext();
    
    
    
    IQueryable<MyTable> query = db.MyTable;
    
    
    
    if (!String.IsNullOrEmpty(txt_ticket_id_1.Text) and !String.IsNullOrEmpty(txt_ticket_id_2.Text))
    
    
    
    {
    
    
    
     int minTicketId = Int32.Parse(txt_ticket_id_1.Text);
    
    
    
     int maxTicketId = Int32.Parse(txt_ticket_id_2.Text);
    
    
    
     query = query.Where(r => r.id >= minTicketId && r.id <= maxTicketId);
    
    
    
    }
    
    
    
    if (!String.IsNullOrEmpty(txt_receiptno.Text))
    
    
    
    {
    
    
    
     int receiptNo = Int32.Parse(txt_receiptno.Text);
    
    
    
     query = query.Where(r => r.receipt_no = receiptNo);
    
    
    
    }	
    
    
    
    else
    
    
    
    {
    
    
    
     query = query.Where(r => r.receipt_no == null || r.receipt_no == 0);
    
    
    
    }
    
    
    
    
    
    
    
    

     PS: No error checking such as TryParse instead of Parse. Also I have previously gave a more detailed sample for this very same question on Turkish MSDN forum in VB. Take a look at there, althoufgh it is in Turkish code is in VB, you would quickly see the progressive (ad hoc) pattern:)

    http://social.msdn.microsoft.com/Forums/tr-TR/vbnettr/thread/965e737e-1c75-49cb-8d19-a5be0eb07256

     

    Update: In that forum there is DataSet usage but that is just because the OP was explicitly asking about DataSets.

    • Marked as answer by Samir Ibrahim Wednesday, March 30, 2011 3:39 PM
    Wednesday, March 30, 2011 12:28 PM
  • Hello Cetin.

    Nice to see you here again :)

    well, you example works perfectly, although I still did not get how the second where is added but it works very well. Since there is no "AND" between the first query and the second one, I assumed the second query will overwrite the first, but it did not, it is being added to it.

    here is my conversion to your example (posted as is in my trial form)

     

    Dim db As New dal.mydbml
    Dim query As IQueryable(Of dal.ticket) = db.tickets
    If Not String.IsNullOrEmpty(Me.TextBox1.Text) and Not String.IsNullOrEmpty(TextBox2.Text)
    Dim minTicketId As Integer = Int32.Parse(Me.TextBox1.Text)
    Dim maxTicketId As Integer = Int32.Parse(Me.TextBox2.Text)
    query = query.Where( Function(r) r.id >= minTicketId andalso r.id <= maxTicketId )
    End If

    If Not String.IsNullOrEmpty(Me.TextBox3.Text)
    Dim receiptNo As string = Me.TextBox3.Text
    query = query.Where(Function(r) r.reciept_no = receiptNo)
    Else
    query = query.Where(Function(r) r.reciept_no <> "" )
    End If
    Me.DataGridView1.DataSource = query.ToList



     

    Dim query As IQueryable(Of dal.ticket) = db.tickets

    The above line removes the error that was occurring when I was using the .Where(), but although your example works perfectly, i am not able to do it in the way posted in http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

    which is trying to pass a string to the .Where(my_creteria_string)

    very cool example. and thanks.

    Edited.

    Thank you Syed, I was not aware the effect if IQueryable because i did not test the code in the link you gave until I convert Cetin code to vb.

     

     



    Wednesday, March 30, 2011 3:39 PM
  • That is not fair. once I am waiting someone to answer me, and now the answers are raining on my head. lol

    from this link linq-to-sql-part-8-executing-custom-sql-expressions , I manage to get what I need exactly

    Dim var1,var2,var3
    var1 = 1
    var2 = 10000
    var3 = 30100
    Dim query2 = "select * from ticket where 1=1"
    If Not String.IsNullOrEmpty(var1and Not String.IsNullOrEmpty(var2)
        query2 += " AND id >= {0}"
        query2 += " AND id <= {1}" 
    End If
    ' search for ticket that has specific receipt number, receipt is nchar(10)
    If Not String.IsNullOrEmpty(var3then
        query2 += " AND reciept_no = {2}" 
    Else
        query2 += " AND reciept_no <> ''"
    End If

    Dim x = db.ExecuteQueryOf dal.ticket)(query2,var1,var2,var3)
    Me.DataGridView1.DataSource = x.ToList

    the query2 variable is holding the string in query as I had it from the first place,

    I begin to love this LINQ. lol

     

    Wednesday, March 30, 2011 5:22 PM
  • That is not fair. once I am waiting someone to answer me, and now the answers are raining on my head. lol

    from this link linq-to-sql-part-8-executing-custom-sql-expressions , I manage to get what I need exactly

    Dim var1,var2,var3
    var1 = 1
    var2 = 10000
    var3 = 30100
    Dim query2 = "select * from ticket where 1=1"
    If Not String.IsNullOrEmpty(var1and Not String.IsNullOrEmpty(var2)
        query2 += " AND id >= {0}"
        query2 += " AND id <= {1}" 
    End If
    ' search for ticket that has specific receipt number, receipt is nchar(10)
    If Not String.IsNullOrEmpty(var3then
        query2 += " AND reciept_no = {2}" 
    Else
        query2 += " AND reciept_no <> ''"
    End If

    Dim x = db.ExecuteQueryOf dal.ticket)(query2,var1,var2,var3)
    Me.DataGridView1.DataSource = x.ToList

    the query2 variable is holding the string in query as I had it from the first place,

    I begin to love this LINQ. lol

     

    And you know very well that I would strongly be against such kind of string builded SQL:) Remember my friend it is a very good example of "SQL injection" attack. Use parameters (and if you would still do direct SQL execution that is not very Linq:)
    Wednesday, March 30, 2011 6:07 PM
  • Hello Again Cetin.

    I know what you mean, but since i am still discovering, and did not made deep test yet, is not this line is parametrized and will prevent SQL Injection 

    Dim x = db.ExecuteQueryOf dal.ticket)(query2,var1,var2,var3)

    The above has parameters passed to the query. No?

    Thursday, March 31, 2011 5:57 AM
  • Do not think taking a single line. There they are conceptually parameters but they are parameters to your ExecuteQuery method. Before that you are already constructing it by string.Format() and in turn sending a constructed string rather than parameterized query. In your case you are only safe in that the values are integers. But IMHO once you get used to this style, sooner or later you use it for injectable items too.
    Friday, April 8, 2011 3:59 PM