Search database depending on chosen search criteria

Question

I'm following a YouTube series which teachs ASP.NET MVC. In the tutorial the teacher shows how to make a simple search functionality however in my case it's different.

I have search criteria: Studies (Dropdown), Country (Dropdown), Status (Dropdown) and Keyword (Input).

My question is how do I query the database to show the results depending on the search criteria that was chosen?

To be more clear: If the User has chosen Studies and Country only then the code should use values from Studies and Country to search the respective database column.

Click here for the UI Design

Table: Students

[StudentID]        INT          IDENTITY (1, 1) NOT NULL,
[StudentName]      VARCHAR (50) NOT NULL,
[StudentStudiesID] INT          NOT NULL,
[StudentCountry]   VARCHAR (50) NOT NULL,
[StudentCity]      VARCHAR (50) NOT NULL,
[StudentStatus]    VARCHAR (50) NOT NULL,
CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED ([StudentID] ASC),
CONSTRAINT [FK_Students_Studies] FOREIGN KEY ([StudentStudiesID]) REFERENCES [dbo].[Studies] ([StudiesID])

SearchController.cs

public class SearchController : Controller
{
    public ActionResult Index()
    {

        DatabaseEntitiesModel db = new DatabaseEntitiesModel();

        int Studies;
        int.TryParse(Request.QueryString["Studies"], out Studies);
        var Country = Request.QueryString["Country"];
        var Status = Request.QueryString["Status"];
        var Keyword = Request.QueryString["Keyword"];

        IQueryable <Student> SearchQuery = db.Students;
        List<SearchViewModel> SVM = SearchQuery.Select(x => new SearchViewModel
        {
            StudentID = x.StudentID,
            StudentName = x.StudentName,
            StudentCountry = x.StudentCountry,
            StudentCity = x.StudentCity,
            StudiesName = x.Study.StudiesName,
            StudentStatus = x.StudentStatus
        }).OrderByDescending(x => x.StudentID).ToList();

        return View( SVM );
    }
}

Answer 1

Reuse SearchQuery (items are lazy-loaded, until you call ToList()) and add as many specific Where() clauses/calls as you need:

// the type (IQueryable<Student>) should be defined explicitly
// details: https://stackoverflow.com/questions/21969154/cannot-implicitly-convert-type-system-linq-iqueryable-to-system-data-entity-d
IQueryable<Student> query = db.Students;

if(viewModel.Filter1 != null) {
    query = query.Where(i => i.SomeStudentProperty1 == viewModel.Filter1);
}

if(viewModel.Filter2 != null) {
    query = query.Where(i => i.SomeStudentProperty2 == viewModel.Filter2);
}

var result = query.ToList();

Answer 2

The easiest way to do this would be to test each condition and if it meets what you want, add a Where clause. Something like this:

 int.TryParse(Request.QueryString["Studies"], out Studies);
 var Country = Request.QueryString["Country"];
 var Status = Request.QueryString["Status"];
 var Keyword = Request.QueryString["Keyword"];

 IQueryable<Student> SearchQuery = db.Students;

 if(Studies > 0)
 {
    SearchQuery = SearchQuery.Where(s => s.StudiesID == Studies);
 }
 if(!string.IsNullOrEmpty(Country))
 {
    SearchQuery = SearchQuery.Where(s => s.StudentCountry == Country);
 }
 ...More conditions can go here

Because of Lazy Loading, the actual query isn't executed until you call .ToList(), or iterate over the collection. Hopefully, this gets you started on the right track.

Edit

In my haste, I changed your IQueryable to a var. Fixed. Also, as Erik pointed out, using Request.QueryString is not the way to go. You'll instead want to pass these values in to the action method. So, something like:

public ActionResult Index(int studies, string status, string country, string keyword)