searching with multiple columns using entity framework in mv

Question

Hi I have courses table and its listing on index view , each course contains name,Program,block,Module and year , the listing is working fine now I want to search the courses based on any combinaiton of these parameters so below is my searching fields the Program populates Years dropdown and the year dorpdown populates Blocks dropdown and the blocks dropdown populates Modules . Below is my intext method it works well if i give the course name string as parameter but if i just want to find all courses in specific program OR Year OR block OR Module then it returns nothing below is my controller. public ActionResult Index(string sortOrder, string currentFilter, string SearchString,int? Block_Id,int? Program_Id,int? Year,int? Module_id, int? page) { ViewBag.Block_Id = new SelectList(db.Blocks, "Id", "Name"); ViewBag.Course_Category_Id = new SelectList(db.Courses_Category, "Id", "Category_Name"); ViewBag.Module_Id = new SelectList(db.Moduels, "Id", "Name"); ViewBag.Program_Id = new SelectList(db.Programs, "Id", "Program_Title"); ViewBag.Year = new SelectList(db.Years, "Id", "Name"); ViewBag.CurrentSort = sortOrder; ViewBag.NameSortParm = String.IsNullOrEmpty(sortOrder) ? "name_desc" : ""; ViewBag.DateSortParm = sortOrder == "Date" ? "date_desc" : "Date"; if (SearchString != null) { page = 1; } else { SearchString = currentFilter; } ViewBag.CurrentFilter = SearchString; var course = from s in db.Courses select s; if (!String.IsNullOrEmpty(SearchString) || Block_Id != null || Program_Id != null || Year != null || Module_id != null) { course = course.Where(s => s.Course_Name.Contains(SearchString) || s.Program_Id==Program_Id || s.Block_Id == Block_Id || s.Module_Id==Module_id); } switch (sortOrder) { case "name_desc": course = course.OrderByDescending(s => s.Course_Name); break; case "Date": course = course.OrderBy(s => s.Date_Created); break; case "date_desc": course = course.OrderByDescending(s => s.Date_Created); break; default: course = course.OrderBy(s => s.Course_Name); break; } int pageSize = 5; int pageNumber = (page ?? 1); ViewData["UserName"] = _ILogin.GetUserNamebyUserID(Convert.ToInt32(Session["UserID"])); return View(course.ToPagedList(pageNumber,pageSize)); } any one can share the complete search example with multiple columns few string parameters and few integer parameters

Gopaiah Nekarukanti · Answer

Try the below query to handle all filters without if else conditions. course = course.Where(s => (string.IsNullOrEmpty(SearchString) || s.Course_Name.Contains(SearchString)) && (Program_Id == null || s.Program_Id==Program_Id) && (Block_Id == null || s.Block_Id == Block_Id) && (Module_id == null ||s.Module_Id==Module_id));

Gopaiah Nekarukanti · Answer

Yuo can try somethnig like below course = course.Where(s => (string.IsNullOrEmpty(SearchString) || s.Course_Name.Contains(SearchString)) || s.Program_Id==Program_Id || s.Block_Id == Block_Id || s.Module_Id==Module_id); below is the one to handle All section scenario for all filters course = course.Where(s => (string.IsNullOrEmpty(SearchString) || s.Course_Name.Contains(SearchString)) && (Program_Id == 0 || s.Program_Id==Program_Id) && (Block_Id == 0 || s.Block_Id == Block_Id) && (Module_id == 0 ||s.Module_Id==Module_id));

Jenith Thakkar · Answer

//Condition when all is not null and you have a all values if(!String.IsNullOrEmpty(SearchString) && Block_Id != null && Program_Id != null && Year != null && Module_id != null) { course = course.Where(s => s.Course_Name.Contains(SearchString) && s.Program_Id==Program_Id && s.Block_Id == Block_Id && s.Module_Id==Module_id); } //Condition when SearchString Null or Empty. in that case you need to avoid serchstring else if(String.IsNullOrEmpty(SearchString) && Block_Id != null && Program_Id != null && Year != null && Module_id != null) { course = course.Where(s => s.Program_Id==Program_Id && s.Block_Id == Block_Id && s.Module_Id==Module_id); } //Condition for Block_Id Null or Empty. in that case you need to avoid Block_Id else if(!String.IsNullOrEmpty(SearchString) && Block_Id == null && Program_Id != null && Year != null && Module_id != null) { course = course.Where(s => s.Course_Name.Contains(SearchString) &&s.Block_Id == Block_Id && s.Module_Id==Module_id); } //same way you need to check all other parameters . . . .

Mark Tabor · Answer

else if(!String.IsNullOrEmpty(SearchString) && Block_Id == null && Program_Id != null && Year != null && Module_id != null) course = course.Where(s => s.Course_Name.Contains(SearchString) &&s.Block_Id == Block_Id && s.Module_Id==Module_id); Jenith Thakkar yes I inspect that if i am not giving the parameter for text it matches with "" string to find the course , but after check your query why you made Block_Id==null and all other != null , I mean is it complete query or i need to writer for other parameters as well can you guide me . Secondly as we are using entity framework is not there any Lambda expression which do the deal in an easy way rather than making lot of if and else to make the process lengthy Thanks for the assistance and help

Mark Tabor · Answer

course = course.Where(s => (string.IsNullOrEmpty(SearchString) || s.Course_Name.Contains(SearchString)) && (Program_Id == 0 || s.Program_Id==Program_Id) && (Block_Id == 0 || s.Block_Id == Block_Id) && (Module_id == 0 ||s.Module_Id==Module_id)); This does not work which i just select the Program ID without passing any other parameters (Gopaiah Nekarukanti)

Jenith Thakkar · Answer

You're not getting any value when you're not write any course because if (!String.IsNullOrEmpty(SearchString) || Block_Id != null || Program_Id != null || Year != null || Module_id != null) { course = course.Where(s => s.Course_Name.Contains(SearchString) || s.Program_Id==Program_Id || s.Block_Id == Block_Id || s.Module_Id==Module_id); } in this code what happen when there's a no search string but Program_Id is there then code will like course = course.Where(s => s.Course_Name.Contains("") || s.Program_Id==13... so when execute this lembda expression it will try to find the list of course with the name of "" and program id 13 then it will return no rows in that case you need to write seprate code if(!String.IsNullOrEmpty(SearchString) && Block_Id != null && Program_Id != null && Year != null && Module_id != null) course = course.Where(s => s.Course_Name.Contains(SearchString) && s.Program_Id==Program_Id && s.Block_Id == Block_Id && s.Module_Id==Module_id); else if(String.IsNullOrEmpty(SearchString) && Block_Id != null && Program_Id != null && Year != null && Module_id != null) course = course.Where(s => s.Program_Id==Program_Id && s.Block_Id == Block_Id && s.Module_Id==Module_id); else if(!String.IsNullOrEmpty(SearchString) && Block_Id == null && Program_Id != null && Year != null && Module_id != null) course = course.Where(s => s.Course_Name.Contains(SearchString) &&s.Block_Id == Block_Id && s.Module_Id==Module_id); . . . .