Linq2sql: singleton or use, best practices

Question

What is the preferred practice of using linq2sql (in asp.net mvc applications): create a "singleton" for the DataContext , for example:

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

or get a new instance if needed in using :

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

using using leads to some restrictions in the code. therefore, I prefer to use singleton one. is this a strange practice?

UPD :
explanation why i use 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. because of this, returns IQueryable - I can continue to compose a query without executing it, so here only a lazy result is returned.
if I rewrote the same code with using - I will not be able to return IQueryable (because db will be deleted and IQueryable will be lost too), and I would change it to List. and now this method will return a "huge" list from which I will filter data by the previous function.

I hope I will describe in sufficient detail.

Answer 1

Linq to Sql data contexts are NOT thread safe and should only be used in the context of a single thread. Using the singleton template not only contradicts the standard linq2sql methods, but also leads to serious problems if your application gets under any serious load.

EDIT:

In response to your limitations regarding the block used, try implementing 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 will allow you to call your method inside the use 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 response to your comment about opening a different 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 every actual operation will be. Regardless of whether you create 1 or 4 datacontexts, if you perform 4 operations on the database, 4 sqlconnections will be opened. However, keep in mind that .NET uses the SQL Server connection pool, so each operation does not require the creation of a completely new SqlConnection, but only retrieving the existing connection pool from the pool and reopening the connection

Answer 2

Linq to SQL requires you to create a context for each operation. In fact, the data loading options can only be set to fulfill the first request, so if you want to do download hints, you should do it that way. However, if you have a three-tier architecture, you will run into the problem that objects from one datacontext cannot work with objects from another context.

Working around is a real pain, so we just executed the context for each request for web content and the local stream approach for Windows services, etc.

Answer 3

You probably want to control the lifetime of your context in order to have scope for one web request and only have one context for the lifetime of this web request cycle.

A Google search for a "context-oriented object object" or "objectcontext lifetime" (or datacontext for l2s).

eg. 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 context management code in your base controller class.