How to apply multiple optional filters in a LINQ query

Question

I have a page where user can select any number of search filters to apply search

When user clicks on search, these parameters are passed to my GetIndicatorData method to perform the query. However, it doesn't seem to work for me.

Here is my code

public static List<tblindicators_data_custom> GetIndicatorsData(string status, int service_id, int operator_id, string year, string frequency)
{


    var date = Convert.ToDateTime(year + "-01-01");
    int[] numbers = status.Split(',').Select(n => int.Parse(n)).ToArray();
    var ict = new ICT_indicatorsEntities();
    var result = from ind in ict.tblindicators_data
                 join ser in ict.tblservices on ind.service_id equals ser.Id
                 join oper in ict.tbloperators on ind.operator_id equals oper.Id
                 where numbers.Contains(ind.status) && (ind.date_added.Year == date.Year) 
                 select new
                 {
                     ind.Id,
                     ind.service_id,
                     ind.survey_id,
                     ind.operator_id,
                     ind.type,
                     ind.date_added,
                     ind.quater_start,
                     ind.quater_end,
                     ind.status,
                     ind.month,
                     service = ser.name,
                     operator_name = oper.name
                 };

    List<tblindicators_data_custom> data = new List<tblindicators_data_custom>();
    foreach (var item in result)
    {
        tblindicators_data_custom row = new tblindicators_data_custom();
        row.Id = item.Id;
        row.survey_id = item.survey_id;
        row.service_id = item.service_id;
        row.service_name = item.service;
        row.operator_id = item.operator_id;
        row.operator_name = item.operator_name;
        row.date_added = item.date_added;
        row.quater_start = item.quater_start;
        row.type = item.type;
        row.quater_end = item.quater_end;
        row.month = item.month == null? DateTime.Now:item.month;
        row.status = item.status;
        data.Add(row);
    }

    return data;
}

One thing I usually do with search forms with optional search parameters is this: `&& (status == "" || ind.status == status) && (service_id==0 || ind.service_id == service_id)` — Cleptus, Jul 11 '18 at 07:42
@bradbury9 this will not work , filters are based on if else condition , the way you mentioned it will not work. — Muhammad Saqlain Arif, Jul 11 '18 at 07:52
Have you given it a try? The code I put is an if-else. If `status == "" || ind.status == status` means 'if status variable is set, check if the value is the variable one, else retrieve the record' — Cleptus, Jul 11 '18 at 07:59
@bradbury9 yes i tried it but not wokring if i set status or any value to 0 it will compare and results will be wrong.. one thing more we can't add if , else inside query. it shows syntax error. — Muhammad Saqlain Arif, Jul 11 '18 at 08:04
Possible duplicate of [Multiple filter LINQ search C#](https://stackoverflow.com/questions/28820343/multiple-filter-linq-search-c-sharp) — Lucifer, Jul 11 '18 at 08:11
@Lucifer not its not duplicate of above example , because they are using linq and i am using iquerable — Muhammad Saqlain Arif, Jul 11 '18 at 08:45
IQueryable is **not** incompatible with linq. Check [this link](https://weblogs.asp.net/dixin/understanding-linq-to-sql-2-iqueryable-lt-t-gt) or this related [stackoverflow question](https://stackoverflow.com/questions/1578778/using-iqueryable-with-linq) — Cleptus, Jul 11 '18 at 11:22
@bradbury9 ok got it, can you suggest me or tell me edits in my query as per my needs. — Muhammad Saqlain Arif, Jul 11 '18 at 11:52

How to apply multiple optional filters in a LINQ query

0 Answers0