Passing multiple values from Ajax to MVC Controller then build a dynamic query string using ExecuteQuery to get Json result

Question

So A little bit background here:

I have built about 9 cascaded dropdowns/listboxs in the webpage and user can select depending on what they like from step-1 to the step-final. After that, they click submit button and I have built a collector to collect all of what they have selected and use Ajax to pass those parameters(and arrays, some of those are multi-selection)to MVC Controller.

The number of data back into the database is gigantic, if I don't set up any restrictions to get all of data in SQL database, that's going to be more than 4 millions of records in more than 15mins. So I can't use any typical approaches like I set up repositories in model and retrieve data in controller using linq, this is the way how I built those dropdowns and listboxs. But in these situation, this method will throw OutOfMemoryExecption and Json will throw maxJsonLength error to me and based on the time effecive, I can't take any single process more than seconds.

One thought about this solution is that, I get data only from what exactly user selected. Actually this is also how it works in sql database. Because no one wants to get everything in this database, they just want what exact field they want to see in this data view. If there are some solutions that I can directly talk from controller to sql database. I can put those selections in a query and pass it to sql database, then I return result only depends on what this query says. That will be very efficient.

Code:

So I wrote Ajax to pass those vars to controller:

$("#submit").click(function(){
    $.ajax({
        cache: false,
        type: "GET",
        url: "url/url",
        data: { 
            "DateFrom": datefrom,
            "DateTo": dateto,
            "dropdownselectioninstep1": step1,
            "dropdownselectioninstep2": step2,
            "multiselectionstep3": intarraystep3,
            "multiselectionstep4": stringarraystep4,
            //....step 5,6,7,8,9,etc...
        },
        success: function (data) {...}
    });
});

New Update - So I took these suggestions below and did some research online, I came up with some ideas that can deal with this giant database view. So the idea is writing a query that directly talks to SQL in controller so that I can get very detailed data using those values that I collected from Ajax, but I came across with some problems because of my lack of C# knowledge... Here's my controller:

public ActionResult GetAllCases(string DateFrom, string DateTo, string step1, string step2, int[] intarraystep3, string [] stringarraystep4, ...)
  {
      // So I worte a long query to match the dynamic needs from those dropdown/listbox 
      //selections and if it can be passed through database directly, 
      //I can return very specific data set instead of waiting SQL to
      // process the full millions of records in database.

 var query = "SELECT" + step1 + ","  + step2 + "FROM view_cases as w WHERE " + step2 + " IN(" + intarraystep3 + ") AND" w.date "BETWEEN '" + DateFrom + "' AND '" + DateTo + "'";

  var result = _dataContext.ExecuteQuery<// what things do I need to put here?>(@query).ToList();

  return Json(result, JsonRequestBehavior.AllowGet);
  }

I realized when I put a break point in this class, I did see this query passed to SQL, and successfully returned the correct amount of data set in Locals window. But If I put my view view_cases in var result = _dataContext.ExecuteQuery<view_cases>(@query).ToList(); It returns all of the columns in this view but not returning the columns that I put in the SELECT clause in query. So I guess I must missed some steps or should I create a model class or something like that?

If you have any question about what I'm trying to explain or any ideas about this question, drop a line to me. Any comment will be highly appreciated!

Thank you

Kevin.

Answer 1

you can create a queryObject and generate the sql query base on the queryObject

 $.ajax({
        cache: false,
        type: "GET",
        url: "url/url",
        data: {
            criteria: {
             "DateFrom": datefrom,
             "DateTo": dateto,
             "dropdownselectioninstep1": step1,
             "dropdownselectioninstep2": step2,
             "multiselectionstep3": intarraystep3,
             "multiselectionstep4": stringarraystep4,
             //....step 5,6,7,8,9,etc...
            } 
        },
        success: function (data) {...}
    });

public ActionResult GetAllCases(QueryObject criteria)
  {
      // do not do sql query from your controller, your controller should be
      // as thin as possible, use a QueryService to encapsulate the query logic
      queryService.queryAllCase(criteria);

  }

public class QueryObject {
   public DateTime DateFrom { get; set; }
   ...

}

if the data is gigantic, conside to paginate your result.