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

Question

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

Rune · Accepted Answer · 2014-05-28 18:19:57Z

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.

Deblaton Jean-Philippe · Accepted Answer · 2014-05-28 18:12:15Z

1

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

answered May 28, 2014 at 18:12

Deblaton Jean-Philippe

11.4k4 gold badges53 silver badges68 bronze badges

3 Comments

user3489866 Over a year ago

thanks,but if i have another methos that need to use the result of db.liste_pays() ,instead of calling the stored procedure again ,i want to put the result once for all in a variable ListePays that is used directly

Deblaton Jean-Philippe Over a year ago

Why do you store it inside your DB then? If it does almost never have to change, you can serialize your object "ListePays" inside a .obj file.

user3489866 Over a year ago

Every time its a diferent result .

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related