I am currently accessing my database directly when I get a GET-request by using something like this: _context.EXT_PRAMEX_INSERTS.Where(item => item.MatID == MaterialID. But my database does not change very often so I want to create a data-class that fetches all tables on startup into lists. And then I just parse the lists when I need to get any of the entries.

I was thinking that I could create a singleton-class that fetches all data on startup and then I just use that instance to get my data. But how do I access the context inside the singleton ? Or is there a better way to accomplish what I am trying to do ?

public sealed class ExternalDatabase
    {


        private static ExternalDatabase _instance = null;
        private static readonly object _instanceLock = new object();

        private List<EXT_PramexInserts>? pramexInserts;
        private List<EXT_PrototypeEndmills>? prototypeEndmills;
        private static List<IProducts> extAll = new List<IProducts>();
        
        ExternalDatabase()
        {


            pramexInserts = pramexInserts == null ? _context.Set<EXT_PramexInserts>().ToList() : pramexInserts;
            prototypeEndmills = prototypeEndmills == null ? _context.Set<EXT_PrototypeEndmills>().ToList() : prototypeEndmills;
            
            if (extAll.Count == 0)
            {
                extAll = extAll.Union(pramexInserts).ToList().Union(prototypeEndmills).ToList();

            }

        }

        public static ExternalDatabase Instance
        {
            get
            {
                lock (_instanceLock)
                {
                    if (_instance == null)
                    {
                        _instance = new ExternalDatabase();
                    }
                    return _instance;
                }
            }
        }


        public List<IProducts> GetExtraData(string materialid)
        {
            var result = extAll.FindAll(p => p.MaterialID == materialid);
            return result;
        }
    }

[ApiController]
[Route("DB")]
public class EXT_TablesController : ControllerBase
{
    private readonly Context _context;
    private static ExternalDatabase data = ExternalDatabase.Instance;
    public EXT_TablesController(Context context)
    {
        _context = context;
    }
    //...

    [HttpPost]
    public IEnumerable<IProducts> Post([FromBody] EXT_Request request)
    {

        string selectedTable = null;

        var (MaterialID, Type, Brand) = request;

        if (Type != null && Brand != null)
        {
            selectedTable = $"EXT_{Brand}_{Type}";

            switch (selectedTable)
            {
                case "EXT_PRAMEX_INSERTS":
                    //var items = _context.EXT_PRAMEX_INSERTS.Where(item => item.MatID == MaterialID);
                    return data.GetExtraData(MaterialID);

                default:
                    return null;
            }
        }
        return null;
    }
}

CodePudding user response：

It's better to keep your DbContext short-lived.

If you want to use the context in a singleton class, you can try DbContextFactory.

Also you may want to have a look at the secondary level cache pattern. Here is a ready-to-go package.

CodePudding user response：

I don't know if it is the best answer but I followed one of the tips that Mehdi Dehghani suggested in the comment above. I created a new context inside the singleton and it solved my problem.