LinqToSql declare and instantiate DataContext best practice?

Question

What's the best practice in terms of setting up my DataContext for easy access in my extended LinqToSql classes?

For example, I have a "User" entity in my dbml and I want to add methods to that class like so:

Partial Public Class User

    Public Function GetUser(ByVal UserID as Integer) as User
         'Do Work
    End Function

End Class

In order to access my DataContext I'd have to declare it inside the method like so:

Partial Public Class User

    Public Function GetUser(ByVal UserID as Integer) as User
         Dim dc as New MyDataContext()
         Return (From u in dc.Users Where u.ID = UserID).Single()
    End Function

End Class

I'd like to not have to do that for every single method. Normally (if I weren't extending the LinqToSql dbml classes) I could just do this:

Partial Public Class User
    Private dc as MyDataContext

    Public Sub New()
         dc = new MyDataContext()
    End Sub

    Public Function GetUser(ByVal UserID as Integer) as User
         Return (From u in dc.Users Where u.ID = UserID).Single()
    End Function

    Public Function GetAllUsers() as IEnumerable(Of User)
         Return From u in dc.Users
    End Function

    'etc...

End Class

This would allow me to access the datacontext for each method without having to declare it newly each time. But of course you can't do that because the dbml already has a constructor. And adding code into the dbml always gets overwritten if anything ever changes.

Anyone have any good ideas on how to save myself some excess code here?

TIA!

Answer 1

First, make sure you are disposing your DataContext when you're done! He can be a heavy little bastard (edit not heavy to instantiate, but heavy to keep around if you keep using it without disposing); you don't want old DataContexts hanging around in memory.

Second, the DataContext is intended to represent a single logical transaction. E.g. you should create a new one every time you want to start a new transaction, and get rid of it when that transaction is complete. So for your purposes, that is probably the scope of the GetUser method. If you have a series of DB calls that need to be made as a group, they should all use the same DC before getting rid of it.

Answer 2

I think perhaps the real problem is that User is probably not the right place for an instance member call GetUser.

Answer 3

As Rex M said, the datacontext is intended to be instantiated, used, and disposed for each logical transaction. Patterns like this are sometimes called a "unit of work".

The most common way (that I know of) to do this is to instantiate your datacontext in a using block. I haven't used VB in a while, but it should look something like this:

Using dc As New MyDataContext()
   user = (From u in dc.Users Where u.ID = UserID).Single()
End Using

This not only reinforces the look of a transaction/unit of work (through the physical shape of the code), but it ensures calling Dispose() on your datacontext when the block ends.

See this MSDN page:

In general, a DataContext instance is designed to last for one "unit of work" however your application defines that term. A DataContext is lightweight and is not expensive to create. A typical LINQ to SQL application creates DataContext instances at method scope or as a member of short-lived classes that represent a logical set of related database operations.

Answer 4

There are a couple of different ways that you could go about this that would be good practice, I think. First you could use a Repository pattern where you query the Repository for an object, it goes out to the database, retrieves the object -- perhaps detaching it from the data context or keeping the data context around depending on the implementation of the Repository -- and returns it to you. The factory methods for your objects would be on the Repository, not the entities themselves. You'd probably use reflection and generics to minimize the number of methods you have to implement and keep your code DRY.

The other way, and the way LINQtoSQL was intended to be used natively IMHO, is to create the data context for each set of database operations that you intend to perform. In this the creation of the data context occurs outside the entity as well, usually in the class that is using the entities, not in the data layer at all. You could also add methods to the data context -- make your actual data context abstract and inherit from it -- using reflection again, to perform some of the common retrieval functions so that you don't have to repeat them. You'd probably have to use a database pattern like ActiveRecords where the id columns always have the same name to make this work.

On the other hand, you could look at using nHibernate or Castle's ActiveRecord instead of replicating either of the above in your own solution.

Answer 5

It may be easier if you leave the User class alone and allow the IDE handle it's creation.

Often I prefer to have a separate class handle the data retrieval. Let's say you call it the UserDataProvider and all calls to get a User instance eventually go through this class.

The constructor of UserDataProvider could instantiate a global instance of the data context object for reuse. It would look something like this (in C# and untested code so bare with me):

public class UserDataProvider 
{
    private UserDataContext _data = null;

    public UserDataProvider()
    {
        _data = new UserDataContext();
    }

    public User GetUser(int userID)
    {
        return _data.Users.FirstOrDefault(u => u.UserID == userID);
    }
}

Alternatively, you might place initialization in a property and access that property for data context usage.

public class UserDataProvider 
{
    private UserDataContext _dataContext;

    private UserDataContext DataContext 
    {
        get
        {
            if (_data == null)
                _data = new UserDataContext();

            return _data;
        }
    }

    public User GetUser(int userID)
    {
        return DataContext.Users.FirstOrDefault(u => u.UserID == userID);
    }
}