CODEWITHSUNDEEP
c#asp.netdatabaseperformancelinq-to-sql
Concerned Bystander
Concerned Bystander

Reputation: 21

Is this really how you use Linq-To-Sql?

I saw an interesting link here about Linq-To-Sql: More efficient database access

Looking at my own code I realized that I also haven't been disposing the DataClassesDataContext. Is this a problem? I don't know, but I went searching through my code and added using (DataClassesDataContext db = new DataClassesDataContext()) throughout my code.

Now, here is the interesting thing. We log almost every page access and transaction in our code. So I now have a block of code that looks like this:

protected WriteLog(String activity)
{
   using (DataClassesDataContext db = new DataClassesDataContext())
   {
       ... Do db write with Linq
   }
}

This is a very heavily used function, with at least 50 calls/second at any time. So before I assume we suffered from potential 'leaks', which is a problem. But now it seems we that we won't be leaking DB resources, but instead we will be spending a lot of time just opening and closing database connections. This can't be right, can it? Is the above code now opening and closing a DB connection for every single log entry by our users, adding even more overhead than just leaving the DataClassesDataContext to be garbage collected at a future time?

Upvotes: 2

Views: 205

Answers (1)

Adam Robinson
Adam Robinson

Reputation: 185643

Your question has a false premise: you're assuming that by not disposing of the context that you're leaving the connection open for other users. This is incorrect. All you're doing is deferring the closing of the connection until garbage collection; subsequent requests will always open a new connection if they're instantiating the context.

That being said, the context class is lightweight (as are database connection objects). Your connections are automatically pooled, so there is not necessarily a 1:1 ratio between database connection objects and actual database connections.

By properly disposing of the class by enclosing it in a using block, you're now using it correctly (no pun intended).

Upvotes: 5

Related Questions