How to apply search filtration based on AND condition using LINQ MVC

Question

I'm working on asp.net-mvc project

I want to filter records based on parameters(most of them are dropdown list in Veiw) using LINQ with(AND condition), but my problem is null or empty parameters.

Sometimes the user filter records based one or two fields and rest of fields values return as empty. then there is no result meet the conditions.

Currently i use (OR condition) to fetch wanted record:

 public ActionResult Search(int? ReportID, int? ReportName, int? Department, string ManagerConfirmationState1, string RiskLevel, string NoteType)
    {




        ViewBag.ReportID = new SelectList(db.Reports, "ReportID", "ReportID");
        ViewBag.ReportName = new SelectList(db.Reports, "ReportID", "ReportName");
        ViewBag.Department = new SelectList(db.Departments, "DepartmentID", "DepartmentName");
        ViewBag.ManagerConfirmationState1 = new SelectList(db.ManagerConfirmationState1, "ManagerConfirmationState1ID", "ManagerConfirmationState11");
        ViewBag.RiskLevel = new SelectList(db.RiskLevels, "RiskLevelID", "RiskLevel1");
        ViewBag.NoteType = new SelectList(db.NoteTypes, "NoteTypeID", "NoteType1");

        var Notes = from n in db.Notes
                    select n;


        //filteration

        Notes = Notes.Where(n => n.ReportID == ReportID
                                   || n.Report.ReportID == ReportName
                                   || n.Report.Department.DepartmentID == Department
                                   || n.ManagerConfirmationState1.Equals(ManagerConfirmationState1)
                                   || n.RiskLevel.Equals(RiskLevel)
                                   || n.NoteType.Equals(NoteType));




        return View(Notes.ToList());
    }

Piece of View:

@using (@Html.BeginForm("Search", "Notes", null, FormMethod.Post))
{

    <div class="form-horizontal">

        <div class="col-md-6">

            <div class="form-group">

                <label class="control-label col-md-2">رقم التقرير</label>
                <div class="col-md-10">
                    @Html.DropDownList("ReportID", null, "اختـر", htmlAttributes: new { @class = "form-control" })

                </div>
            </div>

            <div class="form-group">

                <label class="control-label col-md-2">التقرير</label>
                <div class="col-md-10">
                    @Html.DropDownList("ReportName", null, "اختـر", htmlAttributes: new { @class = "form-control" })

                </div>
            </div>


            <div class="form-group">

                <label class="control-label col-md-2">نوع الملاحظة</label>
                <div class="col-md-10">
                    @Html.DropDownList("NoteType", null, "اختـر", htmlAttributes: new { @class = "form-control" })

                </div>
            </div>

        </div>


        <div class="col-md-6">

            <div class="form-group">

                <label class="control-label col-md-2">الإدارة</label>
                <div class="col-md-10">
                    @Html.DropDownList("Department", null, "اختـر", htmlAttributes: new { @class = "form-control" })

                </div>
            </div>

            <div class="form-group">

                <label class="control-label col-md-2">اعتماد المدير</label>
                <div class="col-md-10">
                    @Html.DropDownList("ManagerConfirmationState1", null, "اختـر", htmlAttributes: new { @class = "form-control" })

                </div>
            </div>

            <div class="form-group">

                <label class="control-label col-md-2">درجة المخاطرة</label>
                <div class="col-md-10">
                    @Html.DropDownList("RiskLevel", null, "اختـر", htmlAttributes: new { @class = "form-control" })

                </div>
            </div>



        </div>





        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="بحث" class="btn btn-default" />
            </div>
        </div>


    </div>


}

Summary:

Can i apply filtration with ignoring empty inputs usin LINQ?

Any suggestion?

Answer 1

If you have a limited number of parameters to filter on (or even a single one that may be null or empty) you can check its value in the query itself when using AND.

If the filter value is not supplied then you want to select all (currently filtered) records without further filtering, otherwise you want to add the filter value to the currently filtered records.

I'll use null checks because you have shown the parameters to be nullable.

Notes = Notes.Where(n => 
(ReportId == null || n.ReportID == ReportID) 
&& (ReportName == null || n.Report.ReportName == ReportName) 
&& (Department == null || n.Report.Department.DepartmentID == Department) 
&& (ManagerConfirmationState1 == null || n.ManagerConfirmationState1.Equals(ManagerConfirmationState1)) 
&& (RiskLevel == null || n.RiskLevel.Equals(RiskLevel)) 
&& (NoteType == null || n.NoteType.Equals(NoteType))
);

Answer 2

Just build the query up incrementally:

if (field1.HasValue) {
  query = query.Where(x => x.Val1 = field1.Value);
}
if (field2.HasValue) {
  query = query.Where(x => x.Val2 = field2.Value);
}

(Because x.Where(y => cond1(y) && cond2(y)) is functionally equivalent to x.Where(y => cond1(y)).Where(y => cond2(y)).