I am new in Linq query. I just want to make my filtering dynamic if 1 of the fields or more in image attached is/are null:

Since I am used to hardcode query, I can't derive the code if I only choose of those 3 items above. How to make this code below become dynamic?

var query = db1.sub_project.Where
            (x =>
                (
                
                (x.sub_project_id == Geotagging_SP_ID && x.sub_project_id != null) &&
                (x.sub_project_name == sub_project_name && x.sub_project_name != null) &&
                (x.cycle_id == Cycle && x.cycle_id != null) &&
                (x.prov_code == Province && x.prov_code != null) &&
                (x.region_code == Region && x.lib_regions.region_code != null) &&
                (x.city_code == Municipality && x.city_code != null) &&
                (x.brgy_code == Barangay && x.brgy_code != null) &&
                (x.lib_project_type.major_classification_id == major_classification_id && x.lib_project_type.major_classification_id != null) &&
                ((x.final_physical_accomplishment >= final_physical_accomplishment_From && x.final_physical_accomplishment != null) && (x.final_physical_accomplishment <= final_physical_accomplishment_To && x.final_physical_accomplishment != null)) &&
                (x.mode_id == mode_ids && x.mode_id != null) &&
                (x.project_type_id == Project_Type && x.project_type_id != null)

                )
                

            ).Select(x => new 
            {
                PkID = x.ID,
                Geotagging_SP_ID = x.sub_project_id, 
                sub_project_name = x.sub_project_name, 
                Project_Type = x.lib_project_type.Name,
                Project = x.lib_modality.name,
                last_updated_date = x.last_updated_date, 
                created_date = x.created_date, 
                sql_Tuning_Final_physical = x.lib_physical_status.Name,
                sql_Tuning_Final_Physical_Accomplishment = x.final_physical_accomplishment,
                Cycle = x.lib_cycle.cycle_name, 
                Region = x.lib_regions.region_name, 
                Province = x.lib_provinces.prov_name, 
                Municipality = x.lib_cities.city_name,
                Barangay = x.lib_brgy.brgy_name
            }).ToList();

            result = query; 

Thank you.

CodePudding user response：

To perform the filtering, define all your input parameters as nullable, for example:

public class FilteringParameters
{
   public int? Geotagging_SP_ID { get; set; }
   public string sub_project_name { get; set; }

   //other properties
 }

Then check all the parameters of your filter in your method as follows: If the opposition is Null or Empty, filter your query based on it. Finally, call the ToList or ToListAsync method.

public async Task<List<ProjectViewModel>> GetProjectsAsync(FilteringParameters parameters)
{
    var query = db1.sub_project.AsQueryable();

      if (parameters.Geotagging_SP_ID != null)
      {
         query = query.Where(x => x.sub_project_id == parameters.Geotagging_SP_ID);
      }

      if (!string.IsNullOrWhiteSpace(parameters.sub_project_name))
      {
         query = query.Where(x =>x.sub_project_name == parameters.sub_project_name);
      }

     if(parameters.Cycle != null)
      {
         query = query.Where(x => x.cycle_id == parameters.Cycle);
      }


      //etc
      //check other filtering parameters
      //and

   var result = await query.Select(x => new ProjectViewModel
        {
            PkID = x.ID,
            Geotagging_SP_ID = x.sub_project_id,
            sub_project_name = x.sub_project_name,
            Project_Type = x.lib_project_type.Name,
            Project = x.lib_modality.name,
            last_updated_date = x.last_updated_date,
            created_date = x.created_date,
            sql_Tuning_Final_physical = x.lib_physical_status.Name,
            sql_Tuning_Final_Physical_Accomplishment = x.final_physical_accomplishment,
            Cycle = x.lib_cycle.cycle_name,
            Region = x.lib_regions.region_name,
            Province = x.lib_provinces.prov_name,
            Municipality = x.lib_cities.city_name,
            Barangay = x.lib_brgy.brgy_name
        }).ToListAsync();

   return result;
}