I know that there were many questions, but the answer to them for yourself, unfortunately, could not find. I will probably write nonsense, do not judge strictly, I’m still learning)

If you use GenericRepository with the Entity Framework in a bundle with Unitofwork, in order to make complex requests with multiple join We will have to make the type of return value iQuerable and not ienumerable, which in turn is only suitable for those ORM that support Query Builder.

And if for example, do the implementation for Dapper, then I do not understand how to make complex requests, if you consider that repository is an in-memory collection of domain objects. Those. I mean, either you will have to first download the entire collection of objects from the base into memory, or to invent your version of Query Builder for ORM, with the help of any specifications. In general, is it really possible to use Repository with Dapper or Pure SQL, given that there is a constant need to jig a few tables?

I want to clarify what I ask this question within the framework of the repository pattern and not Dao.

Answer 1, Authority 100%

first and most importantly that you need to understand about repository – it returns subject area, even more – not just essence, but aggregates .

For example, consider the site with registered users. Users have an identifier, login and password.

class user
{
  Public int ID {Get; SET; }
  Public String Login {Get; SET; }
  Public String Password {Get; SET; }
}

Wonderful class entity , which is easy to display on the table in the database. Unfortunately, such a frontal implementation violates all the principles of the OOP. In particular, the user password cannot be changed if the user does not know the old password. Similarly, the user ID should not change at all, and the login change may require a serious script with a duplicate search in the database.

Class-essence should look like this:

class user
{
  Public int ID {Get; }
  Public String Login {Get; }
  Private String _Password;
  Public Void ChangePassword (String OldPassword, String NewPassword)
  {
    if (_password! = oldpassword)
      THROW NEW EXCEPTION ();
    _password = newpassword;
  }
  INTERNAL USER (INT ID, STRING LOGIN, STRING PASSWORD)
  {
    Id = id;
    Login = login;
    _password = password;
  }
}

This class implements business logic. This is the essence of the subject area. It could be displayed on the table in the database “As is”, if not to think about security. OWASP recommendations are offered in particular, never to store passwords in the open form, but to store their hashies, moreover, to calculate the haze to use a random seed.

For storage in the database, we will need another class with an excellent structure. Since it is used to communicate the object in the database, we will consider it the data transfer object (Data Transfer Object & NBSP; – DTO).

class userdto
{
  Public int ID {Get; SET; }
  Public String Login {Get; SET; }
  Public Byte [] passwordhash {get; SET; }
  Public Byte [] SALT {Get; SET; }
}

Now we have an object class User and the data transfer object UserDTO , with which we can fold users to the database. Pay attention, we do not overlay any special limitations on where users will be stored. UserDTO Determines only which fields should eventually persist.

Class User You can rewrite taking into account the existence of UserDTO :

class user
{
  Private userdto _dto;
  Private User (UserDTO DTO)
  {
    _dto = dto;
  }
  Public int id = & gt; _dto.id;
  Public String Login = & gt; _dto.login;
  Public Void ChangePassword (String OldPassword, String NewPassword)
  { 
Var Hash = Calculatehash (OldPassword, _dto.salt);
    if (! hash.sequenseequal (_dto.passwordhash))
      THROW NEW EXCEPTION ();
    _dto.passwordhash = Calculatehash (newpassword, _dto.salt);
  }
}

Now let’s see where repository appears in this scheme. The repository allows you to completely hide storage details. The only thing we need to know about the repository is large, so we cannot download all objects in memory.

At the subject area level – that is, physically in the same project, where User and UserDTO We describe only the repository interface.

interface iUserRepository
{
  User Create (String Login, String Password);
  User GetByid (int ID);
  User GetBylogin (String Login);
  User Updatelogin (int ID, String Login);
}

This interface can be implemented using ADO, Dapper, EF or in any other way. You can store users in SQL Server, Postres, MongoDB and even in an XML file if there are not very much.

Create a new project, let’s say, for ADO repository.

class adouserrepository: iuserrepository
{
  Private Func & Lt; SqlConnection & GT; _ConnectionFactory;
  Public AdouseRRepository (FUNC & LT; SQLConnection & GT; ConnectionFactory)
  {
    _ConnectionFactory = ConnectionFactory;
  }
  User GetByid (int ID)
  {
    Using (var connection = _ConnectionFactory ())
    Using (Var Command = Connection.CreateCommand ())
    {
      Command.commandText = "SELECT * FROM [USERS] WHERE ID = @ID";
      command.parameters.add ("@ id", id);
      . . .
    }
  }
}

Build queries and displaying data from requests to UserDTO are solved at the level of specific repositories. If you have an EF, then the repositories are simple.

We have an important practical question: if we filled UserDto , who will turn this object in the domain essence User ? The book about the GOF patterns describes the Memento pattern, which is just engaged in saving and restoring objects.

MEMENTO Logic differs from what we need, but not dramatically, so we can call your realization of the class to save and restore Mememto . It will be a separate class not to load User second responsibility. And, since it should have access to private members of the User , it will be embedded.

Public Class User
{
  Private userdto _dto;
  Public Static Class Memento
  {
    Public Static UserDto Serialize (User Domain) = & gt; domain._dto;
    Public Static User Deserialize (UserDTO DTO) = & gt; NEW User (DTO);
  }
}

Now the implementation of repositories, for example AdouseRepository can call User.memento.Deserialize to create User from UserDto .

user getbyid (int id)
{
  Using (var connection = _ConnectionFactory ())
  Using (Var Command = Connection.CreateCommand ())
  {
    Command.commandText = "SELECT * FROM [USERS] WHERE ID = @ID";
    command.parameters.add ("@ id", id);
    var Reader = Command.ExecuteReader ();
    if (! reader.read ())
      THROW NEW EXCEPTION ();
    Return user.memento.Deserialize (New Userdto
    {
      Id = Reader.getint32 (0),
      Login = Reader.getString (1),
      Passwordhash = reader.getbytes (),
      SALT = Reader.getBytes (),
    });
  }
}

The last question is why I stressed the difference between entities and aggregates . The unit is an integral entity, the elements of which are not very necessary for each other. In the database, such an entity is stored in several tables, but in the subject area it is one large object containing other entities or their collections.

Imagine that our users have a profile. This is a set of key-value parameters that are not very much. We could always load these parameters when downloading the user. If we do so, we hide from business logic such details as tables, links, external keys. Of course, they all remain at the implementation level, but do not penetrate up.

This is how you can load the unit together with the child parameters using the energetic EF load (INCLUDE method):

user getbyid (int id)
{
  Using (var context = _contextractory ())
  {
    var dto = context.users
             .Include (X = & GT; X.Parameters)
             .Single (x = & gt; x.id == id);
    Return user.memento.deserialize (DTO);
  }
}

As you can see, the implementation can be more complicated and easier. EF will send one request to the database and it will be able to correctly display the field values ​​to the UserDTO properties. We ourselves could send two requests to the database and implement a simple mapping if ADO was used.

In any case, this is a detail of the implementation, which we can not think at the level of the subject area.

Answer 2, Authority 17%

In general, is it possible to use the repository with Dapper or pure SQL,
Considering that there is a constant need to joyny a few
Tables?

Pattern Repository can be used with any data source. Even if it is a network resource or file.

Moreover, the repository should not know anything about the physical structure of the data and the method of their storage. For this there is a data source and their object model. The task of the repository to provide the facade interface to the data.