I have a fairly simple query using multiple Include
s that was working fine before I upgraded my application to .NET 5.0.
This query now takes +30sec to run and I'm not sure why.
When I replace the ProjectTo
with an explicit mapping of the result set, it works normally. The problem is I have many instances of ProjectTo
in my application with the same slow behavior.
Can anyone please help me identify what I'm doing wrong or if I need to make any configuration changes?
Thanks!
Query
public async Task<List<VisualCodeDTO>> GetVisualCodesAsync(int days, int? visualCodeTypeId = null)
{
try
{
var visualCodes = _dbContext.VisualCode
.Include(x => x.Doctor)
.Include(x => x.VisualCodeType)
.Include(x => x.Symbols)
;
if (visualCodeTypeId != null)
visualCodes = visualCodes.Where(x => x.VisualCodeTypeId == visualCodeTypeId);
return await visualCodes.AsNoTracking()
.OrderByDescending(x => x.LastUpdated)
.ProjectTo<VisualCodeDTO>(_mapper.ConfigurationProvider)
.ToListAsync();
//return _mapper.Map<List<VisualCodeDTO>>(result);
}
catch (Exception e)
{
_logger.LogError(e.Message);
throw;
}
}
Startup.cs
...
services.AddAutoMapper(typeof(AutoMapping));
...
AutoMapping.cs
public AutoMapping()
{
CreateMap<VisualCode, VisualCodeDTO>()
.ForMember(dest => dest.DoctorName, opt => opt.MapFrom(src => src.Doctor != null ? string.Format("{0} {1}", src.Doctor.FirstName, src.Doctor.LastName) : string.Empty))
.ForMember(dest => dest.LastUpdated, opt => opt.MapFrom(src => src.LastUpdated.HasValue ? src.LastUpdated.Value.ToLocalTime().ToShortDateString() : string.Empty))
.ForMember(dest => dest.VisualCodeType, opt => opt.MapFrom(src => src.VisualCodeType != null ? src.VisualCodeType.Name : string.Empty))
.ForMember(dest => dest.VisualCodeTypeIcon, opt => opt.MapFrom(src => src.Symbols != null ? src.Symbols.FirstOrDefault().ImgSrc : string.Empty));
CreateMap<Symbol, SymbolDTO>();
CreateMap<VisualCodeType, VisualCodeTypeDTO>()
.ForMember(dest => dest.Icon, opt => opt.MapFrom(src => src.VisualCodes.Count > 0 && src.VisualCodes.FirstOrDefault().Symbols.Count > 0 ? src.VisualCodes.First().Symbols.First().ImgSrc : string.Empty));
Models + DTOs
public class VisualCode
{
public int Id { get; set; }
public string Name { get; set; }
public DateTime? LastUpdated { get; set; }
public string UserId { get; set; }
public virtual ApplicationUser Doctor { get; set; }
public virtual List<Exercise> Exercises { get; set; }
public virtual List<Symbol> Symbols { get; set; }
public int VisualCodeTypeId { get; set; }
public virtual VisualCodeType VisualCodeType { get; set; }
}
public class VisualCodeDTO
{
public int Id { get; set; }
public string Name { get; set; }
public string UserId { get; set; }
public int VisualCodeTypeId { get; set; }
public string LastUpdated { get; set; }
public string DoctorName { get; set; }
public List<SymbolDTO> Symbols { get; set; }
public string VisualCodeType { get; set; }
public string VisualCodeTypeIcon { get; set; }
}
public class Symbol
{
public int Id { get; set; }
public int VisualCodeId { get; set; }
public virtual VisualCode VisualCode { get; set; }
public char Letter { get; set; }
public string ImgSrc { get; set; }
}
public class SymbolDTO
{
public int Id { get; set; }
public int VisualCodeId { get; set; }
public char Letter { get; set; }
public string ImgSrc { get; set; }
public string ImgSrc_Answer { get; set; }
}
public class VisualCodeType
{
public int Id { get; set; }
public string Name { get; set; }
public virtual List<VisualCode> VisualCodes { get; set; }
}
public class VisualCodeTypeDTO
{
public int Id { get; set; }
public string Name { get; set; }
public string Icon { get; set; }
}
EDIT
The mapping definition that was causing issues was:
CreateMap<VisualCodeType, VisualCodeTypeDTO>()
.ForMember(dest => dest.Icon, opt => opt.MapFrom(src => src.VisualCodes.Count > 0 && src.VisualCodes.FirstOrDefault().Symbols.Count > 0 ? src.VisualCodes.First().Symbols.First().ImgSrc : string.Empty));
I replaced this with
CreateMap<VisualCodeType, VisualCodeTypeDTO>();
and then added Icon
prop the the VisualCodeType
table instead.