How should I organize common linq queries (or even SQL queries)?

Question

I use the Entity Framework. Let's say I have a realy complex linq query, that I need in multiple parts of my application, some services, some controllers.

Is there any design pattern I should follow?

First I thought, the Repository Pattern would perfectly be valid:

MyDto myRepository.GetDataFromComplexQuery();

But we shouldn't use the Repository Pattern with Entity Framework. The answer in the linked question is talking about using Services instead. However, I don't want to boil my service classes with a lot of (duplicating) complex query logic.

So I am wondering how I should design this and how to organize it in the solution?

Something like this feels very wrong:

namespace Foo.Data.DataHelpers
{
    public class ComplexQuery
    {
        private DbContext _ctx;

        public ComplexQuery(DbContext ctx)
        {
            _ctx = ctx;
        }

        public MyDto GetData()
        {
            query = //...
            return query.Single();
        }
    }
}

Also sometimes the query is to complex and needs to replaced by plain old SQL for performance reason. But I don't think, this would change the answer?

Answer 1

I think by "service pattern" the answer is recommending you use an interface that abstracts the source of the data. It differs from the repository pattern in that the repository pattern is an abstraction that models a data source as if it were a collection with operations for listing, updating, and creating new elements. Whereas services abstract the actions required by the client in a request-response approach. The naming of the service would depend on the purpose of the queries, and you would group similar queries under the same service. In the following example I use the domain of report building as a domain that requires some complex queries.

interface IReportDataService {
  MyDto getReportData();
} 

public class DbReportDataService : IReportDataService {
  private DbContext _ctx;

  public DbReportDataService(DbContext ctx) {
    _ctx = ctx;
  }

  MyDto IReportDataService.getReportData() {
    query = //...
    return query.Single();
  }
}

// Usage
public class ReportBuilder {
  private IReportDataService _dataService;

  public ReportBuilder(IReportDataService dataService) {
    _dataService = dataService;
  }

  public Report fillReport(...) {
    MyDto data = _dataService.getReportData();
  }
}

By using this abstraction, you can unit test ReportBuilder by mocking IReportDataService. The implementation can use Linq or SQL scripts--it doesn't matter to ReportBuilder. Your composition root should construct a single instance of DbReportDataService and inject it into components that need it (ReportBuilder).