linq2sql: singleton or using, best practices

Question

what is the preferred practice when linq2sql using (in asp.net mvc applications): to create "singleton" for DataContext like:

partial class db
{
    static db _db = new db(global::data.Properties.Settings.Default.nanocrmConnectionString, new AttributeMappingSource());

    public static db GetInstance()
    {
        return _db;
    }
}

or to retrieve new instance when it needed within using:

using (db _db = new db())
{
    ...
}

the usage of using brings some limitations into code. so I prefer to use singleton one. is it weird practice?

UPD:
explanation why i'm using singleton:

public class UserGroupRepository
{
    public static IQueryable<Group> RolesFor(string username)
    {
        User user = UserRepository.WithUsername(username);

        return from g in db.GetInstance().Groups
                join ug in db.GetInstance().UsersGroups on g.Id equals ug.GroupId
                where ug.UserId == user.Id
                select g;
    }
}

i have this method. due to it returns IQueryable - I can continue composing query without it executing, so here just lazy result returns.
if i rewrite the same code with using - i cannot be able to return IQueryable (because db will be disposed and IQueryable will be lost too), and i would change it to List. and now this method will return "huge" List from which i will filter data on previous function.

i hope i describe enough detailed.

Answer 1

Linq to Sql data contexts are NOT thread safe, and should only be used within the context of a single thread. Using the singleton pattern is not only contrary to standard linq2sql practices, but will result in serious problems if your application comes under any sort of serious load.

EDIT:

In response to your limitations with regard to the using block, try implement your RolesFor method as an extension method:

public static IQueryable<Group> GetUserRoles(this Database db, string username)
{
        return from g in db.GetInstance().Groups
                join ug in db.GetInstance().UsersGroups on g.Id equals ug.GroupId
                where ug.UserId == user.Id
                select g;
}

This would allow you to call your method inside a using block from anywhere:

using(Database db = createContext())
{
     IQueryable<Group> queryable = db.GetUserRoles("MyUsername");
     // from here on you can query the queryable object
     var groups = (from g in queryable
                   where g.Name == "MyRole"
                   select g).ToList();
}

EDIT 2

In reponse to your comment about opening another connection to the sql server for each instance of the data context. Creating a datacontext will not open a connection to the sql server, but each actual operation will. Regardless of whether you create 1 or 4 datacontexts, if you are performing 4 operations on the database, 4 sqlconnections will be opened. However, keep in mind that .NET uses a sql server connection pool, so each operation doesn't require the creation of an entirely new SqlConnection, but only the retrieval of an existing one from the connection pool and the reopening of the connection

Answer 2

You'll probably want to manage the lifetime of your context to be scoped to a single web request and have only one context for the lifetime of that web request cycle.

Google search for 'web scoped objectcontext' or 'objectcontext lifetime' (or datacontext for l2s).

e.g. http://dotnetslackers.com/articles/ado_net/Managing-Entity-Framework-ObjectContext-lifespan-and-scope-in-n-layered-ASP-NET-applications.aspx

In MVC2 you can put the context management code in your base controller class.

Answer 3

Linq to SQL wants you to create a context per operation. In fact, the Data Load Options can only be set for executing the first query, so if you want to do loading hints, you have to do it that way. However, when you have a 3-tier architecture, you will run into the problem that objects from one datacontext can't really work with objects from a different context.

Working around that is a real pain, so we just did a context per request for web stuff and a thread local approach for Windows Services and the like.