CODEWITHSUNDEEP
c#mysqlasp.netsqllinq
user3489866
user3489866

Reputation: 363

How to optimise ASP.NET MVC controller to minimise the number of database query

My controller has different methods that use the same result returned by a stored procedure called by a LINQ query, Is there a way to create a global variable that contains the result after making only one call to the procedure ??

I tried creating a constructor but every time the variable (ListePays) is used a new query is executed 

public class BUController : Controller {
    private NAV_MAUIEntities db = new NAV_MAUIEntities();
    public DbSet<liste_pays> ListePays;
    public BUController() {
        ListePays = db.liste_pays();
    }
    public JsonResult BillPh(string Pays) {
        var x = from pays in ListePays
            where pays.Pays.ToUpper() == Pays.ToUpper()
            select pays.code_pays;
        string CodePays = x.FirstOrDefault().ToString();
    }
    public JsonResult BillPh2(string Pays) {
        var x = from pays in ListePays
            where pays.Pays.ToUpper() == Pays.ToUpper()
            select pays.code_pays;
        string CodePays = x.FirstOrDefault().ToString();
    }
}

Upvotes: 0

Views: 166

Answers (2)

Rune
Rune

Reputation: 8380

If the methods are all called as part the processing of the same HTTP request, just make sure some central actor (say, the action method) calls the procedure and passes the result to each method. 

public ActionResult MyAction()
{
  var data = db.liste_pays();
  Method1(data);
  Method2(data);

  return View();
}

On the other hand, if you want to share the result across HTTP requests, you can cache the result of the procedure call in a number of locations (this is not an exhaustive list):

Not knowing more about your specific scenario, I can't recommend one over the other. However, be aware that the two latter options will potentially share the data between users, which may or may not be what you want.

NOTE: As your code stands at the moment, the call to db.liste_pays() is inside the constructor of BUController, so it is called every time a controller is created, that is, every time a new incoming HTTP request arrives. In other words, your assumption that it is being called every time the variable is used is not entirely correct.

Upvotes: 1

Deblaton Jean-Philippe
Deblaton Jean-Philippe

Reputation: 11388

First of all, there is no need for a constructor into an ASP.NET MVC controller. What you did, means that every time a user makes a call to this controller, a call will be made to the DB to retrieve all your "pays".

I assume you are using Entity Framework. And the way you use Linq is "LinqToEntities". Do not worry about your DB calls, those are managed by the entity framework.

So, now, you just have to use linq that way :

public JsonResult BillPh(string Pays)
{
    string codePays = db.liste_pays.FirstOrDefault(f => 
                                               f.Pays.ToUpper() == Pays.ToUpper())
                                                .CodePays.ToString();
}

And the syntax for the query is called "Lambda expressions".

Good luck ;-)

Upvotes: 1

Related Questions