Linq query construction based on empty parameters

Question

I have an object like this:

public class Filters
{
    List<string> A { get; set; }
    List<string> B { get; set; }
}

I will use it as parameters of a function that will execute a query on a Database. These itens, A and B, will indicate the "where" filters.

Look at the following code:

//Filters Initialization
Filters TestFilters = new Filters();
TestFilters.A = new List<string>();
TestFilters.B = new List<string>(new string[] {"testfilter1","testfilter2"}); //WHERE B == "testfilter1" OR B == "testfilter2"

//Call of the function that executes SELECT on DB
List<Table> Result = TestFunction(TestFilter);

The function:

public static List<Table> TestFunction(Filters pParameter)
{
    ExampleDataContext dc = new ExampleDataContext(Properties.Settings.Default.ExampleConnectionString);
    List<Table> SelectResult = new List<Table>();

    if (pParameter.A.count != 0 && pParameter.B.count != 0)

        SelectResult = (from x in dc.Table
            where pParameter.A.Contains(x.A)
            where pParameter.B.Contains(x.B)
            select x).ToList();

    else if (pParameter.A.count == 0 && pParameter.B.count != 0)
    {
        SelectResult = (from x in dc.Table
            where pParameter.B.Contains(x.B)
            select x).ToList();
    }

    else if (pParameter.A.count != 0 && pParameter.B.count == 0)
    {
        SelectResult = (from x in dc.Table
            where pParameter.A.Contains(x.A)
            select x).ToList();
    }

    else if (pParameter.A.count == 0 && pParameter.B.count == 0)
    {
        SelectResult = (from x in dc.Table
            select x).ToList();
    }       

    return SelectResult;
}

Sometimes A or/and B is/are empty, then I am using "IF" structure to handle it. Maybe I could face a problem where my filter is bigger, more than 2 parameters, and it would be difficult/boring to code. My question is: It is working, but is there another way to do it, instead of using IF?

you should use chained linq query, that is to old query result continued with new query. you should not query on x every time. — Lei Yang, Jul 03 '17 at 02:14

score 1 · Answer 1 · answered Jul 03 '17 at 02:17

Please have a try(chained linq), I just rewrite by hand and did not compile or run:

        public static List<Table> TestFunction(Filters pParameter)
    {
        ExampleDataContext dc = new ExampleDataContext(Properties.Settings.Default.ExampleConnectionString);
        var SelectResult = dc.Table;

        if (pParameter.A.count != 0)
            SelectResult = from x in SelectResult 
                           where pParameter.A.Contains(x.A)
                           select x;

        if (pParameter.B.count != 0)
        {
            SelectResult = from x in SelectResult
                           where pParameter.B.Contains(x.B)
                           select x;
        }
        return SelectResult.ToList();
    }

score 0 · Answer 2 · answered Jul 03 '17 at 02:04

0

You can do something like this:

SelectResult = (from x in dc.Table
            where pParameter.A.Any() && pParameter.A.Contains(x.A)
            where pParameter.B.Any() && pParameter.B.Contains(x.B)
            select x).ToList();

answered Jul 03 '17 at 02:04

Xiaosu

605
1
9
21

score 0 · Answer 3 · answered Jul 03 '17 at 04:15

The code is straightforward. Just use correct LINQ syntax.

//Parameters class
public Class Parameters
{
    public List<string> A {get; set;}
    public List<int> B {get; set;}
}

//some function in a controller
public List<SomeResult> GetResult(Parameters pars)
{
    var db = new DbContext();
    var result = db.SomeResult.Where(s => s.Any(p =>p.SomeString == pars.A
                   || p.SomeInt == pars.B))
            .ToList();
    return result;
}

Hi Alex, I have tried it, but "Any" is not available. Are you sure? — R.Santos, Jul 03 '17 at 12:41

dovid · Answer 4 · 2017-07-03T04:53:25.793

with IQueryable<Table> (type of dc.Table and dc.Table.Where()), you can chain criteria, and export ToList only in buttom line.

so if yoy change the SelectResult from List<Table> to IQueryable<Table>, each criteria can be chained to the previous expression, and it is done as though AND:

public static List<Table> TestFunction(Filters pParameter)
{
    ExampleDataContext dc = new ExampleDataContext(Properties.Settings.Default.ExampleConnectionString);

    //all
    IQueryable<Table> SelectResult = dc.Table;

    //add conditions
    if (pParameter.A.count != 0 )
        SelectResult = SelectResult.Where(x => pParameter.A.Contains(x.B));
    if (pParameter.B.count != 0)
        SelectResult = SelectResult.Where(x => pParameter.A.Contains(x.B));

    //export, with one\two\zero conditions
    return SelectResult.ToList();
}

If your case is more complex (such as multiple conditions and with OR operators) consider using the wonderful tool PredicateBuilder.

score 0 · Accepted Answer · answered Jul 03 '17 at 14:34

I have tried some of the options, but unsucessfully. Below you can find the code that worked for me:

List<Table> SelectResult = (from x in dc.Table
                            where (pParameter.A.Count != 0 ? pParameter.A.Contains(x.A) : true)
                            where (pParameter.B.Count != 0 ? pParameter.B.Contains(x.B) : true)
                            select s).ToList();

Linq query construction based on empty parameters

5 Answers5