How could you unit test against this bad Linq-To-SQL predicate?

Question

Given the following example code:

Table<Person> tableToQuery = MethodThatGetsReferenceToPersonTable();
string searchType = "NonUser";
IQueryable<Person> queryResults = tableToQuery.Where(p =>
    (p.IsMale == false) && // exclude male people
    type.Equals("User", StringComparison.OrdinalIgnoreCase)
        ? p.User != null : p.User == null);

I am new to L2S, but do have some of experience with EntityFramework. I would not expect the above query to work correctly in an ORM like EF because of the type.Equals that gets invoked in the predicate's ternary expression. I would instead expect EF to throw an exception since it cannot convert that part of the predicate into a (SQL) store expression.

With L2S, it seems that the .Where is returning data, but it is not excluding items where p.Male == true when type == "NonUser. I have already fixed the code above to pull the type.Equals ternary out of the predicate and return correct results, and am now trying to write a test to assert the correct results. The problem I am running into is that the L2S code is actually behind an IRepository<Person> interface.

So the actual code looks more like this:

string searchType = "NonUser";
ICriteria<Person> query = new Query<Person>(p =>
  (p.IsMale == false) && // exclude male people
  type.Equals("User", StringComparison.OrdinalIgnoreCase)
      ? p.User != null : p.User == null);
IQueryable<Person> = _peopleRepository.FindByQuery(query)

...and the _peopleRepository implementation just passes the query.Predicate as an argument to the L2S Table<Person>.Where.

Questions:

1. Is it correct that the L2S Table<Person>.Where is not returning the correct results because of the ternary expression in the lambda predicate? I assume so since taking the ternary out an generating separate ICriteria<Person> objects depending on the value of searchType is yielding correct results. However I am not so certain if this is because L2S cannot convert the ternary into a store expression, or if it is caused by something else.

2. Since the method under test depends on a IRepository<Person> instance, how could I actually write a unit test around this? Mocking the IRepository<Person> in a unit test would not allow us to test the effects of the lambda on real underlying data. Creating a FakePersonRepository backed by some kind of IList<Person> would not reveal the actual defect either because the above lambda with the ternary expression returns expected results using linq-to-objects. Is there any way we could mock part of L2S so that we can possibly generate SQL using the lambda, and write assertions against that instead?

3. Is this just something we have to do with an integration test and an actual L2S context with connection string, and cannot properly unit test? My definition of "integration test" means "connect to an actual database running in a separate process from the test runner using some kind of connection string", whereas "unit test" means "do everything in the test runner process".

Answer 1

1. Is it correct that the L2S (...) is not returning the correct results

It does return correct results, but not the results you expect, because you read the query as a human being and not as a compiler. The latter reads this:

tableToQuery.Where(p =>
    ((p.IsMale == false) && type.Equals("User", StringComparison.OrdinalIgnoreCase))
        ? p.User != null
        : p.User == null);

We humans tend to read this:

tableToQuery.Where(p => (p.IsMale == false) && 
     (type.Equals("User", StringComparison.OrdinalIgnoreCase)
        ? p.User != null
        : p.User == null));

2./3. how could I actually write a unit test around this?

Mocking and unit testing is virtually impossible. I stick to integration tests when I want to test the behavior of a data layer. For Entity Framework I collected some reasons for integration tests here. Much of it applies too LINQ-to-SQL as well.