How do i filter and search selected fields only and exclude null textboxes in ASP.NET MVC

Question

Simply I have 4 textboxes and I want to search and filter data by one or two or 3 or 4 textboxes and execlude the textbox if i didn't want to search by that textbox :

I am using entityFramework , and I have orders_class include the tables Lab_Orders,Lab_Sample_status,Patients:

The fields I have :

Start Date ---------------

End Date -----------------

Polyclinic File No -------------

ID or Passport No --------------

I select the data depends on the value entered in the textbox fields

I want to search some times the orders between 2 dates (start date to end date) only .

Some times I need to search all orders by patient passport number

some times I need to search by polyclinic File No only

1- I created the View input textboxes :

@using (Html.BeginForm("Clinicsorders", "Orders", FormMethod.Get))
{
    <span id="FromDate">From Date</span><input id="FromDatetext" type="text" name="startdate"/>
    <span id="ToDate"> To Date</span>    <input id="ToFatetext" type="text" name="enddate"/>
    <span id="PCfileno">Polyclinic File No</span><input id="pcfilenotext" type="text" name="pcfileno" />
    <span id="IDNO">ID/Iqama No</span>    <input id="idnotext" type="text" name="idno" />
    <br/>
    <br/>
    <input id="Submit1" type="submit" value="Search Orders" style="margin-left:1000px" />
}

2- I created the controller :

public ActionResult Clinicsorders(DateTime? startdate,DateTime? enddate,string pcfileno,string idno)
        {


            List<Lab_Orders> Clinicorder = db.Lab_Orders.OrderByDescending(x => x.ORDER_ID).ToList();
            List<Lab_Sample_status> samplestatus = db.Lab_Sample_status.ToList();
            List<Patients> patients = db.Patients.ToList();
            if (startdate == null && enddate == null && pcfileno == null && idno == null)
            {
                var OrdersList = from o in Clinicorder
                                 join st in samplestatus on o.order_status equals st.status_id
                                 join pt in patients on o.patient_no equals pt.Patient_No
                                 where o.CUSTID == (int)Session["UserCustid"]
                                 select new Orders_Tables { LabOrders = o, LabOrderStatus = st , patients = pt };
                                 return View(OrdersList);
            }
            else if (startdate != null || enddate != null || pcfileno !=null || idno != null)
            {
                var OrdersList = from o in Clinicorder
                                 join st in samplestatus on o.order_status equals st.status_id
                                 join pt in patients on o.patient_no equals pt.Patient_No
                                 where o.CUSTID == (int)Session["UserCustid"] && (o.ORDER_DATE >= startdate && o.ORDER_DATE <= enddate)
                                 select new Orders_Tables { LabOrders = o, LabOrderStatus = st , patients = pt };
                return View(OrdersList);
            }

            return View();


        }

When I run the view All orders selected by default.

Also I can search and filter now successfully between 2 dates (startdate and enddate).

I want now the remaining parts to filter and search by pcfileno and idno which is string value.

How I will implement this functionality and filter only by and search with the data I need and execlude the null textboxes.

In ASP.NET I implemented this filter before like the following code :

protected void BtnSearch_Click(object sender, EventArgs e)
        {
            if (Session["custid"] != null && Convert.ToInt32(Session["custid"]) > 0)
            {
                string sql = @"SELECT [LAB_RESULTS].ORDER_ID  as 'order number'
       ,APPROVED_DATE as 'Report Date'
       ,labtests.TestId as 'Test Id'  
       ,labtests.TestName as 'Test_Name'  
       ,labtests.culture as 'Culture' 
       ,CASE WHEN coalesce([RESULT_NUMBER],'') <> '' THEN RESULT_NUMBER WHEN coalesce(RESULT_REPORT, '') <> '' THEN RESULT_REPORT END AS Result  
       ,LAB_RESULTS.CUSTID as 'Customer No'
      ,Customers.CustName as 'Customer Name'
      ,LAB_RESULTS.patient_no as 'Patient No'
      ,Patients.PATIENT_NAME as 'Patient Name'
      ,patientcat.CatName as 'Category'
      ,Patients.Age AS 'AGE'
      ,gender.gendername as 'SEX'
      ,LAB_RESULTS.deptid AS 'DEPTID'
      ,TestsRanges.LowerLimit as 'Low Range'
      ,TestsRanges.UpperLimit as 'High Range'
      ,TestsRanges.text_range as 'Text Range'
      ,Lab_Hematology_Samples.COLLECTION_DATE as 'Collection Date'
      ,LAB_RESULTS.REQ_FORM_NO as 'REQ FORM NUMBER'


  FROM [dbo].[LAB_RESULTS]
  inner join LabTests on LabTests.testid=LAB_RESULTS.TESTID
    inner join TestsRanges on TestsRanges.TestId = LAB_RESULTS.TESTID
  inner join PatientCat on patientcat.CatId = TestsRanges.CatId
  inner join Customers on Customers.CustId=LAB_RESULTS.CUSTID
  inner join patients on Patients.Patient_No = LAB_RESULTS.patient_no
  inner join gender on gender.genderid = Patients.Gender
  inner join Lab_Orders on Lab_Orders.ORDER_ID = LAB_RESULTS.ORDER_ID
  inner join Lab_Hematology_Samples on Lab_Hematology_Samples.ORDER_ID = LAB_RESULTS.ORDER_ID

  where lab_results.deptid =1
  and   (Lab_Orders.catid = TestsRanges.CatId or PatientCat.CatId =5)
  and  LAB_RESULTS.EXAMINED_DATE is not null 
  and  LAB_RESULTS.APPROVED_DATE is not null
  and LAB_RESULTS.update_count in (select max(update_count) from LAB_RESULTS A where A.SAMPLE_STATUS = 6 and A.deptid=LAB_RESULTS.DEPTID  and A.ORDER_ID = LAB_RESULTS.ORDER_ID)
  and  (Patients.Gender = TestsRanges.PatientSex or TestsRanges.PatientSex = 1 )
  AND TestsRanges.machine_id = LAB_RESULTS.machine_id  ";

                string condition = "";
                string orderby = "";
                condition += " and LAB_RESULTS.custid ='" + Session["custid"] + "'";
                orderby += "order by LAB_RESULTS.ORDER_ID desc";
                if (DropDownTest.SelectedValue != "")
                {
                    condition += " and labtests.TestId ='" + DropDownTest.SelectedValue + "'";
                }

                if (TxtPatientNo.Text != "")
                {
                    condition += " and LAB_RESULTS.patient_no='" + TxtPatientNo.Text + "'";
                }

                if (txtreq.Text != "")
                {
                    condition += " and LAB_RESULTS.REQ_FORM_NO='" + txtreq.Text + "'";
                }

                if (TxtFromDate.Text != "")
                {
                    string date_day = Convert.ToDateTime(TxtFromDate.Text).Day.ToString();
                    string date_month = Convert.ToDateTime(TxtFromDate.Text).Month.ToString();
                    string date_year = Convert.ToDateTime(TxtFromDate.Text).Year.ToString();
                    string date = date_month + "/" + date_day + "/" + date_year;
                    condition += " and Lab_Hematology_Samples.COLLECTION_DATE > '" + date + "'";
                }

                if (TxtToDate.Text != "")
                {
                    string date_day = Convert.ToDateTime(TxtToDate.Text).Day.ToString();
                    string date_month = Convert.ToDateTime(TxtToDate.Text).Month.ToString();
                    string date_year = Convert.ToDateTime(TxtToDate.Text).Year.ToString();
                    string date = date_month + "/" + date_day + "/" + date_year;
                    condition += " and Lab_Hematology_Samples.COLLECTION_DATE <  '" + date + "'";
                }


                DataTable dt = func.fireDatatable(string.Format(sql + condition + orderby));
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }

How i will make same functionality in MVC and linq

If pcfileno textbox != null then search

if idno textbox != null then search ??

Enumerate through OrdersList before returning just like you did in old code using a for loop before you return. — jdweng, Jun 15 '20 at 08:17
See the OP's earlier question at https://stackoverflow.com/questions/62378695/why-the-linq-didnt-return-any-result-when-search-and-filter-data-in-asp-net-mvc for context. — mjwills, Jun 15 '20 at 08:31
This is easy yet troublesome to do just because we are not aware of your data context and models. Is lazy loading disabled? Do you have navigation properties defined? I highly suggest learning how to work with `IQueryable` and the approach can be the same as the one you used in ASP.NET; By that I mean you can do `if(...)query=query.Where(...)` for each of your parameters. — jegtugado, Jun 15 '20 at 08:33
@jdweng can you give me an example how i will execlude empty textbox and pass not empty textbox to the query ? — Abdullah, Jun 15 '20 at 16:42
@jegtugado can you please give an example how to pass parameters using IQueryable ? — Abdullah, Jun 15 '20 at 16:44
The parameters are in the string sql. So you just need to allow parameters in the string. I usually usr string.Format(" query string param {0} param {1}", param1, param2);. You can make the parameters global so you do not need a parameter in the Click method. — jdweng, Jun 15 '20 at 17:19
Are you using EntityFramework? Where does `DateTime? startdate,DateTime? enddate,string pcfileno,string idno` point to in your database? I'm not sure if we are playing the guessing game here. Please provide clear details so we can help you. — jegtugado, Jun 16 '20 at 07:58
@jegtugado yes i am using entityFramework . about the variables DateTime? startdate,DateTime? enddate,string pcfileno,string idno its the (textbox) input name i compare and select the data depending on the criteria entered in textbox — Abdullah, Jun 16 '20 at 08:41
@jegtugado hi sir can you please help me in my question how to print crystal reports in MVC i have old system in ASP.NET MVC and I need to use same reports and print crystal reports , this will be great help if you can help thank you so much . https://stackoverflow.com/questions/62498523/how-to-transfer-this-code-from-asp-net-to-asp-net-mvc-to-print-crystal-reports?noredirect=1#comment110528249_62498523 — Abdullah, Jun 21 '20 at 18:27

jegtugado · Accepted Answer · 2020-06-16T13:11:59.847

1

Assuming you are using EntityFramework and LazyLoading is disabled and your entities have navigation properties Lab_Sample_Status and Patient, you can utilize the IQueryable like the ff:

int custId = (int)Session["UserCustid"];
// eager load navigation properties
// query is IQueryable
var query = db.Lab_Orders.Include("Lab_Sample_Status").Include("Patient").Where(r => r.CUSTID == custId);

if (startdate.HasValue && enddate.HasValue)
{
    query = query.Where(r => DbFunctions.TruncateTime(r.Lab_Sample_Status.CollectionDate) >= DbFunctions.TruncateTime(startdate.Value) && 
        DbFunctions.TruncateTime(r.Lab_Sample_Status.CollectionDate) <= DbFunctions.TruncateTime(enddate.Value));
}

if (!string.IsNullOrEmpty(pcfileno))
{
    query = query.Where(r => r.Lab_Sample_Status.PcFileNo == pcfileno);
}

if (!string.IsNullOrEmpty(idno))
{
    query = query.Where(r => r.Patient.PatientNo == idno);
}

// .. You can continue to manipulate the query

// db results is determined upon calling ToList()
var model = query.ToList().Select(r => new Orders_Tables() { LabOrders = r, LabOrderStatus = r.Lab_Sample_Status, Patient = r.Patient });

return View(model);

If you don't have navigation properties like indicated above, you can do the ff:

int custId = (int)Session["UserCustid"];
// query is IQueryable
var query = db.Lab_Orders.Where(r => r.CUSTID == custId)
                .Join(db.Lab_Sample_Status, order => order.order_status, status => status.status_id, (order, status) => new { Order = order, Status = status })
                .Join(db.Patients, rec => rec.Order.patient_no, patient => patient.Patient_No, (rec, patient) => new Orders_Tables { LabOrders = rec.Order, LabOrderStatus = rec.Status, patients = patient });

if (startdate.HasValue && enddate.HasValue)
{
    query = query.Where(r => DbFunctions.TruncateTime(r.LabOrderStatus.CollectionDate) >= DbFunctions.TruncateTime(startdate.Value) && 
        DbFunctions.TruncateTime(r.LabOrderStatus.CollectionDate) <= DbFunctions.TruncateTime(enddate.Value));
}

if (!string.IsNullOrEmpty(pcfileno))
{
    query = query.Where(r => r.LabOrderStatus.PcFileNo == pcfileno);
}

if (!string.IsNullOrEmpty(idno))
{
    query = query.Where(r => r.patients.Patient_No == idno);
}

// db results is determined upon calling ToList()
var model = query.ToList();

return View(model);

PS: I'm hesitant to post this as an answer since OP's question isn't clear enough but the comment section has gotten a bit long.

edited Jun 16 '20 at 13:11

answered Jun 16 '20 at 09:13

jegtugado

5,081
1
12
35

Thank you for your time I will try it today and for sure i will give you the vote, from long time i searching for such solution in YOUTUBE and other sites and even purchased courses in udemy but they dont have complete search screen functionality for MVC like this :) – Abdullah Jun 16 '20 at 10:48
I highly suggest you read the [ASP.NET MVC - Contoso University](https://learn.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/creating-an-entity-framework-data-model-for-an-asp-net-mvc-application) tutorial. It has a clear and easy example of using Entity Framework with MVC. – jegtugado Jun 16 '20 at 11:22
in the second and third if statement query = query.Where(r => r.Lab_Sample_Status.PcFileNo == pcfileno); query = query.Where(r => r.Patient.PatientNo == idno); I cannot select the table name Lab_Sample_Status and Patient only i can select column name from table Lab_Orders first table in first line db.Lab_Orders.Include("Lab_Sample_Status").Include("Patient").Where(r => r.CUSTID == custId); ? the include not show other tables – Abdullah Jun 16 '20 at 11:44
@Abdullah that's a solution assuming you have navigation properties as mentioned above. I added a new one which should work for you without those properties. – jegtugado Jun 16 '20 at 12:04
in the second join it shows error : cannot implicitly convert type system.linq.iqueryable ......Lab_orders .... Lab_sample_status...patients to anonymous type lab_orders , lab_sample_status > an explicit conversion exists are missing a cast ? query = query.Join(db.Patients, rec => rec.Order.patient_no, patient => patient.Patient_No, (rec, patient) => new { Order = rec.Order, Status = rec.Status, Patient = patient }); – Abdullah Jun 16 '20 at 12:25
@Abdullah I updated the answer with the correct type instead of anonymous type. Also used the property names from your example. – jegtugado Jun 16 '20 at 12:51
Thank you so much Sir its working as expected and response time very fast , last thing if i want to order the result descending OrderByDescending db.Lab_Orders.OrderByDescending(x => x.ORDER_ID).ToList(); where I can do it ? – Abdullah Jun 16 '20 at 17:59
@Abdullah you can do `var model = query.OrderByDescending(r => r.LabOrders.ORDER_ID).ToList();`. You can do it anywhere before the `ToList()` so it will be ordered within the query. If you do it after, it will be done in app memory. – jegtugado Jun 17 '20 at 09:00

How do i filter and search selected fields only and exclude null textboxes in ASP.NET MVC

1 Answers1