locked
How do I query a database only once? RRS feed

  • Question

  • User1434241939 posted

    I have a drop down that's populated from a database table and its corresponding model. Works great!

    However, every time I return to the controller, I have to read the table again.  This seems inefficient at best.  I tried to overcome this by creating a static object, but every time a View is rendered, the object is disposed and an error is thrown by the controller.  How do I overcome this?

    Here is my failed code:

    // List of bagtypes. Only want to get these once
    public static DbSet<Bagtypes> bagtypes {get; set;} = null;

    ... Other Controller code

    [HttpGet]
    public async Task<IActionResult> Edit(int? id, bool? copy)
    {

    // other edit stuff here


    if (bagtypes == null) // Only do this the first time through...

    {

    bagtypes = _db.Types;

    }

    bvm.TypeOfBag = bagtypes;

    return View(bvm);

    }

    Wednesday, May 6, 2020 12:42 PM

Answers

  • User753101303 posted

    Hi,

    The DbSet is a "proxy" to expose objects being taken later from the db. Don't store the DbSet but just a List<Bagtypes> :
    bagtypes = _db.Types;.ToList(); // then you are storing just .NET objects in memory.

    You could also hide this decision from your main code so it could be used easily from everywhere (and later you could change the underlying code to reload the list if the underlying table is updated or whatever)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 6, 2020 1:04 PM

All replies

  • User753101303 posted

    Hi,

    The DbSet is a "proxy" to expose objects being taken later from the db. Don't store the DbSet but just a List<Bagtypes> :
    bagtypes = _db.Types;.ToList(); // then you are storing just .NET objects in memory.

    You could also hide this decision from your main code so it could be used easily from everywhere (and later you could change the underlying code to reload the list if the underlying table is updated or whatever)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 6, 2020 1:04 PM
  • User475983607 posted

    However, every time I return to the controller, I have to read the table again.  This seems inefficient at best.  I tried to overcome this by creating a static object, but every time a View is rendered, the object is disposed and an error is thrown by the controller.  How do I overcome this?

    The standard pattern is caching.  Check the cache before making the DB call and if the cache exists then grab the data from the cache.  If the cache does not exist, make the DB call, populate the cache, and return the results.  Cache must have an expiration or the you can run out of memory.  

    Caching is covered in the fundamental docs.

    https://docs.microsoft.com/en-us/aspnet/core/performance/caching/memory?view=aspnetcore-3.1

    Wednesday, May 6, 2020 1:05 PM
  • User1434241939 posted

    I tried caching first since I wanted to learn how to do it, but ultimately I failed because caching requires static classes and I can't put a non-static context reference inside the constructor.  I suppose I could have passed the context as a parameter but that was starting to feel like a hack so I just used List instead of DbSet as PatriceSc suggested.

    Wednesday, May 6, 2020 2:14 PM