none
c# linq dynamic where clause

    Question

  • Apologies if its a repeat question

    I have few text boxes and combobox on my add-in which the users might select or not, since these are optional before clicking submit. If there a way to apply this options conditions on linq where clause

    So it the user doesn't enter value in textbox then I don't want it as part of my filter.

    My current code 

    var _bList = (from C in model.dbObject_table
                   .Where ( p => p.Bname.Contains(tbBName.Text)
                    )
                  ).Tolist();

    Should I handle this where clause in SQL such that if textbox value is null then don't apply the filter ?


    • Edited by StSingh Friday, April 7, 2017 10:47 AM
    Friday, April 7, 2017 10:46 AM

Answers

  • Hitting the db will not be such a good idea if you have the data available in the list.

    in case of strings it will be pointless to do the checking if there is no text entered then it will always return the full list as the contains will return true for empty string and Text property of a text box will not be null.

    in case of other types you can use the conditional ?: and the null coalescing (??) operator with an additional variable

    int? Bname =  String.IsNullOrEmpty(tbBName.Text.Trim()) ? null :Convert.ToInt32(tbBName.Text.Trim());
    
    
    var _bList = (from C in model.dbObject_table
                   .Where ( p => p.Bname = (Bname??p.Bname)
                    )
                  ).Tolist();



    --------------------------------------------------------

    Surender Singh Bhadauria

    My Blog

     



    Friday, April 7, 2017 11:07 AM

All replies

  • Would be helpful if you can post the scenario, where you want this piece code, like where it fits in.

    Mark Answered, if it solves your question and Vote if you found it helpful.
    Rohit Arora

    Friday, April 7, 2017 11:04 AM
  • Hitting the db will not be such a good idea if you have the data available in the list.

    in case of strings it will be pointless to do the checking if there is no text entered then it will always return the full list as the contains will return true for empty string and Text property of a text box will not be null.

    in case of other types you can use the conditional ?: and the null coalescing (??) operator with an additional variable

    int? Bname =  String.IsNullOrEmpty(tbBName.Text.Trim()) ? null :Convert.ToInt32(tbBName.Text.Trim());
    
    
    var _bList = (from C in model.dbObject_table
                   .Where ( p => p.Bname = (Bname??p.Bname)
                    )
                  ).Tolist();



    --------------------------------------------------------

    Surender Singh Bhadauria

    My Blog

     



    Friday, April 7, 2017 11:07 AM
  • Thanks Surender.

    If I'm understanding it correctly, all the textboxes that are null or no value entered, LINQ will not include then as part of its query which will be posted to database ?

    Friday, April 7, 2017 11:18 AM
  • the text property of texbox will never be null and It will be included in the query but the contains part will return true for blanks but make sure that you perform a trim on the text property other wise the whitespace will cause the contain to return false if there are no whitespaces in the value.

    The Linq will not hit the DB unless there is some change in the data. it will perform the query on the in memory list and return the results.


    --------------------------------------------------------

    Surender Singh Bhadauria

    My Blog

     


    Friday, April 7, 2017 11:23 AM
  • I ran a SQL profiler and noticed that it generates below code

    AND ([Extent1].[BName] LIKE @p__linq__0 ESCAPE ''~'')
    @p__linq__0 varchar(8000)
    @p__linq__0='%%'

    because of which the result set is not accurate. I mean its returning less rows... :(

    And on my further investigation it seems that where BName is null it excludes those rows.

    is there any alternative to this ?

    • Edited by StSingh Friday, April 7, 2017 4:50 PM
    Friday, April 7, 2017 4:43 PM