Searching with multiple columns using entity framework in MVC

Question

0.00/5 (No votes)

See more:

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
<a href="https://imge.to/i/vdroSh"></a>
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.

any one can share the complete search example with multiple columns few string parameters and few integer parameters

What I have tried:

C#

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));
}

Posted 1-Dec-19 23:34pm

Malikdanish

Updated 3-Dec-19 2:47am

Maciej Los

v2

Add a Solution

Comments

ZurdoDev 2-Dec-19 13:07pm

You have code for one search filter, right? So, just do the same thing and add more.

Malikdanish 2-Dec-19 13:10pm

Hi can you explain it a little bit ?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2019-12-03T02:48:00

If you want to return courses which match all filters, it's quite simple:

C#

var courses = db.Courses.AsQueryable();
if (!string.IsNullOrEmpty(SearchString))
{
    courses = courses.Where(s => s.Course_Name.Contains(SearchString));
}
if (Block_Id != null)
{
    courses = courses.Where(s => s.Block_Id == Block_Id);
}
if (Program_Id != null)
{
    courses = courses.Where(s => s.Program_Id == Program_Id);
}
if (Module_id != null)
{
    courses = courses.Where(s => s.Module_Id == Module_id);
}
if (Year != null)
{
    courses = courses.Where(s => s.Year == Year);
}

If you want to return courses which match any one of the filters instead, it gets more complicated. Something like this should work:

C#

var courses = db.Courses.AsQueryable();
var courseConditions = new List<IQueryable<Course>>();
if (!string.IsNullOrEmpty(SearchString))
{
    courseConditions.Add(courses.Where(s => s.Course_Name.Contains(SearchString)));
}
if (Block_Id != null)
{
    courseConditions.Add(courses.Where(s => s.Block_Id == Block_Id));
}
if (Program_Id != null)
{
    courseConditions.Add(courses.Where(s => s.Program_Id == Program_Id));
}
if (Module_id != null)
{
    courseConditions.Add(courses.Where(s => s.Module_Id == Module_id));
}
if (Year != null)
{
    courseConditions.Add(courses.Where(s => s.Year == Year));
}

switch (courseConditions.Count)
{
    case 0:
    {
        break;
    }
    case 1:
    {
        courses = courseConditions[0];
        break;
    }
    default:
    {
        courses = courseConditions.Aggregate((left, right) => left.Union(right));
        break;
    }
}