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"
}
]
}
1条答案
按热度按时间ws51t4hk1#
This isn't a repository, it's a CRUD class. The error itself is caused by a typo though - the
Employees
property inCompany
becomesEmployee
inCompanyWithEmployeesDto
.Change the property to employees. And consider whether all this code provides any benefit over :
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