benefit of open connection with LINQ RRS feed

  • Question

  • Hi All

    I have a website uses the LINQ , and I have a large number of SQL statment

    is the  open LINQ connection  and close it after complated is usefull ?

    how I can get the best performance?

    please help me.


    Wednesday, March 16, 2011 4:09 PM


All replies

  • Can you explain better?

    If super optimal performance is a must then perhaps L2S is not for you and you are better off with raw TSQL

    With S2L there is a trade off performance for the LINQ goodness, nothing in life is free.


    Wednesday, March 16, 2011 4:43 PM
  • Hi Ora,

    Close connection has certain benefit. If you want to improve the efficiency of DB, the important point is the use of connection pool.

    When the connection end up, you should close the connection, so it will be returned to the pool. Generally, execute the connection.close() in try {}finally.

    Make an suppose, I create a database, it contains of table and stored procedure, then add data into database. The data may lost when end of the test. The database is in use, program will pop up error.

    You can read the blog "Don't forget to close your Linq to Sql connections". I think that it can help you.

    I hope this will help resolve your problem. If anything is unclear, please free feel to let us know.


    Related Links:



    Best Regards,


    Thursday, March 17, 2011 7:18 AM
  • may be the TSQL is more useful in some time  but I got the website depends on LINQ and it is not perfect ,

    I don't have a time to fix it

    there is many SQL Statment executed in the page load and in every time the LINQ open the connection and close it again

    I try to open the connection before first statment and close it in the end , is it useful?

    Thursday, March 17, 2011 8:31 AM
  • Most times there is no need to maintain a connection open the whole time, pooling has become very efficient so opening and closing connections is very fast for most cases

    In most scenarios, you only open the connection, use it and close it inmediately.


    Thursday, March 17, 2011 5:09 PM
  • thank you Serguey

    could you gave me the tips for pooling setting


    Wednesday, March 23, 2011 12:52 PM
    • Marked as answer by Ora-dbaabode Thursday, March 24, 2011 12:43 PM
    Wednesday, March 23, 2011 2:02 PM
  • thank you

     could you gave me more tips for Asp.net


    Thursday, March 24, 2011 12:42 PM
  • Note that the supplied like only applies if you explicitly open the connection on the context. If you allow the context to open and close itself, you don't need to explicitly close the connection. Also in that example, each request used a separate SubmitChanges which could be optimized using the UnitOfWork pattern which would only open the connection once for all updates.

    You do want to dispose the data context correctly.

    http://www.ThinqLinq.com http://www.LinqInAction.net - "LINQ In Action", The book is now available. Don't wait for the movie
    Thursday, March 24, 2011 3:15 PM
  • the

    Saturday, July 9, 2011 9:27 AM