How to properly pass objects in N-layer architecture?

Question

ASP.NET

Good afternoon! Please help me understand the three-tier architecture and the transfer of objects from level to level.
The application uses a three-tier architecture:
DAL (Data Access Layer) contains:

• Entity classes (database entities)
  
• DB context class (Entity Framework 6)
  
• GenericRepository class
  

  public interface IGenericRepository<TEntity> : IDisposable where TEntity : class
   {
          IQueryable<TEntity> GetAll();
          TEntity Find(object id);
          void Insert(TEntity item);
          void Update(TEntity item);
          void Delete(object id);
          void Dispose(bool disposing);
  }
  
  public class GenericRepository<TEntity> : IGenericRepository<TEntity> where TEntity : class
  {
          //...
  }

  
  
• UnitOfWork class
  

  public interface IUnitOfWork : IDisposable 
  {
          GenericRepository<T> Repository<T>() where T : class;
          void Save();
          void Dispose(bool disposing);
  }
  
  public class UnitOfWork<TDbContext> : IUnitOfWork where TDbContext : DbContext, new()
  {
          //...
  }

  
  

Entity Framework 6 is used to work with the database.
BLL (Business Logic Layer) contains:

• DTO classes
  
• service classes
  

UI (Presentation layer) contains:

• ASP.NET MVC project in which ViewModels are defined
  

Each of the layers is a separate project, DAL and BLL are Class Library projects, and UI is ASP.NET MVC 4. The
question is as follows:
Now it is done so that the GenericRepository method GetAll returns an IQueryable selection of the entire table of the specified TEntity entity, and already in the service more complex conditions apply to this sample, for example:

public class BookService : IBookService
{
  private UnitOfWork<MyContext> Db { get; set; }
  
  public BookService()
  {
    Db = new UnitOfWork<MyContext>();
  }

  public List<BookDTO> Exmaple()
  {
    Mapper.CreateMap<Book, BookDTO>();
    return Mapper.Map<IEnumerable<Book>, List<BookDTO>>(b.Repository<Books>().GetAll().Where(b => b.BookAccepted == true && b.ParentBookId == null).ToList());
  }
}

Those. it turns out that the real selection and much more complex queries (for example, with three join tables) are made in the service, but because of this, you have to connect Entity FrameWork in a project with a BLL level.
How correct is this?
It might be better to make a separate repository and make a method in it (in this example, List GetAcceptedBooks()). But in this case, the following problem arises:
For example, you need to build a report that can be obtained from the database by attaching several tables, something like:

SELECT b.BookName, b.BookDate, SUM(bp.BillPrice) as BookSum, p.PaidSum, np.NotPaidSum
FROM ... as b
LEFT JOIN ... as bp on ...
LEFT JOIN 
(
  SELECT BookId, SUM(BookId) as PaidSum
  FROM ...
  GROUP BY BookId
) as p on p.BookId=b.BookId
....

that is, the query returns not a database entity, but a new object. Then, at the BLL level, a DTO object is created, for example BookReportDTO, which corresponds to the result of such a selection. But if such a selection is in the repository, it turns out that the repository method should return a List and the DAL level will be associated with the BLL level.
Will it be right?
It turns out that the general question can be defined as follows: How to organize the transfer of complex objects (NOT ENTITIES) from the DAL level to the BLL level? Or leave only CRUD queries at the DAL level in the repository, and already make complex selections in the BLL level services and create collections of DTO objects there?
Thanks in advance for any help!

Answer 1

GenericRepository and its GetAll method is about nothing. In the sense that you do not need such a repository - it does nothing and does not abstract anything. You expose IQueryable - and you get problems on your head. Yes, this is a very tempting thing, but still a repository in order to leave all possible requests there, otherwise they go far into the application logic. You need a specific repository (the GenericRepository interface might come in handy) with specific queries that return an IEnumerable. This is its essence - at the repository level, an PRIMARY selection is made (using IQueryable), which is essentially a query to the database and the materialization of all NECESSARY objects, and then fine filtering can be done with objects in the IEnumerable collection.
I would make my own repository for every such thing. It will be DIFFERENT from the entity repository, as there will not be, for example, Update. This will be a repository of RECORDS, not objects. Otherwise, everything is the same - all possible requests should be collected there. And a class can and should also be created like BookReport, only it will be immutable, i.e. will behave like a record. And then do whatever you want with it.
Here I do not understand. Well, let it return List or IEnumerable, where do you see the connection?
In general, you may need to worry less about separating DAL and BLL. As a rule, this leads to an anemic data model, and this will not bring you anything but harm. I don’t know where this fashion came from, I always managed to leave 80% of the logic in the entity classes themselves, and everything was ok.