design-patternsquery-optimizationservice-layerdata-layerstestability

Where should we do the filtering - at the data layer or the service layer to optimize testability and efficiency?


We use LINQ in EF to filter the data (to optimize queries). However, on testing our services, we cannot test the queries - so should we have filtering also done at the service layer?


Solution

  • If your data layer exposes the fetched collection as List<T> that means it materializes all the data in memory. When you apply filtering inside the service layer that means you perform client-side filtering which is far from optimal. Unless you want to reuse the same data collection from the data layer multiple times.

    So, if you want to optimize your queries for speed then do the filtering logic inside the data layer. From testing perspective if you want to perform unit testing against the data layer function then you use of the entity framework mocking library. Some works better with Moq, others with NSubstitute.

    For integration testing you could use EF's in-memory database provider BUT please bear in mind that it behaves differently in many cases than a real database. Please read the following article about EF testing.