Automapper - How get joined table data

Question

i've build example application with .Net Core 7 and trying use Automapper 12.0 and stucked in this situation.

I want to sum values from Table HutangHistory which is joined (maybe) from my configuration below:

ApplicationDbContext.cs

 modelBuilder.Entity<Hutang>()
     .HasMany(h => h.HutangHistories)
     .WithOne(hh => hh.Hutang)
     .HasForeignKey(hh => hh.HutangId)
     .IsRequired();

MappingProfile.cs

public class MappingProfiles : Profile
{
    public MappingProfiles()
    {
        CreateMap<Hutang, HutangReadDto>()
             .ForMember(dest => dest.TotalHutang, opt => opt.MapFrom(src => src.HutangHistories.Sum(hh => hh.Nominal))); // Expected this is enough.
        CreateMap<HutangReadDto, Hutang>();
    }
}

Hutang.cs

 public class Hutang
 {
     [Key]
     [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
     public Guid HutangId { get; set; }
     public int Status { get; set; }
     public ICollection<HutangHistory> HutangHistories { get;  } = new List<HutangHistory>();
     [Required]
     public Guid CreatedBy { get; set; }
     [Required]
     public DateTime CreatedAt { get; set; }
     [Required]
     public Guid UpdatedBy { get; set; }
     [Required]
     public DateTime UpdatedAt { get; set; }
 }

HutangHistory.cs

public class HutangHistory
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid HistoryId { get; set; }
    public decimal Nominal { get; set; }
    public int Tipe;
    public Guid HutangId { get; set; }
    public Hutang Hutang { get; set; } = null!;
    public Guid SumberHutangId { get; set; }
    public SumberHutang SumberHutang { get; set; } = null!;
    [Required]
    public Guid CreatedBy { get; set; }
    [Required]
    public DateTime CreatedAt { get; set; }
    [Required]
    public Guid UpdatedBy { get; set; }
    [Required]
    public DateTime UpdatedAt { get; set; }
}

HutangReadDto.cs

 public class HutangReadDto
 {
     public Guid HutangId { get; set; }
     public int Status { get; set; }
     public decimal TotalHutang { get; set; }
     public Guid CreatedBy { get; set; }
     public DateTime CreatedAt { get; set; }
 }

Finally the Controller:

 private readonly ILogger<HutangAPIController> _logger;
 private readonly ApplicationDbContext _dbContext;
 private readonly IMapper _mapper;

 public HutangAPIController(ILogger<HutangAPIController> logger, ApplicationDbContext dbContext, IMapper mapper)
 {
     _logger = logger;
     _dbContext = dbContext;
     _mapper = mapper;
 }

 [HttpGet]
 [ProducesResponseType(StatusCodes.Status200OK)]
 public ActionResult<IEnumerable<HutangReadDto>> GetHutangs() 
 {
     _logger.LogInformation("Menampilkan semua hutang");

     var hutangs = _mapper.Map<HutangReadDto>(_dbContext.Hutangs.ToList()); // My expactation is HutangHistories data will be retrieved but unfortunately is not.

     return Ok(hutangs);
 } 

FYI the mapping was successful, but i didn't retrieve HutangHistory data.

Am i missing something? Please help.

Answer 1

The latest way to do this is by using ProjectTo, which was introduced in Automapper

So you would define your Mapping like so:

public class FooEntity 
{
   public int Id {get;set;}
   public int ForeignKeyId {get;set;}
   public virtual ForeignEntity ForeignEntity {get;set;}

}

public class FooDto 
{
   public int Id {get;set;}
   public int ForeignKeyId {get;set;}
   public int ForeignItemName {get;set;}
}


 profile.CreateMap<FooEntity , FooDto >()
     .ForMember(d => d.ForeignItemName , s => s.MapFrom(x => x.ForeignEntity.Name));
 ;

Then Query the DB like:

IQueryable<FooDto > check = _dbContext.Foos.ProjectTo<FooDto>(_mapper.ConfigurationProvider);

This will then Query the DB by creating an Inner/Left join depending on the relationship defined.

In my test, I performed the following Mapping:

public class MaintenanceLogDto
{
    public int ItemCount { get; set; }

    public void Mapping(Profile profile) => 
            profile.CreateMap<MaintenanceLog, MaintenanceLogDto>()
                .ForMember(d => d.ItemCount, s=> s.MapFrom(x => x.MaintenanceLogItems.Count()));
}

And then Query like this:

    IQueryable<MaintenanceLogDto> logs = _tenantContext.MaintenanceLogs
                                                .ProjectTo<MaintenanceLogDto>(_mapper.ConfigurationProvider);

and this was the result SQL logged:

  SELECT 
      (SELECT COUNT(*)
      FROM [MaintenanceLogItems] AS [m1]
      WHERE [t].[Id] = [m1].[MaintenanceLogId]) AS [ItemCount]
  FROM (
      SELECT [m].[Id]--, ""MY OTHER FIELDS""....
      FROM [MaintenanceLogs] AS [m]
  ) AS [t]

Official Docs:

https://docs.automapper.org/en/stable/Queryable-Extensions.html