Editor C# .NET Where and OrWhere clause

Editor C# .NET Where and OrWhere clause

david.j.meyer2@boeing.comdavid.j.meyer2@boeing.com Posts: 54Questions: 15Answers: 0
edited February 2023 in Editor

Link to test case: N/A
Debugger code (debug.datatables.net): N/A
Error messages shown: N/A
Description of problem:

I have an array of strings that contain the ID's of records i need to retrieve from the database. How do I create a where clause, what I am doing is not working. The issue is the ID's are generated dynamically with no limit on the number of ID's being requested.

string[] ids= { "3", "4", "5", "6", "7", "8", "9" };

editor.Where(r =>
{
    bool firsttime = true;
    foreach (var i in ids)
    {
        if (firsttime)
        {
            r.Where("ID", i);
        }
        else
        {
            firsttime = false;
            r.OrWhere("ID", i);
        }
    }
});

Any help is greatly appreciated.

Replies

  • david.j.meyer2@boeing.comdavid.j.meyer2@boeing.com Posts: 54Questions: 15Answers: 0

    The WHERE CLAUSE we are looking for based on the example above is included below.

    WHERE ID=3 OR ID=4 OR ID=5 OR ID=6 OR ID=7 OR ID=8 OR ID=9

    As i stated earlier, the number of ID's is variable so the solution needs to be dynamic. Thanks again in advance for any help provided.

  • allanallan Posts: 61,669Questions: 1Answers: 10,096 Site admin

    Hi David,

    Try this:

    editor.Where(q => {
      q.Where(r => {
        foreach (var i in ids) {
          r.OrWhere("ID", i);
        }
      });
    })
    

    The reason I think this will make a different is that Editor.Where() does not automatically do grouping, but Query.Where() does (i.e. that inner q.Where() is going to be wrapped in parenthesis).

    Allan

  • david.j.meyer2@boeing.comdavid.j.meyer2@boeing.com Posts: 54Questions: 15Answers: 0

    This worked perfectly, thanks Allan. It generates an OR condition where clause dynamically enclosed in parenthesis. Here is an example of a where clause generated using the code you provided. I only had 4 ID's in this testcase.

    WHERE ("ID" = :where_1 OR "ID" = :where_2 OR "ID" = :where_3 OR "ID" = :where_4 )
    

    I am including the generated where clause to help anyone else attempting to programmatically generate a dynamic where clause.

  • allanallan Posts: 61,669Questions: 1Answers: 10,096 Site admin

    Great to hear that did the job - thanks for posting back!

    Allan

Sign In or Register to comment.