Linq-SQL query logic in unit testing

I am trying to write some unit tests for my code. In out project we are using the Linq-SQL objects that are created from the DBML. I am trying to figure out how i would need to test the following logic.

Say for example i need to get a count of records from a table using LINQ.

var objectCount = (from x in DB.MyRecords
 开发者_高级运维                 where x.DateAdded.Date == DateTime.Now.Date
                  select x).Count(); //For example this will return 4

if(objectCount > 3)
{
   //Do some logic here
}
else
{
   //Do some logic here
}

Now my understanding is that a unit test is not really a unit test if you access the DB.

My query is also quite more involved than that as it the data structure has Forign keys that need to be maintained.

Now the next problem is that due to the fact that we are using LINQ-SQL object we are not using interfaces therefore we cannot really use a mocking framework (or can we????) i would like to know what the process is to be able to unit test this.

You need an intermediary classes between your DBML and your business logic code to be able to test. Take a look at Repository pattern for more details about this.

Let me give you a very simple example, suppose you have a Product table and you want to test whether total number of product in your database is less than 10 products. You need a repository class that supply you the number of product in the database in a form of integer, you don't care about how the method gets the number as long as you get the number (think of abstraction). Take a look at the following code snippet:

public interface IRepository
{
    int CountProduct();
}

// your implementation of repository against real database
public class DBLinqToSQLRepository : IRepository
{
    // some constructor here

    public int CountProduct()
    {
        // your code here to return the actual number of product from db.
    }
}

// your implementation of fake repository that will provide fake data for testing
public class FakeRepository : IRepository
{
    private List<Product> products = new List<Product>();

    // some initialization here

    public int CountProduct()
    {
        return products.Count;
    }
}

Then in your test environment you can use this FakeRepository instead of the actual database repository to test your business logic.

You could encapsulate this code into a Repository class which may have the following method e.g.

public int GetNumberOfItemsAddedToday()
{
    return from x in DB.MyRecords
           where x.DateAdded.Date == DateTime.Now.Date
           select x).Count();
}

I would recommend using an interface for the repository, so that you could mock out the repository (using something like Moq, RhinoMocks). This blog post has a good tutorial on creating a repository for LINQ To SQL related code, but there are plenty of examples on the web and SO on how to do this also.

You could then unit test the above code

When you are writing unit tests which interact with the database, those tend to be called integration tests.