With Entity Framework Core you have the option to use an in-memory database for your unit tests. EF6 doesn't provide this option. The following is a simple way to allow you to test your query logic anyway.

First, we create the repository definition. We make it an abstract class instead of the "usual" interface. We create a public "interface" method, and a protected sibling that takes an IQueryable as an additional parameter.

public abstract class FooRepository {
  public abstract List<Foo> FindExpiredFoos(DateTime deadline);

  protected List<Foo> FindExpiredFoos(DateTime deadline, IQueryable<Foo> fooDataSource) {
    return fooDataSource.Where(f => f.ExpirationDate <= deadline).ToList();
  }
}

Next we create our first repository implementation. We open a context (or use an injected one) and delegate the actual querying to the base class.

public class EfFooRepository : FooRepository {
  public List<Foo> FindExpiredFoos(DateTime deadline) {
    using(MyContext context = new MyContext()) {
      return FindExpiredFoos(deadline, context.Foos)
    }
  }
}

And for testing we create a mock implementation that substitutes the context with a list.

public class MockFooRepository : FooRepository {
  private readonly List<Foo> _fooDataSource = new List<Foo>();

  public List<Foo> FindExpiredFoos(DateTime deadline) {
    return FindExpiredFoos(deadline, _fooDataSource.AsQueryable());
  }

  public void Add(Foo foo) {
    _fooDataSource.Add(foo);
  }
}

In the unit test we can then setup our test data and test our, admittedly not very complex, query logic.

[TestMethod]
public void ShouldReturnOnlyExpiredFoos {
  MockFooRepository repo = new MockFooRepository();
  AppService service = new AppService(repo);

  List<Foo> expiredFoos = new List<Foo>() {
    new Foo { ExpirationDate = new DateTime(2022, 1, 1) },
    new Foo { ExpirationDate = new DateTime(2022, 1, 2) }
  }
  repo.AddRange(expiredFoos);
  repo.Add(new Foo { ExpirationDate = new DateTime(2022, 1, 3) });

  List<Foo> actual = service.GetExpiredFoos(new DateTime(2022, 1, 2))
  CollectionAssert.AreEquivalent(expiredFoos, actual);
}

This approach has a few limitations:

• There are differences between the production and the test query, as the EF implementation uses LINQ to Entities while the mock implementation uses LINQ to Objects. Some examples are:
  • some methods behave differently, e.g. Concat allows for unlimited string parameters in LINQ to Objects, but is limited to 5 or so in LINQ to Entities.
  • query execution is different between the two as well. E.g. SomeList.FirstOrDefault().SomeProperty in a subselect always works when using LINQ to Entities, but will throw when using LINQ to Objects if SomeList.FirstOrDefault() is null.
• this won't work if you're using async EF methods such as ToListAsync(). You can circumvent this issue by passing in a parameter to use ToList() when calling from a unit test. Admittedly this is not really pretty because it pollutes production code with test code - but IMHO better than no test.

Therefore the test implementation and the production implementation don't behave exactly the same, but are close enough for most cases.