0

I have a fairly simple query using multiple Includes 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; }
    }

enter image description here

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.

roonz11
  • 795
  • 1
  • 13
  • 24
  • The following may be helpful: https://stackoverflow.com/questions/34724196/entity-framework-code-is-slow-when-using-include-many-times and https://stackoverflow.com/questions/69109112/this-db-query-takes-way-too-long and https://learn.microsoft.com/en-us/ef/core/querying/single-split-queries – Tu deschizi eu inchid Oct 23 '21 at 21:04
  • thanks. I tried using split query but that threw an exception and suggested I convert it back to a single query. – roonz11 Oct 23 '21 at 21:23
  • 1
    @roonz11 have you tried to compare generated SQL before and after update? – Guru Stron Oct 23 '21 at 21:34
  • @GuruStron I just configured my logging to see the outputed SQL and it does in fact look quite inefficient! This gives me a good idea! thanks! – roonz11 Oct 23 '21 at 21:46
  • `Include` is not needed with `ProjectTo`. Not sure about those null checks. Does it break without them? – Lucian Bargaoanu Oct 24 '21 at 06:30
  • @LucianBargaoanu Sorry for the late reply! It seemed as though the queries within the mapping were not being translated to proper SQL and therefor slowing things down. Once I removed those it started working again. – roonz11 Nov 20 '21 at 19:02
  • @roonz11 So you just removed the ProjectTo functionality of AutoMapper and just queried with EF directly? – drewlsvern Mar 02 '22 at 16:05
  • @drewlsvern I removed a linq query from within mapping and created a workaround into the EF query instead. I was able to still use ProjectTo on the result set. – roonz11 Mar 03 '22 at 17:14

0 Answers0