SQL Server How to implement (Include) in Onion architecture

ercv8c1e  于 2023-08-02  发布在  其他
关注(0)|答案(1)|浏览(86)

I want to get Company with its employees and this is my query

Executed DbCommand (87ms) [Parameters=[@__companyId_0='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
      SELECT [t].[CompanyId], [t].[Address], [t].[Country], [t].[Name], [e].[EmployeeId], [e].[Age], [e].[CompanyId], [e].[Name], [e].[Position] 
      FROM (
          SELECT TOP(1) [c].[CompanyId], [c].[Address], [c].[Country], [c].[Name]
          FROM [Companies] AS [c]
          WHERE [c].[CompanyId] = @__companyId_0
          ORDER BY [c].[Name]
      ) AS [t]
      LEFT JOIN [Employees] AS [e] ON [t].[CompanyId] = [e].[CompanyId]
      ORDER BY [t].[Name], [t].[CompanyId]

This is what I tried.

namespace Entities.Models
{
    public class Company
    {
        [Column("CompanyId")]
        public Guid Id { get; set; }
        public string Name { get; set; }
        public string Address { get; set; }
        public string Country { get; set; }
        public ICollection<Employee> Employees { get; set; }
    }
}
namespace Entities.Models
{
    public class Employee
    {
        [Column("EmployeeId")]
        public Guid Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
        public string Position { get; set; }   
        [ForeignKey(nameof(Company))]
        public Guid CompanyId { get; set; }
        public Company Company { get; set; }
    }
}
namespace Shared.DataTransferObjects
{
    public class CompanyWithEmployeesDto
    {
        public Guid Id { get; init; }
        public string Name { get; init; }
        public string FullAddress { get; init; }
        public IEnumerable<EmployeeDto> Employee { get; set; }
    }
}
namespace Shared.DataTransferObjects
{
    public class EmployeeDto
    {
        public Guid Id { get; init; }
        public string Name { get; init; }
        public int Age { get; init; }
        public string Position { get; set; }
    }

}
namespace API.Helpers
{
    public class MappingProfile : Profile
    {
        public MappingProfile()
        {
            CreateMap<Employee, EmployeeDto>();
            CreateMap<Company, CompanyWithEmployeesDto>()
            .ForMember(c => c.FullAddress,
                    opt => opt.MapFrom(x => string.Join(' ', x.Address, x.Country)));

        }
    }
}
namespace Contracts
{
    public interface ICompanyRepository
    {
        Company GetCompanyWithEmployees(Guid companyId, bool trackChanges);
    }
}
namespace Contracts
{
    public interface IRepositoryBase<T>
    {
        IQueryable<T> FindAll(bool trackChanges);
        IQueryable<T> FindByCondition(Expression<Func<T, bool>> expression, bool trackChanges);
        void Create(T entity);
        void Update(T entity);
        void Delete(T entity);
    }
}
namespace Repository
{
    public abstract class RepositoryBase<T> : IRepositoryBase<T> where T : class
    {
        protected RepositoryContext RepositoryContext;

        public RepositoryBase(RepositoryContext repositoryContext) => RepositoryContext = repositoryContext;

        public IQueryable<T> FindAll(bool trackChanges) => 
            !trackChanges ?  
                RepositoryContext.Set<T>()
                    .AsNoTracking() :
                RepositoryContext.Set<T>();
        
        public IQueryable<T> FindByCondition(Expression<Func<T, bool>> expression, bool trackChanges) =>
            !trackChanges ?
                RepositoryContext.Set<T>()
                    .Where(expression)
                    .AsNoTracking() :
                RepositoryContext.Set<T>()
                    .Where(expression);
        
        public void Create(T entity) => RepositoryContext.Set<T>().Add(entity);
        public void Update(T entity) => RepositoryContext.Set<T>().Update(entity);
        public void Delete(T entity) => RepositoryContext.Set<T>().Remove(entity);
    }
}
namespace Repository
{
    public class CompanyRepository : RepositoryBase<Company>, ICompanyRepository
      {
        public CompanyRepository(RepositoryContext repositoryContext) : base(repositoryContext)
        {
        }

        public Company GetCompanyWithEmployees(Guid companyId, bool trackChanges)
        {
            return FindByCondition(owner => owner.Id.Equals(companyId), trackChanges)
             .Include(s => s.Employees)
             .OrderBy(c=>c.Name)
             .FirstOrDefault();
        }
     }
}
namespace Service.Contracts
{
    public interface ICompanyService
    {
        CompanyWithEmployeesDto GetCompanyWithEmployees(Guid companyId, bool trackChanges);

    }
}
namespace Service
{
    internal sealed class CompanyService : ICompanyService
    {
       private readonly IRepositoryManager _repository;
        private readonly ILoggerManager _logger;
        private readonly IMapper _mapper;
        public CompanyService(IRepositoryManager repository, ILoggerManager logger, IMapper mapper)
        {
            _repository = repository;
            _logger = logger;
            _mapper = mapper;
        }

        public CompanyWithEmployeesDto GetCompanyWithEmployees(Guid companyId, bool trackChanges)
        {
            var company = _repository.Company.GetCompanyWithEmployees(companyId, trackChanges);
            if (company is null)
                throw new CompanyNotFoundException(companyId);

            var CompanyWithEmployeesDto = _mapper.Map<CompanyWithEmployeesDto>(company);
            return CompanyWithEmployeesDto;
        }
    }
}
namespace Presentation.Controllers
{
    [Route("api/companies")]
    [ApiController]
    public class CompaniesController : ControllerBase
    {
        private readonly IServiceManager _service;
        public CompaniesController(IServiceManager service) => _service = service;

        [HttpGet("{id:guid}/details")]
        public IActionResult GetCompanyDetails(Guid id)
        {
            var company = _service.CompanyService.GetCompanyWithEmployees(id, trackChanges: true);
            return Ok(company);
        }

    }
}

This is what i get:

{
    "id": "3d490a70-94ce-4d15-9494-5248280c2ce3",
    "name": "Admin_Solutions Ltd",
    "fullAddress": "312 Forest Avenue, BF 923 USA",
    "employee": null
}

and this is what i want:

{
    "id": "3d490a70-94ce-4d15-9494-5248280c2ce3",
    "name": "Admin_Solutions Ltd",
    "fullAddress": "312 Forest Avenue, BF 923 USA",
    "employee": [
    {
        "id": "021ca3c1-0deb-4afd-ae94-2159a8479811",
        "name": "Kane Miller",
        "age": 35,
        "position": "Administrator"
    }
]
}
ws51t4hk

ws51t4hk1#

This isn't a repository, it's a CRUD class. The error itself is caused by a typo though - the Employees property in Company becomes Employee in CompanyWithEmployeesDto .

Change the property to employees. And consider whether all this code provides any benefit over :

var company=_context.Company.Include(c=>c.Employees).Where(c=>c.ID==45).First();

var dto=_mapper.Map<CompanyWithEmployeesDto>(company);

A real Repository should raise the level of abstraction. With EF Core you're already at the level of a multi-entity Unit-of-Work. There's no need for single-entity repositories. In the question's code, the actual Repository is the CompanyService class

相关问题