I have a very simple LINQ query where I call a view to get an object, basically it looks like this:
context.view.FirstOrDefault(p => p.id == key)
Key and id are strings, which is less than ideal I think. However, when I run it against my local database, it works fine.
However, when I deploy my application, I get the message:
[Win32Exception (0x80004005): The wait operation timed out]
[SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to
completion of the operation or the server is not responding.]
etc.
Other queries against views work quickly and as expected. They return lists rather than single objects, I'm not sure if that makes a difference.
I'm unable to connect to my hosted database from my development environment due to how the environment is configured, so my troubleshooting options are somewhat limited. I have double checked that the view is the same across both the local and the hosted environment.
When I run the query in SSMS against the hosted database it executes in under a second.
I'm not sure what good next steps would be as far as troubleshooting and would appreciate any advice.
I'm using Entity Framework 6, database first.
Those were great suggestions from Diana and William. Unfortunately, I didn't have permission to view query plans in the hosted database.
After simplifying the result returned by the view on the hosted database, I determined that it was in fact slowness in querying the view that was the root of the issue. While the query itself is simple, the view is pretty complex. I'm going to see if I can get the requisite permissions on the database to make an indexed view.