Unit testing with Data Access Layer

what is a good way to write unit tests with a LINQ to SQL DAL?

Currently I am doing some database testing and need to create helper methods that access the database, but I don't want those methods in my main repo's.

So what I have is two copies of the DAL, one in my main project and one in the Test project. Is it easier to manage these things if I create a separate project for the data layer? I'm not sure which way is a better way to approach this.

If I do create a data layer project would I move all my repo's to that project as well? I'm not sure how to properly setup the layers.

Thanks

I would use the Repository Pattern outlined in the September 2009 article in Visual Studio magazine titled "Eliminate Database Dependencies in Test-Driven development". I have been using this pattern since I read the article with great success. This pattern will help to decouple your data layer and write good unit tests.

This will require you to adopt an n-tier architecture and create a separate data layer, but in the long run it is worth it.

Here is a link to the online article. Repository Pattern

I'm using Linq2Sql for my DAL layer and I have it as a seperate project. In my domain project I have a repository interface which I then implement using a custom Linq2SqlCarRepository in my DAL project, this class wraps up the generated Linq2Sql classes.

eg. In Car.Core project

 public interface ICarRepository
 {
    IQueryable<Car> GetAllCars();
    void Add(Car);
 }

I then have a implementation of the interface which wraps up access to the generated Linq2Sql class.

Car.Data project

public class SqlCarRepository : ICarRepository
{
    private CarDataContext _context;

    public SqlCarRepository()
    {
        _context = new CarDataContext();
    }

    #region ICarRepository Members

    public IQueryable<Car> GetAllCars()
    {
        return _context.Cars;
    }

I then have a test project Car.Data.Test which then uses mocks to mock the ICarRepository and tests away. I think this is slightly different to what you're describing. But I think you want to try and seperate you're DAL from your application so it's a peripheral that could be swapped out if you wanted.

I haven't got all the completely sorted but I currently have these projects:

Car.Core         --- All the interfaces and domain objects, DTO's etc
Car.Core.Tests   --- The tests of the core business logic.
Car.Web          --- Asp.net MVC frontend
Car.Web.Tests    --- Tests for the website
Car.Data         --- The Linq2Sql stuff lives in here
Car.Data.Tests   --- The tests for the DAL layer

That's what I've got currently though it might not be the best way of doing things now.

I'd recommend reading through The Onion Architecture and looking at the MVC StoreFront videos for inspiration; good luck.