Am I writing correctly in asp.net Core?

Question

ASP.NET

Good afternoon, please tell me if I'm doing the right thing.
Basically, when accessing the database, I use repositories. For example,

I create a model

public class Photos:BaseEntity
{
public int id {get;set;}
public string file {get;set;}
....
}

create repositories

public interface IRPhotos<T> where T:BaseEntity
    {
        Task New(T photo);
        Task Delete(int id);
        Task<Photos> Avatar(string iin);
        Task<IList<Photos>> ShowAll(string iin);
    }

I also create my own file for each interface

private string connectionString;

        public ReposPhotos(IConfiguration configuration)
        {
            connectionString = configuration.GetValue<string>("DBInfo:ConnectionString");
        }

        internal IDbConnection Connection
        {
            get
            {
                return new NpgsqlConnection(connectionString);
            }
        }
public async Task<Photos> Avatar(string iin)
        {
            using (IDbConnection dbConnection = Connection)
            {
                dbConnection.Open();
                return (await dbConnection.QueryAsync<Photos>("SELECT * FROM social.photos WHERE [email protected] AND avatar=true",new { iin=iin})).FirstOrDefault();
            }
        }

There is a case when I directly access the database in the controller, for example

public async Task<IActionResult> TPricing()
        {
            using (IDbConnection dbConnection = Connection)
            {
                dbConnection.Open();
                return View(await dbConnection.QueryAsync<TPricing>("SELECT DISTINCT(iin) FROM uchast.t_pricing"));
            }
        }

So my question is, am I doing the right thing? Can it be used like this? Advantages and disadvantages?
asp.net core+dapper+postgresql connection

Answer 1

1) The repository is a collection, and methods of working with it as with a collection, nothing more.
2) Controller - an intermediary between View and Model (MVC style). It should not contain any business logic, which means opening a connection and executing a request with its text. There should be a call to a competent performer - service.
I recommend to look at - CleanArchitecture https://github.com/jasontaylordev/CleanArchitecture
Look at ready-made sites:
https://github.com/nopSolutions/nopCommerce
https://github.com/simplcommerce/SimplCommerce
And a cool monster - https:/ /github.com/dotnet-architecture/eShopOnCont...
Everything will not be remembered at once and will not go in, it's a long way.
The best course from 0 to norms of understanding -https://codewithmosh.com/p/become-a-full-stack-net...
It consists of three parts, where the author creates a ready-made application like a mini social network with music, sort of.
You can watch him as a whole, and do as he does, you won’t miss.

Answer 2

I would suggest using the SqlCommand class and parametrizing the query. This immediately cuts off a lot of potential SQL-related vulnerabilities. At a minimum, injections can no longer be afraid.