Repository Pattern and Linq to sql

Question

I 'm trying to implement user authentication and authorization using roles table, user table and a xref table having userid, roleid.

For implementing generic repoistory to update role, insert role, add user, add user to role, update user, update user role, authenticate user, add user session to audit etc do i have write seperate functions for each or can i use one generic method for similar functions. There are some other operations like joining user to other table and get top 5 rows based on conditions, Inserting in 3 tables (joined on a key) using single form etc

I'm confused reading many articles and samples, bcos samples i see don't go into deep implemenation except CRUD operations.

Can someone guide me or direct me to a good sample/article?

Answer 1

Ideally you should not be guessing the requirements for the repository. One way to do this is to write the code that uses the repository first, extending the repository's interface as needed. This is achievable if you write unit tests that provide test doubles for the repository.

Answer 2

linq2sql is an implementation of ActiveRecord pattern which is kinda antipattern, so if you want to use repository pattern then you must not use linq2sql but something else where it is possible to have the separation of concerns (separate class for entity and separate class for data access)

or probably you can somehow, i don't know about that :D

Answer 3

First off, follow what Frank Schwieterman stated. Let your repositories grow as their usage grows. In addition, learn about and make use of the IQueryable interfaces. L2S, along with Entity Framework, LINQ to nHibernate, and some of the newer ORM's like SubSonic and Telerik's ORM, all support the IQueryable interface.

In the cases where you need mutable queries from your repository, but still want the benefit of interchanging OR mappers if needed, IQueryable is a powerful tool. Assume something like the following:

public class ProductRepository: IProductRepository
{
    public Product GetByID(int id);
    public IList<Product> GetAll();
    public void Insert(Product product);
    public Product Update(Product product);
    public void Delete(Product product);
}

This is a pretty common repository, with the bare bones common methods. Over time, you may end up with a bunch more methods:

public IList<Product> GetByOrder(Order order);
public IList<Product> GetByCategory(Category category);
public IList<Product> GetByQuantityInStock(int quantityInStock);

This is also pretty common, and depending on how you like to approach the problem, perfectly acceptable. However, in the long run, your repository can grow to an unwieldy size, and its interface will always be changing. You also loose the real benefit of using an OR mapper behind the scenes.

You can keep the original, simple repository interface, but still provide yourself with a lot of flexibility, if you change one method:

public IQueryable<Product> GetAll();

Your repository now returns a query, rather than a list of already-retrieved objects. You are now free to use this query like any other LINQ enabled object:

var productsWithLowStock = productRepository.GetAll().Where(p => p.Quantity < 10);

var orders = orderRepository.GetAll();
var productsWithOrders = productRepository.GetAll().Where(p => orders.OrderLines.Any(ol => ol.ProductID == p.ProductID));

Once you start using the IQueryable interface with your repositories, you gain the best of both worlds: A mockable abstraction around your lower-level data access, as well as the power of dynamic querying within your code. You can take the concept a little farther, and create a base Repository class that implements IQueryable itself, allowing you to eliminate the need for the GetAll() call, and simply query the repository directly (albeit with another degree of complexity.)