Entity Framework Database Connection in Single-Threaded Application

Question

I have a unique (or so I think) problem - we have an ASP.NET web app using MVC principles. The project will be at most single threaded (our business requires single point of control). We are using Entity Framework to connect to the database

Problem:

We want to query our database less frequently than every page load.

I have considered putting our database connection in a singleton but am worried about connecting to in too infrequently -- will a query still work if it connected a significant time ago? How would you recommend connecting to the database?

What do you mean by a "single-threaded ASP.NET" application? You mean one request has to finish before another begins? That seems like a huge problem for scalability (much worse that the impact of re-creating connections) — D Stanley
– D Stanley, Commented Nov 1, 2017 at 21:33
A single-threaded web application doesn't make any sense. Either you're not describing this properly or you're headed for a major disaster. — xxbbcc
– xxbbcc, Commented Nov 1, 2017 at 21:38
Single-threaded is the best term I could come up with. We have a server that will only have one user at a time. Only one computer will have access to the server for that matter For this reason scalability is not an issue — Tyler Barron
– Tyler Barron, Commented Nov 1, 2017 at 21:47

D Stanley · Accepted Answer · 2017-11-01 21:31:31Z

4

How would you recommend connecting to the database?

Do NOT use a shared connection. Connections are not thread-safe, and are pooled by .NET, so creating one generally isn't an expensive operation.

The best practice is to create a command and connection for every database request. If you are using Entity Framework, then this will be taken care of for you.

If you want to cache results using the built-in Session or Cache properties, then that's fine, but don't cache disposable resources like connections, EF contexts, etc.

If at some point you find you have a measurable performance problem directly related to creating connections or contexts, then you can try and deal with that, but don't try to optimize something that might not even be a problem.

answered Nov 1, 2017 at 21:31

D Stanley

153k12 gold badges189 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tyler Barron Over a year ago

Okay! That makes it easier for us

Himzo Tahic · Accepted Answer · 2017-11-01 21:25:14Z

If you want to get data without connecting to the database, you need to cache it - either in memory, in a file or in whatever mean of storage you want, but you need to keep it in front of the DB somehow. There is no other way known to me.

If by connecting you mean building a completely new SqlConnection to your DB, then you can either rely on connection pooling (EF is smart enough to keep your connections alive for some minutes even after you finish your business) or you can just create connections and keep them alive inside your application by not closing them instantly (i.e. keeping track of them inside a structure).

But you should definitely consider if this is REALLY what you want. The way EF does it internally is most of the time exactly what you want.

Some further reading: https://learn.microsoft.com/en-us/aspnet/mvc/overview/older-versions/getting-started-with-ef-5-using-mvc-4/implementing-the-repository-and-unit-of-work-patterns-in-an-asp-net-mvc-application

Collectives™ on Stack Overflow

Entity Framework Database Connection in Single-Threaded Application

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related