CODEWITHSUNDEEP
c#asp.net-mvcentity-framework
JohnC1
JohnC1

Reputation: 861

Is it a bad idea to keep creating a new Database Entity?

I would like to learn more about creating a Database Entity (Connection?). Coming from PHP/MySQL, i was creating only one connection and was reusing that over and over using a connection pool.

I noticed in MVC, I create new db entity almost every chance I get. Is this really the correct way to do so in real world example?

For example, I have a code that tells the user how many unread messages they have left on every refresh/page view. It goes like this:

    public int UnreadMessages()
    {
        using (dbEntities db = new dbEntities())
        {
            return db.messages.Select(M => M.status == "Unread").Count();
        }
    }

On my _Layout.html, I have a line that calls this code. So, this is being executed on every request. The way I look at it, this is terrible way of doing it because I keep creating a new connection? or maybe this is the way it was supposed to be done on MVC.

Could someone please explain to me, the best way of doing this? or maybe provide some links that may help me understand this better?

P.S. I am also not too sure how db connection on MVC works. Wether 1 connection is made and a new db entity(Not a connection, rather just a call?) is created on requests or a new brand new connection is made on requests.

Upvotes: 1

Views: 231

Answers (3)

Akash Kava
Akash Kava

Reputation: 39956

Two things, Entity framework uses underlying ADO.NET which supports powerful connection pooling, and connections to database are closed instantly by context. So you don't need to worry about connection pooling.

However, it is not good idea to create and destroy context every time for single operation. Ideally only one context should be created for entire lifecycle of a request. Since creating and destroying context is little costly it does affect performance at high load.

Controller has OnDispose method, and this is how you can easily implement it,

 public abstract class DBController : Controller {

     public MyDbContext DbContext { get; private set; }

     public DBController() {
         DbContext = new ... 
         HttpContext.Items["DbContext"] = DbContext;
     }

     protected override void OnDispose() {
         DbContext.Dispose();
     }

 }

And your every Controller should be derived from DBController. And in Layout file you can use same context by retrieving HttpContext.Items["DbContext"]

This way same context will be used for entire request. And yes, for every request new context will be created. EF is not designed to be thread safe and should not be reused for different requests.

Upvotes: 1

Mattias Åslund
Mattias Åslund

Reputation: 3907

In the mvc world, views (including layout) should only use data from the model or include partial views with RenderAction() that get their models from other actions.

You ask about connections and EF though, and while opening and disposing objects frequently isn't great you need to understand that EF has its own connection pool, so if your action calls a bunch of methods that all create and dispose their own dbEntities() object, only one connection to the actual database will be used.

Upvotes: 1

Hiệp Lê
Hiệp Lê

Reputation: 634

In my opinion, it's recommended to use using to create new instance as it will automatically close connection after the connection and dispose the instance.

If you want to use a Global variable, you need to make sure to open and close db connection in each method, then it still be fine.

However, the bad thing that you are doing is to call Database connection from your _Layout.html, that is the view, should only render the view; not to connect to DB.

Upvotes: 0

Related Questions