From typed DataSets to LINQ.... hmmm.

[Al Beback]

Hi folks, I'm currently considering migrating a small web project from typed DataSets to LINQ. It has a business layer that basically handles the moving of the various data pieces (DataTables or DataRows) back to the pages based on the business rules. I've taken advantage of the TableAdapters (defined in the DataSet) to graphically add Fill and Get methods to do all the data retrieval. I've also added a few of my own custom ones via the partial classes. In other words, the business layer doesn't have any queries in it; it just calls methods inside the TableAdapters. This works great! Now with LINQ to SQL, I'm trying to mimic this same architecture, replacing the typed DataSet with a generated DataContext. I changed my business code to return either an instance of the generated classes or Lists of them (instead of DataRows or DataTables). Not a problem. The problem is, I no longer have the individual TableAdapters I used for keeping my Get and Fill methods for all my custom queries. So I have only four possible options, as far as I can see: 1. Move all the methods I had in my TableAdapters into the business classes and invoke them through there. I would essentially be mixing data access logic with business logic which I really don't want to do. 2. Move all the methods I had in my TableAdapters into the DataContext partial class itself. This would be bad because I would end up with dozens of methods of different tables inside one class. I would probably have to rename some of them to avoid name collisions. In other words, instead of userTA.GetByEmail(email); now it would be dataContext.GetUserByEmail(email). And the class would only get bigger with each new table. 3. Move all the methods I had in my TableAdapters into the generated partial classes themselves. This would mean that the methods would have to become static, and I would have to add to each one a DataContext parameter to work with. So instead of userTA.GetByEmail(email) now it would be User.GetByEmail(dataContext, email). Yuck! It just doesn't look right, especially since I want to avoid static methods in a multi-threaded app. 4. Move all the queries inside the TableAdapter methods into stored procedures and map them to the generated objects via the IDE. Now, putting queries in stored procedures is considered a good idea for performance reasons, but I'm not keen on doing it because it's the only sane way to implement a multi-layer architecture with LINQ to SQL.

[Al Beback]

I found the solution, thanks to the magic of Extension methods. (They kick ass!) I decided to move my TableAdapter methods as extension methods of the IQueryable<_Entity_> class. So now for example, my User methods are inside their own class and they take this IQueryable<User> as the first parameter, like this:

public static User GetByEmail(this IQueryable<User> users, string email)
{
return users.SingleOrDefault(u => u.Email.ToLower() == email.ToLower());
}

This allows me to then say in my Business code:

var user = dataContext.Users.GetByEmail(email);

It's cool stuff because extension methods basically allowed me to modify the Users property of the DataContext from the outside, and keep all the user-related data-access logic neatly organized like it was before inside the TableAdapter. Cheers! Al

- Is God willing to prevent evil, but not able? Then he is impotent. - Is he able, but not willing? Then he is malevolent. - Is he both able and willing? Whence then is evil? - Is he neither able nor willing? Then why call him God? Epicurus