Reduce number of query in ASP.NET controller for polling data

Question

I'm trying to make a request every 10 seconds to a webApi controller in ASP.NET. The controller has to check in the database (SQL Server) if there are new data available (Using enitity framework). Here is the controller side

public class RemoteCommandController : ApiController
{
    public Command Get(String id)
    {
        Command command = null;
        ProxyCommand proxycommand = null;
        Device deviceSearch;

        using (var systemDB = new DB())
        {
            deviceSearch = systemDB.Devices.Include("CommandList").Where(d => d.Name.Equals(id)).SingleOrDefault();
            command = deviceSearch.CommandList.LastOrDefault();
        }


        if (command.IsExecuted == false)
        {
            proxycommand = ConvertingObjects.FromCommandToProxy(command);

        }                     

        return proxycommand;
    }
}

I want to avoid to query the DB everytime I call this controller. Is there a way to improve this controller in order to reduce the number of querys? I was thinking of using Session, but I don't think is a good idea...

I would setup [SignalR](https://www.asp.net/signalr/overview/getting-started/introduction-to-signalr) — jamesSampica, Dec 05 '16 at 18:21

score 1 · Answer 1 · edited Dec 06 '16 at 09:32

1

Just use output caching. Add this attribute to your controller:

[OutputCache(Duration=10, VaryByParam="none")]
public class RemoteCommandController : ApiController
{
    //etc.....

Increase or decrease the duration as needed. If the command list never changes, you can set the duration absurdly high and the controller will only get called once.

edited Dec 06 '16 at 09:32

Daniele

668
2
10
25

answered Dec 05 '16 at 20:35

John Wu

50,556
8
44
80

It can help me, but what about if the database data changes? The output cache is able to have a feedback of database changings? – Daniele Dec 06 '16 at 08:32
Yes, you can reset the cache using [this method](http://stackoverflow.com/questions/16194140/how-to-invalidate-cache-data-outputcache-from-a-controller). – John Wu Dec 06 '16 at 10:30

Reduce number of query in ASP.NET controller for polling data

1 Answers1