To me the repository pattern is about putting a thin wrapper around your data access methodology. LINQ to SQL in your case, but NHibernate, hand-rolled in others. What I've found myself doing is create a repository-per-table for that is extremely simple (like bruno lists and you already have). That is responsible for finding things and doing CRUD operations.
But then I have a service level that deals more with aggregate roots, as Johannes mentions. I would have a UserService with a method like GetExistingUser(int id). This would internally call the UserRepository.GetById() method to retrieve the user. If your business process requires the user class returned by GetExistingUser() to pretty much always need the User.IsInRoles() property to be filled, then simply have the UserService depend upon both the UserRepository and RoleRepository. In pseudo code it could look something like this:
public class UserService
{
public UserService(IUserRepository userRep, IRoleRepository roleRep) {...}
public User GetById(int id)
{
User user = _userService.GetById(id);
user.Roles = _roleService.FindByUser(id);
return user;
}
The userRep and roleRep would be constructed with your LINQ to SQL bits something like this:
public class UserRep : IUserRepository
{
public UserRep(string connectionStringName)
{
// user the conn when building your datacontext
}
public User GetById(int id)
{
var context = new DataContext(_conString);
// obviously typing this freeform but you get the idea...
var user = // linq stuff
return user;
}
public IQueryable<User> FindAll()
{
var context = // ... same pattern, delayed execution
}
}
Personally I would make the repository classes internally scoped and have the UserService and other XXXXXService classes public so keep your consumers of the service API honest. So again I see repositories as more closely linked to the act of talking to a datastore, but your service layer being more closely aligned to the needs of your business process.
I've often found myself really overthinking the flexibility of Linq to Objects and all that stuff and using IQuerable et al instead of just building service methods that spit out what I actually need. User LINQ where appropriate but don't try to make the respository do everything.
public IList<User> ActiveUsersInRole(Role role)
{
var users = _userRep.FindAll(); // IQueryable<User>() - delayed execution;
var activeUsersInRole = from users u where u.IsActive = true && u.Role.Contains(role);
// I can't remember any linq and i'm type pseudocode, but
// again the point is that the service is presenting a simple
// interface and delegating responsibility to
// the repository with it's simple methods.
return activeUsersInRole;
}
So, that was a bit rambling. Not sure if I really helped any, but my advise is to avoid getting too fancy with extension methods, and just add another layer to keep each of the moving parts pretty simple. Works for me.