When does the DataContext will open a connection to the DB?

Question

I am using L2S to access my MSSQL 2008 Express server. I would like to know when the DataContext will open a connection to the DB? and will it close the connection right after it opened it?

For example:

var dc = new TestDB();  // connection opened and closed?

dc.SomeTable.InsertOnSubmit(obj);  // connection opened and closed?

foreach(var obj in dc.SomeTable.AsEnumerable())  // connection opened and closed?
{
    ...  // connection opened and closed?
}

dc.SubmitChanges();     // connection opened and closed?

Answer 1

Don't know about LINQ to SQL internals, but common sense tells that database connections must be opened the least amount of time possible, so it is reasonable to expect that SubmitChanges opens the connection, does its works, and then closes the connection.

Note anyway that you can have access to the DbConnection object used by the DataContext. Maybe if you open the connection by hand, the DataContext will not close it after SubmitChanges finishes (just guessing, never tried it).

Answer 2

A connection is made when you actually start enumerating and when you hit SubmitChanges (if there are changes to be made). I'm not sure if in the above code only ONE connection is opened and used, but I know in those two places I mentioned, you will invoke a connection.

You need to start looking into LinqPad and how to use it over at dimecasts. Also check out their series on the Delayed Execution features of Linq 2 Sql

Note, something like this (getTenSomethingElse(s,s,s)) does not query the db, at least not until you start enumerating over the return value

partial class MyDataContext
{
    // builds the tree to pull data from the db that matches a criteriea and pass it a ctor of another class
    public System.Linq.IQueryable<SomethingElse> getSomethingElse(string searchTerm, string searchValue, string orderBy)
    {
        var items = 
            from s in 
            this.Somethings 
            select new SomethingElse(s);

        return items.Where(searchTerm, searchValue).OrderBy(orderBy);
    }

    // calls the above method but adds take 10 to that tree
    public System.Linq.IQueryable<SomethingElse> getTenSomethingElse(string searchTerm, string searchValue, string orderBy)
    {
        var items = 
            from s in 
            this.getSomethingElse(searchTerm, searchValue, orderBy) 
            select s;

        return items.Take(10);
    }
}

IDK about you but I consider that to be fairly awesome considering all the work thats being done.

Oh btw, more info on that "Where(s,s)" extension can be found on ScottGu's awesome blog

Answer 3

LINQ to SQL is good to you: it will open and close the database connection in SubmitChanges().

Also, if you insert more than one record, the inserts will be contained in a single transaction, so either they all succeed or they all fail.

In the case of foreach(var obj in dc.SomeTable.AsEnumerable()), a database connection is opened and closed - just once, during which all the records are retrieved.